When I execute the stored procedure in SQL Server and export data out to .csv (using "Save Result As"), the output .csv was formatted very nicely.
My goal is to extract data in this format, with the headers:
PK1,2005/010,#,Not Analysed,CVOI001,C Voice 001 Test Account,PI -Local,11/11/2019,-571.430,C,T00012
PK1,2005/010,#,Not Analysed,32400,Annual bonus,PI -Local,11/11/2019,571.430,D,T00012
But when I use sqlcmd to extract data from the same stored procedure (the goal is to automate this extraction task), the format is very different with "------" to separate the headers and a lot of padding in the fields. Sorry I cannot post the headers as the post will exceed the character limit.
Here's my code and the current output. Is there anything I should change?
sqlcmd -S SUNSERVER -E -Q "Exec SunSystemsData.[dbo].[z_UniFocusExtract] $(BUCode), $(LCode), $(TCodeNo), $(NoDayBack)"
-v BUCode='PK1' LCode='ANAL_T1' TCodeNo='2' NoDayBack='-100' -s "," -o "C:/Test/Test.csv"
PK1 ,2005/010,# ,Not Analysed ,CVOI001 ,C Voice 001 Test Account ,PI -Local ,11/11/2019 , -571.430,C ,T00012
PK1 ,2005/010,# ,Not Analysed ,32400 ,Annual bonus ,PI -Local ,11/11/2019 , 571.430,D ,T00012
You can add one more parameter to remove the headers from the csv file generated.
-h-1 removes column name headers from the result
-h headers
Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS. Use -1 to specify that headers not be printed. Any value that is not valid causes sqlcmd to generate an error message and then exit.
For removing the extra padding spaces in the column width, add -W parameter. I have tested it. It is working fine.
-W This option removes trailing spaces from a column. Use this option together with the -s option when preparing data that is to be exported to another application. Cannot be used with the -y or -Y options.