Bulk insert only loading 1 row of .csv file

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 2005 Forums

SSIS and Import/Export (2005)

Bulk insert only loading 1 row of .csv file

Author

Topic

erutledge
Starting Member

1 Post

Posted - 2009-04-02 : 13:02:05

I've created a .fmt file and can successfully load 1 row of my .csv file, using it, but for some reason, it doesn't load the rest!!! Here are the contents of the .csv file:
"760915"," 1 5 LB"," TOPPING M & M PLAIN CRUSHED"," 198"," 109/03/01"," 109/03/3 30"," A"," 19.240"," 760960"," 30.800",,
"760915"," 1 5 LB"," TOPPING M & M PLAIN CRUSHED"," 198"," 109/04/01"," 109/04/3 30"," A"," 19.920"," 760960"," 30.800",,
"760915"," 1 5 LB"," TOPPING M & M PLAIN CRUSHED"," 355"," 109/03/31"," 109/04/0 7"," M"," 19.040"," 760960"," 30.450",,

my .fmt file looks like this:
9.0
12
1 SQLCHAR 0 0 "\"" 0 x SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "\",\"" 1 old_item SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\",\"" 2 pack_size SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 description SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "\",\"" 4 contract_num SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\",\"" 5 contract_start SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "\",\"" 6 contractend_andday SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 "\",\"" 7 term SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\",\"" 8 current_price SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 "\",\"" 9 new_item SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\"," 10 new_price SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 "\",\r\n" 0 deleted SQL_Latin1_General_CP1_CI_AS

And the SQL statement I'm using to load is:
BULK INSERT mb_contract_changes
FROM 'C:\cntchg.csv'
WITH (ROWTERMINATOR = '/n',
FORMATFILE='C:\cntchg.fmt');

Does anyone see what I'm doing wrong? Thanks for your help!!!

erutledge

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-04-06 : 16:04:32

I came across something similiar once.

Check the table/index definition - for "ignore duplicate key"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 16:05:38

Also make sure you haven't hit an EOF character at the end of the first row. You can use a hex editor to check for the EOF character and row terminators.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources