I wrote the this sqlcmd to export data from SQL SERVER to csv
sqlcmd -S SERVERNAME -E - i d:\files\code.sql -o d:\output\mycode.csv -s"," -W -d EMployee(databasename)
the first 15 colums have the correct data however after that the data is pushed to the right
so if COLUMN NAME should be kirk its saying 124 8th st and so on till the end, The .sql file has over 300 exported column form the select * from tablename query.
Am i missing something within my sqlcmd script?
what is causing my data to be off when its a simple select *
The only thing I can think of if your set on not using SSIS is to concatenate the columns with quotes and commas to have control over the formatting of the output file.
sqlcmd -Sods.db -Q"select top(100) char(34)+first_name+char(34)+','+char(34)+last_name+char(34) [""first_name"",""last_name""] from ODS.dbo.pers" -opers.csv
Or use Powershell, which does this better:
Read-SqlTableData -SchemaName dbo -TableName Numbers -ServerInstance . -DatabaseName 'Scratch' | Export-Csv -NoTypeInformation -Path c:\test\test.csv
the danger with csv is that if your data itself contains commas in it then your output will be hosed
for example if there is data with
"Note: customer would like to order, separate order but one billing, blah blah blah"
2 commas 3 separate columns in your csv ? That is why I personally like tab delimited txt file
If you're exporting CHAR() datatypes or the data was imported from something similar, then you're going to have a wad of spaces similar to a right-padded column in that column.