Generating a CSV file with a query

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)

Generating a CSV file with a query

Author

Topic

zstone
Starting Member

6 Posts

Posted - 2007-07-12 : 02:04:46

Hi All,

I have spent a few days trying to generate a CSV file from a SELECT query ( used in a web application ).

I have a system written in MySQL which uses the following query to genarate a CSV file:

SELECT column1, column2...etc
INTO OUTFILE "path_to_file_on_server/file_name.csv"
FIELDS TERMINATED BY ','
ESCAPED BY '"'
ENCLOSED BY '"'

I am trying to do the same in MS SQL but so far this doesn't seem to be possible - I tried using xp_cmdshell but that has limitations with the formatting of the CSV its not allowing me to specify the order of the columns of the CSV - is this at all possible in MSSQL 2005 ?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-12 : 02:09:25

Try one of these

1) BCP
2) OPENROWSET

Peter Larsson
Helsingborg, Sweden

zstone
Starting Member

6 Posts

Posted - 2007-07-12 : 02:39:12

Thank you - I looked at openrowset it looks like it is mainly used for importing data...but I am trying to export data.

I tried

EXEC master..xp_cmdshell
'osql -S localhost -U user2 -P user2 -q "SELECT * FROM database..table" -o D:\Webroot\appname\test.txt'

Its generating the file but there is no format and the data is a mess - is there a way to format it in columns...?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 02:41:24

Have you check out BCP ?

KH
[spoiler]Time is always against us[/spoiler]

zstone
Starting Member

6 Posts

Posted - 2007-07-12 : 02:58:06

I tried BDCP like below - nothing is happening

EXEC master..xp_cmdshell
'bcp "select TOP 10 filed1,field2,field3 from database..tablename" queryout D:\Webroot\test.txt -c -username -password'

Something is not correct

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 03:11:29

try to run it directly in command prompt first. Get things right before using cmdshell

KH
[spoiler]Time is always against us[/spoiler]

zstone
Starting Member

6 Posts

Posted - 2007-07-12 : 08:23:44

Thank you all - the problem here is now with formatting teh column order, column name...etc its running fine from cmd line but I just would like to tidy up the data in proper columns.... Its dumping everything without any formatting. I am using XP_cmdshell

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-12 : 15:02:04

I think BCP has an option of using a format file. What do Books Online tell you?

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 16:41:44

YEs use a format file:


-- CREATE FORMAT FILE
master..xp_cmdshell 'bcp DataBase.dbo.TableName format nul -T -c -t"|" -f"\\somepath\somefile.fmt" -T'

--BCP OUT
exec master..xp_cmdshell 'bcp "SELECT * FROM DataBase.dbo.TableName" queryout "\\somepath\SomeTextfile.txt" -f"\\somepath\somefile.fmt" -S"ServerName" -T'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

zstone
Starting Member

6 Posts

Posted - 2007-07-12 : 17:31:54

Thank you very very much dinakar

You're a champion - thats exactly what I was after.

I will now look at the different options for creating .fmt files....

Cheers !

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 17:36:18

quote:
Originally posted by zstone

Thank you very very much dinakar

You're a champion - thats exactly what I was after.

I will now look at the different options for creating .fmt files....

Cheers !

Welcome. Glad to help.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources