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?
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...