Export to CSV file 2

[ecobb]

I'm trying to set up a nightly data transfer on my SQL Server. I need to export the contents of a table to a CSV file. I felt like BCP would probably be the easiest way to do it, but I can't seem to figure out how to get the file to be comma delimited. When I BCP to a .txt file, it looks like this:

1	0005	I	     1367107	1	F34115	1
2	0005	I	     1367108	1	F34115	1
3	0005	I	     1367109	1	F34115	1
4	0005	I	     1370819	1	F16594	1

which would be great, except that I need it to be comma delimited. When I BCP to a .csv file and open it in Excel, all I get is garbage. When I open it in Notepad, it looks like the above example. Is there some command in BCP where I can specify to use a comma as the delimiter? Also, (even though you can't tell above) there are NULLS in between some of the values. I need to be able to retain them in the BCP "output".

Here is my BCP command:

bcp "Select * From Database..Table" queryout "C:\whatever\test.csv" -c -Uuser -Ppassword

Is BCP the best way to get this done? I thought about using DTS packages, but I've never really fooled with them either. Any help or ideas would be appreciated!

Thanks!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[JayKusch]

Can you not create a DTS job and then schedule it to run at the predetermined time?

If so, in your DTS package, select your source as the table needing to be exported, then set your target as a csv file. Create the data pump conduit between the 2 connections and close up the package and save it to the desired name. Once created, right-mouse click on the new package and schedule it.

Of course this may be a moute point if you are set on using BCP only!

Thanks

J. Kusch

 

[MDXer]

you can use the -t for column delimeters and the -r for row delimeters. But if you change to comma delimeter you may want to consider using a dts package so you can add Text qualifiers incase any of your data contains a comma.

bcp "Select * From Database..Table" queryout "C:\whatever\test.csv" -c -t , -Uuser -Ppassword

to use a dts you would
1)create a new package
2)create your connection object if it is SQL and the local source you just use the sql connector.
3) define your destination object. This would be the Text (Destination) This will prompt you for a file name. You can go under the advanced properties and define all your qualifiers.
4) Create a datapump between the 2.

if you want I can create a sample package and email it to you.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ecobb]

Thanks guys! I've been reading up on DTS this afternoon and was getting ready to try it. The only reason I was trying BCP was that I had used it before on occasion and I've never tried a DTS package. Thanks for listing out the steps involved, it seems pretty straight forward. I'm getting ready to give it a shot and will let you know how it goes. Thanks, again!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

You guys are great. I ran into problems when I tried to create the datapump between the objects (It was looking for columns in my blank csv file). But once I figured that out everything worked perfectly!

Thanks!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[MDXer]

Glad it worked.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck