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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 BULK INSERT AND FILE FORMAT
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JHCOLVI
Starting Member

USA
13 Posts

Posted - 07/27/2005 :  13:10:05  Show Profile  Reply with Quote
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?

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/27/2005 :  18:56:05  Show Profile  Visit timmy's Homepage  Reply with Quote
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...

HTH,

Tim
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2005 :  22:17:54  Show Profile  Visit nr's Homepage  Reply with Quote
see
http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html

==========================================
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000