michaelheffernan
asked on
MSSQL 2008 isql commnd line export to CSV specifying double quotes around alpha fields
Hello. Like it sez...using ISQL to export to CSV for later import into Excel; works fine unless agents put commas in text fields. I understand that somehow I can specify double quotes around alpha fields from the command line, but cannot locate info on how. -s doesn't work, since double quotes are misinterpreted by the batch file that runs the query in Automate.
So...is there a command line parameter that will specify double quotes around alpha fields to give me "," delimiting around them? CUrrent command line:
isql.exe -d tt20%logYear%_%logMonth% -h 0 -i %directory%\8230YCC.sql -S data_base77 -s "," -U sa -P ******* -n -w 1024 >> %directory%\asiexport.csv
THank you.
M
So...is there a command line parameter that will specify double quotes around alpha fields to give me "," delimiting around them? CUrrent command line:
isql.exe -d tt20%logYear%_%logMonth% -h 0 -i %directory%\8230YCC.sql -S data_base77 -s "," -U sa -P ******* -n -w 1024 >> %directory%\asiexport.csv
THank you.
M
ASKER
Yeah, I guess I can mod the SQL select statement directly, I was hoping for an easier way... let me look at doing that. TY
You can also try use SSIS which may be indeed easier way that via I/Osql commands - sorry if I misinterpreted your question...would be something like:
1. Create a flat file connection manager and map the destination .csv file
2. Use a "Flat File Destination" task and export the data into this .csv file.
While configuring the flat file connection manager, when you click the browse button you need to select the Files of Type as *.csv (CSV files).
here is where you can find some details/examples:
http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/
1. Create a flat file connection manager and map the destination .csv file
2. Use a "Flat File Destination" task and export the data into this .csv file.
While configuring the flat file connection manager, when you click the browse button you need to select the Files of Type as *.csv (CSV files).
here is where you can find some details/examples:
http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/
ASKER
You have. This is an automated daily process. No GUIs need apply. But I will eyeball the link. TY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didn't want a complex solution to a very simple problem. I needed quick and dirty and I just figured it out.
exec master..xp_cmdshell N'sqlcmd -E -Q"select ''"'',accountid,''"'',''"'
exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; select * from DB_Name.dbo.TableName;" -s"," -W -o"c:\output.csv"'
or without headers like:
exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schem
more details about switches at:
http://msdn.microsoft.com/en-us/library/ms162806(v=sql.105).aspx