如何通过运行SQL查询从批处理脚本导出CSV文件中删除多余的空格 [英] How to remove extra spaces from CSV file exporting trough a batch script by running a sql query

问题描述

您好我正在使用以下查询来获取一些数据： - 然后将其带入sp并在批处理文件中调用SP以将其导出为CSV文件： - 但列中有额外的空格。需要删除。该怎么做 -



查询： -

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],b.name 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

我的批处理脚本： -





SQLCMD -U<> -P<> -S％SERVER％-d< db name => -s，-QMissingSales| findstr / V / C： - / B> ％RootDir％\ MissingSales_％date：~4,2％_％date：~7,2％_％date：~10,4％.csv

echo存储过程成功处理>> ; ％LOG_FILE％



请帮我从导出的CSV文件中删除空格。先谢谢

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

推荐答案

尝试-W和你的命令。



http://www.sqlserverspecialists.com/2012/10/sqlcmd-command-line-option.html [ ^ ]

Try -W along with your command.

http://www.sqlserverspecialists.com/2012/10/sqlcmd-command-line-option.html[^]

您好，



您能否尝试以下解决方案？



自动删除CSV文件中空格的步骤：

在Microsoft®Excel中打开csv文件

选择标记栏

选择编辑>替换然后选择选项按钮

选择搜索下拉列表并选择按列

在查找内容字段中输入空格并将替换为字段留空

选择全部替换。

选择全部替换将消除标记列每行中的所有空格。

Hi,

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.

这篇关于如何通过运行SQL查询从批处理脚本导出CSV文件中删除多余的空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！