BCP Export File Formatting

[cstrong]

Hi all,

I need to export data from a SQL Server 7.0 table into a .csv file and as one of the columns is varchar(6000), I am using the BCP command to do this.

However, I have some fields that also contain comma's within them so I'd like to enclose ALL string columns withing quotes, so that it is properly formatted.

I am trying to create reports, so I need the formatting to be correct.

Any idea's..?

Cheers,
Clive

 

[Tim1]

Hi Clive,

I have used BCP quite significantly over the last 6 months or so, as we are loading a new database, but I have never seen an option to allow you to use quotes around text fields when copying data out.

Do you have to create the file as a csv file, or could you use some other kind of delimiter to seperate the data (e.g. a tilde, or a pipe).

You can specify this easily in the bcp command just by using -t'x'

where x is whatever you want your delimeter to be.

Sorry I can't be of more help,

Tim

 

[cstrong]

Hi,

Unfortunately we do need a CSV file format. It's a customer report and it's gets emailed to them direct and there not flexible.

Cheers
Clive

 

[dbmsguy]

you should be able to do it with a bcp format file.

to create a format file:

bcp dbname..table format c:\table.dat -f c:\table.fmt -T -c =t','

you would send the format file and bcp file to your customer. bcp or bulk copy back in will read the format file.

Paul

http://www.dbmsguy.com

 

[tlbroadbent]

If the customer is using the file in a different application, the BCP format file won't be useful.

I suggest that you create a view that concatenates the double quotes to the data and then bcp from the view rather than the table.

Example:

Create View vwMyTableForExport As

Select
Col1,
'"' + col2 + '"' As Col2,
col3,
'"' + col4 + '"' As Col4
From MyTable Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[dbmsguy]

another thought...

you could use the queryout feature of bcp...

1. create a table called 'delim'. one column and one row with the value ','
2. use following command:

bcp "select val1 + delimcol + val2 from db..yourtab, db..delim" queryout c:\testdb.out -T -c

basically, you do a cartesian join on your table and the one row one column table. this one row table will have you 'delimiter' value of ',' and will be concat in one big long string... you would have to make sure all numerics are converted to char

I think this would give you what you want to see. Paul

http://www.dbmsguy.com