Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
latture
Starting Member
24 Posts |
Posted - 2013-02-06 : 14:19:41
|
Hello.I'm using SQLCMD to export file to excel using below code.set @CommandString = 'sqlcmd -S rvsql -E -Q "select PGroupId,SId,MfC,Part,OTQty,Convert(varchar(10),ReleaseDate,101) ReleaseDate,IsReleased,IsA,CW_E,IsS,OTQ From ##temp" -o "c:\export\test.xls" -s"," -w 700'It works fine and I'm getting it to export but I can't seem to get it to separate columns by comma which I thought it was covered. Each row is showing with one big string. Help? |
|
tm
Posting Yak Master
160 Posts |
Posted - 2013-02-06 : 14:43:02
|
How are you executing the sqlcmd?I put the sqlcmd into a batch file to test and it exported with comma as delimeter as per parameter.One thing I did add is -d for database.command put into batch file ..sqlcmd -S (local) -d TESTDB -E -Q "select ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType From dbo.TESTTABLE" -o "c:\temp\test_sqlcmd_output.xls" -s"," -w 700 |
|
|
latture
Starting Member
24 Posts |
Posted - 2013-02-06 : 14:58:33
|
I'm using the following. exec master..xp_cmdshell @CommandStringIt wouldn't be a problem when I export to csv but then the leading zero in one of my fields gets dropped off so I was trying to work around it. WHen I try to run to command with -d (which I'm not certain if I'm running it right since the ##temp file isn't attached to any particular database) I'm getting the same result. When I open it with excel (our user only wants to use Excel 2010) I'm getting the message that file that I'm trying to open is in a different format which is the same message that I was getting earlier without -d. When the file opens the data is all under one column and NOT seperated by comma... I don't know what to do... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 19:46:05
|
If you want to export the file as an XLS or XLSX file, I don't think sqlcmd with a simple select can do it. You will need to use SSIS or something else - for example a custom .Net program that uses Excel object model. But you also say you want it to be comma-separated. Comma-separated files are usually given the extension .csv. If you set the extension as .csv, Excel 2010 can open it; only thing is that if the client wants to open the file and then to do some formatting or use some other Excel features, after they make such changes, they will have to save as an Excel file instead of the csv file. |
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2013-02-08 : 11:18:45
|
Thanks James K for clarification as I didn't mentioned that I exported as CSV file and not excel file even though I exported to XLS extension.latture .. you might want to refer link below for exporting to excel file using xp_cmdshell..[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[/url] |
|
|
latture
Starting Member
24 Posts |
Posted - 2013-02-11 : 09:33:19
|
Thanks for the input. I ended up using SSIS cause I couldn't get the export file to look right us sqlcmd. The reason I tried to export the file from sqlcmd first was because it was exporting from a temporary folder that was being created on the spot instead of a folder in a database. Just curious though, is that possible? Exporting a file via SSIS from a #temp? |
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2013-02-11 : 11:59:42
|
I believe you can as I did see one before but I cannot find the link.If you web search for "SSIS temp table" or "SSIS data flow temp table" you can get a lot of information. |
|
|
|
|
|
|
|