SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can't get separte columns, sqlcmd
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

latture
Starting Member

24 Posts

Posted - 02/06/2013 :  14:19:41  Show Profile  Reply with Quote
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?

Edited by - latture on 02/06/2013 14:50:03

tm
Posting Yak Master

160 Posts

Posted - 02/06/2013 :  14:43:02  Show Profile  Reply with Quote
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 - 02/06/2013 :  14:58:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 02/06/2013 :  19:46:05  Show Profile  Reply with Quote
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 - 02/08/2013 :  11:18:45  Show Profile  Reply with Quote
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..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 02/11/2013 :  09:33:19  Show Profile  Reply with Quote
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 - 02/11/2013 :  11:59:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000