Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)

Author  Topic 

Starting Member

13 Posts

Posted - 2005-07-27 : 13:10:05
I want to import a comma delimited file into SQL 2000. The data for most of the columns are surrounded by double quotes, but not all. Some of the data has commas in middle.
I want to use a bulk insert to import the file. I can not use a comma for the FIELDTERMINATOR or double quotes and a comma either because of the problems mentioned above.
It seems the best way to do this would be to make a bcp.fmt file and use it in the bulk insert.
I have never made one before and the comma delimited file has 43 columns of data. Since I have not made one of these types of files before, I need a way that will make the file without mistakes in it.

My question is:

Is there a way to make the bcp.fmt file automatically or a way to make the file and let me edit it for the different FIELDTERMINATORs?

Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-27 : 18:56:05
If you call bcp from the command line and don't give it a format file, it will ask you a bunch of questions about the file format and give you the option of saving the resultant format file for later use. Check out BOL for confirmation because it's been a while since I used it...


Go to Top of Page


12543 Posts

Posted - 2005-07-27 : 22:17:54

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

- Advertisement -