Bulk Insert format problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. 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)

Bulk Insert format problem

Author

Topic

a691u23
Starting Member

3 Posts

Posted - 2003-03-20 : 11:27:15

I have a delimited text file that I am importing into SQL Server via a bulk insert. The field separator is a comma and the opening and closing delimiters are double quotes. The data looks like this:

"MRS","JESSICA","","NELSON","1 BEEKMAN PL","","AGAWAM","MA","01001"

I am using a format file like this:

7.0
9
1 SQLCHAR 0 3 "\",\"" 1 title
2 SQLCHAR 0 15 "\",\"" 2 firstname
3 SQLCHAR 0 1 "\",\"" 3 middleinitial
4 SQLCHAR 0 21 "\",\"" 4 lastname
5 SQLCHAR 0 40 "\",\"" 5 address1
6 SQLCHAR 0 40 "\",\"" 6 address2
7 SQLCHAR 0 20 "\",\"" 7 city
8 SQLCHAR 0 2 "\",\"" 8 state
9 SQLCHAR 0 5 "\r\n" 9 zip

I am having problems with the title and the zip fields. The title data appears as "Mrs instead of Mrs . The zip data appears as "01001".
I know the problem lies in the format file but I can't figure out how to fix it.

Thanks in advance.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-21 : 04:58:28

BOL Says..
Using Format files,
Note: It is possible to skip importing a table column if the field does not exist in the data file by specifying 0 prefix length, 0 length, 0 server column order, and no terminator. This effectively states that the data field does not exist in the data file, and that the server column should not have data loaded into it.

format file.
7.0
10
1 SQLCHAR 0 0 "\"" 0 xxx
2 SQLCHAR 0 3 "\",\"" 1 title
3 SQLCHAR 0 15 "\",\"" 2 firstname
4 SQLCHAR 0 1 "\",\"" 3 middleinitial
5 SQLCHAR 0 21 "\",\"" 4 lastname
6 SQLCHAR 0 40 "\",\"" 5 address1
7 SQLCHAR 0 40 "\",\"" 6 address2
8 SQLCHAR 0 20 "\",\"" 7 city
9 SQLCHAR 0 2 "\",\"" 8 state
10 SQLCHAR 0 5 "\"\r\n" 9 zip

create table Prod (title varchar(5), firstname varchar(20), middleinitial varchar(3),
lastname varchar(25), address1 varchar(50), address2 varchar(50),
city varchar(25), state varchar(5), zip varchar(10))
GO
BULK INSERT Prod FROM 'C:\data.txt' WITH (FORMATFILE = 'c:\format1.txt')
GO
Select * from Prod
GO
Drop table prod

EDIT: Change in format file at first and last file field order (1&10)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 03/21/2003 05:02:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources