Output to CSV but Separate Columns

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 2005 Forums

Transact-SQL (2005)

Output to CSV but Separate Columns

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-22 : 16:25:23

hi experts,

I want to export a table to a .csv file.

That's easy enough, but the results are going into 1 column in the excel file.

So I'm trying to break out the columns - this is what I have so far but it's not working. I get systax error at ','.

SET @cmd = '"SELECT PO_NUMBER||','|| as [PO NUMBER], LINE_NBR||',' as [PO LINE], ceiling(ENTERED_QTY)||','|| as [INVOICE QUANTITY], TOT_BASE_AMT||','|| as [INVOICE PRICE] from PROD.dbo.MAINVDTL WHERE COMPANY = 200 " -o ' + @ReportPath;

I'm trying to use the pipe symbols, is there another way?
Thanks, John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-22 : 16:31:45

Why don't you just use bcp? You can use it in T-SQL via xp_cmdshell.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-22 : 16:44:00

Thanks, Tara. I'll look into bcp. I assume with it, I'll end up with the data in separate columns?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-22 : 16:47:06

Yes.

Here's an example:

EXEC master..xp_cmdshell 'bcp Db1.dbo.Tbl1 out C:\Tbl1.csv -Sserver1\instance1 -T -t, -r\r\n -c'

Here's an example using a query:

EXEC master..xp_cmdshell 'bcp "select * from Db1..Tbl1 where Col1 = 2" queryout C:\Tbl1.csv -Sserver1\instance1 -T -t, -r\r\n -c'

If the queryout gets too complicated, you may need to wrap it into a view. And if you can't wrap it into a view, well I'd then recommend using SSIS/export wizard.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 09:46:06

... and a few days later... I ran this:

-- Construct report path and name

Set @ReportPath = '\\pncllwappco\E$\MESABA\OUT\' + 'INV' + '_' + Convert(varchar(8), @curDate, 112) + '.csv'

EXEC master..xp_cmdshell 'bcp "SELECT PO_NUMBER as [PO NUMBER], LINE_NBR as [PO LINE] from PROD.dbo.MAINVDTL" queryout @ReportPath -SPNCLLWDBCO\ -T -t, -r\r\n -c'

I ran this in the Query window. it seemed to run successfully but i cannot find the output file.
I'm running this from the database server (PNCLLWDBCO) but directing the out file to the application server PNCLLWAPPCO E drive.

queryout @ReportPath -SPNCLLWDBCO\ -T -t, -r\r\n -c'

** When using bcp, can the output path be a variable?

Thanks, John

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-04-27 : 12:38:29

Closing this post. I will start a new thread for my bcp question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources