October 8, 2009 at 4:06 am
Hello
I have a script that has been written to obtain some data from our SQL Express instance. the script works with no issues. However i need to automate this. I was going to automate this using a SQLCMD -S then call the script which is saved on the server. I dont thing this is going to be an issue, the issue is the results. how can i get these to be exported to CSV file?
Can someone let me know the code or the commands needed to obtain this?
Regards
Gareth
October 9, 2009 at 5:15 am
Hi,
Try this command:
sqlcmd -Sservername -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.csv" -h-1 -s"," -w 700
-h-1 removes column name headers from the result
-s"," sets the column seperator to ,
-w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
October 19, 2009 at 9:29 am
Thanks for this,
sorry for the delayed reply.
i will give this ago. the script will need to reference more than one DB but i assume i will be able to tweek what you have put
Many thanks
Gareth
October 20, 2009 at 4:41 am
Hi,
Also, you can try out using -i D:\SQL\Script1.sql in place of -Q if you are directly calling a script. -i indicates the script file path.
Have a nice day !
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
October 20, 2009 at 7:07 am
Once again thanks, that has worked a treat. Think im going to see if it can date stamp the output that is created so that more than one file can exist, but that was very helpfull
Regards
Gareth
April 6, 2010 at 1:06 pm
Hi, you have posted below string:
sqlcmd -Sservername -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.csv" -h-1 -s"," -w 700
In the above string you are deviding data with 'comma' suparator, but what if we already have 'comma' in between the data not between the columns. Still it will grab the data with out overlaping.
Ex:
col1 col2
----- -----
abc g,h,i
def jk,l
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply