Click here to Skip to main content
14,883,822 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I am using the below query to fetch some data :- then took it in sp and called the SP inside a batch file to export it in CSV file :- but the columns are having extra white spaces. which needs to be removed. how to do that-

Query :-
select replace(quotename(b.Store_Code,'"'),'','') as [Store Code], as [Store Name],
quotename(c.TRANSNUM,'"') as [Transaction Number], 
quotename(d.PRODUCT_SCANNED,'"') as [Lot Number],
quotename(p.product_code,'"') as [Product Code],
cast(d.QTY as numeric(9)) as [Quantity], 
quotename(cast(d.Price_Sold as numeric(9,2)),'"') as [Price Per Unit], 
quotename(cast(d.QTY*d.Price_Sold as numeric(9,2)),'"')as [Total Price], 
quotename(convert(Varchar(25),d.TS_ID,121),'"') as [TS_ID],
c.TRANSDATE as transdate,
quotename(convert(varchar(25),d.PostingDate,121),'"') as [Posting Date] 
from out_transactions (nolock) a 
inner join store b on a.key1=b.store_code_id 
inner join retail_transaction (nolock) c on a.key1=c.store_code_id and a.key2=c.TRANSTYPE 
and a.key3=c.Cais and a.key4=c.TRANSNUM and a.key5=c.session_number 
inner join dt_product (nolock) d on a.key1=d.store_code_id 
and a.key2=d.TRANSTYPE and a.key3=d.Cais and a.key4=d.TRANSNUM
inner join product p on p.product_id=d.product_id
where  a.sended <> 'D' and c.PROGRAMNAME <> 'inbound sales' and a.ts_id < CONVERT(Char(10),GETDATE(),126) -- Give the current date here yyyy-mm-dd

My Batch Script :--

SQLCMD -U <> -P <> -S%SERVER% -d<db name=""> -s, -Q "MissingSales" | findstr /V /C:"-" /B > %RootDir%\MissingSales_%date:~4,2%_%date:~7,2%_%date:~10,4%.csv
echo Store procedure processed successfully >> %LOG_FILE%

Please help me to remove the spaces from the exported CSV File. Thanks in Advance
Updated 16-Jul-14 15:57pm
ArunRajendra 16-Jul-14 23:09pm
Use LTrim & RTrim function on fields on which you want to remove the spaces. Or check this link to create trim function.
DEbopm 16-Jul-14 23:14pm
I dont need to trim anything on SQL result. I want to trim space after result will be exported to csv file

DEbopm 17-Jul-14 2:38am
when I put -W this will not set the output as a text formatted CSV file

Can you please try below solution ?

Steps to automatically remove spaces in the CSV file:
Open csv file in Microsoft® Excel
Select the Tag column
Select Edit > Replace then select the Options button
Select the Search drop down and select By Columns
In the Find what field input a space and leave the Replace with field blank
Select Replace All.
Selecting Replace All will eliminate all the spaces in each row of the Tag column.
DEbopm 17-Jul-14 2:07am
HI ,

I need to do this automatically , from inside the batch .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900