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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can't get separte columns, sqlcmd

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

Go to Top of Page

latture
Starting Member

24 Posts

Posted - 2013-02-06 : 14:58:33
I'm using the following. exec master..xp_cmdshell @CommandString

It 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...
Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -