Exporting the SQL Table into csv file

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

Other SQL Server Topics (2005)

Exporting the SQL Table into csv file

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2009-03-18 : 02:19:29

Hi,
I need to export the data in SQL Table into a csv file. I have tried using the bcp command. but how do we handle the columns which has ',' in it like address, notes field.

Suppose my table has data as
Fname Lname Address Phone Notes
XXX YYY 123,123123,LKJ 222-222222 ads,asd,sa
MMN JJDH 21,ash,asdh asd 232-023423 asdas,asda,sd

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 06:25:52

Hi Vmurali,

This is one of those problems where the solution is totally and annoyingly complex. What you will have to do is to use a custom format file. Here is my example SQL setup


CREATE TABLE _foo (
	[Fname] VARCHAR(255)
	, [Lname] VARCHAR(255)
	, [Address] VARCHAR(255)
	, [Phone] VARCHAR(255)
	, [Notes] VARCHAR(255)
	) 

INSERT _foo (
	[Fname]
	, [Lname]
	, [Address]
	, [Phone]
	, [Notes]
	) 
      SELECT 'foo', 'bar', '1, 23 Av', '223123', '123123'
UNION SELECT 'aaa', 'bbb', 'ccccc', 'dddd', 'eeee'

This makes the table _foo in my database with the fields that you specified.

Here's the bcp command that I'm using


bcp "SELECT TOP 10 '\"',* FROM CS_FLEX234_SONY.dbo._foo" QUERYOUT dump.csv -t\",\" -r\"\n -SDEVDB2\SQLSERVER2005 -Usa -Pmuppet -fbcp.fmt

And finally -- here's my bcp.fmt file


9.0
6
1       SQLCHAR       0       0       ""         1     FirstQuote         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       255     "\",\""    2     Fname              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       255     "\",\""    3     Lname              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       255     "\",\""    4     Address            SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       255     "\",\""    5     Phone              SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       255     "\"\r\n"   6     Notes              SQL_Latin1_General_CP1_CI_AS

What this does is

1) Selects a " as the first column returned.
2) The format file treats the column terminator for that first column as <nothing> therefore the second column gets concatenated onto the first (the quote).
3) format file for other column terminators is "," (so getting " round each other elmenet
4) row teminator is " <newline>

the output I get is this


"foo","bar","1, 23 Av","223123","123123"
"aaa","bbb","ccccc","dddd","eeee"

BCP takes a little getting your head around sometime!

Hope this helps you out.

NB: edited due to typo(s)
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pmohans123
Starting Member

5 Posts

Posted - 2009-04-03 : 02:19:16

Hi,

I have two questions abt BCP

1. I am using the BCP command to export the result into the text file. Generally the file result is in comma separated, but I want the result in space separated. like

S.NO Name City
1 AAAA XXXXX
2 BBBB YYYYY

Please tell me what option I have to use to get the above mentioned format result in the output file.

2. My second question is, in query analyzer I am running some query to get the result in text format, I am getting the result correctly, but the thing is I am seeing lot of space between one column to another, Is there any command to reduce the space between the column in the text result.

I am eagerly waiting the answer/solution for these 2 qns. Thanks in advance.

Bye
PMS

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-06 : 04:34:19

1) Change the format file -- replace the , with a space

2) Not sure. It's not one of the formats I'd ever export to -- I'd use a custom delimiter instead and then do a find/replace for that delimiter.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources