Bulk INSERT help

[penndro]

Dear all,
I could use some help with the script below that I am using to import a HUGE .CSV file into SQL Server 2005.

Here is the code that I am running:

CREATE TABLE AnalysisFile

(BillingDate TEXT,
Soldto INT,
PickupFrom VARCHAR(40),
PostalCode VARCHAR(40),
Matnr INT,
Weight INT,
Desloc INT,
Shippoint INT,
Markup INT,
Pieces INT)

GO

BULK INSERT AnalysisFile
FROM 'h:\product management\extracted files\200812.csv'
WITH
( FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\t\n'
)
GO

When I run this code I get these errors:

Msg 4866, Level 16, State 8, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 10. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".


I have tried running this with several variations Like:

FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

or

FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'

or

FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'

But none of these work.

One thing to note is the import works fine if I delete the header row (1st row of records from the CSV file; however the file has to be split to open it and accomplish this). In doing so I would have to work with over 1,200 individual files - because excel and notepad max out on the size...

 

[gmmastros]

It's difficult to give advice about bulk loading because 99% of the time, the advice depends on the data.

Can you post the first 5 or so rows so we can see what the data looks like? You should change the data so we don't see any sensitive data like names or phone numbers.

Also, it sometimes helps to open the file using a hex editor. You can then see the ascii character that separates the row. Often times, it's a carriage return/line feed (but not always). Anyway, try... ROWTERMINATOR = '\r\n'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

Thank you George for trying to help out with this.

I tried the 'r\n\'; but that gave me this error:

Msg 4866, Level 16, State 8, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 10. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

Here are some lines from the CSV file (opened in NOTEPAD):
"SUBSTR(S.BILLINGDATE,1,6)","SOLDTO","PICKUPFROM","POSTALCODE","MATNR","WEIGHT","DESTLOC","SHIPPOINT","MARKUP","SUM(S.QUANT)"
"200807","0005103802","0005301005","01536-1632","000000000000000081","4.0000","5","4004","0","1"
"200807","0005103802","0005301005","54016-9112","000000000000000081","4.0000","4","4004","0","1"
"200807","0005102941","0005304852","22042 ","000000000000000081","4.0000","4","4004","0","1"
"200807","0005102941","0005304852","55372 ","000000000000000081","7.0000","4","4004","0","1"
"200807","0005102941","0005304852","48124 ","000000000000000081","5.0000","3","4004","0","1"
"200807","0005103224","0005301336","27513-8467","000000000000000081","5.0000","6","4006","0","1"
"200807","0005112231","0005314943","43228-1672","000000000000000072","3.0000","2","4004","0","1"
"200807","0005102941","0005304852","98033 ","000000000000000081","10.0000","8","4004","0","1"
"200807","0005102941","0005304852","17870 ","000000000000000081","7.0000","4","4004","0","1"
"200807","0005103229","0005304465","23464-6891","000000000000000081","7.0000","4","4004","0","1"
"200807","0005105184","0005302709","91325-1639","000000000000000081","3.0000","6","4006","0","1"
"200807","0005103802","0005301005","55437-1220","000000000000000081","5.0000","4","4004","0","1"
"200807","0005102941","0005304852","12170 ","000000000000000081","4.0000","5","4004","0","1"
"200807","0005103224","0005301336","74074-2128","000000000000000081","9.0000","3","4006","0","1"
"200807","0005102941","0005304852","10598 ","000000000000000081","5.0000","4","4004","0","1"
"200807","0005102941","0005304852","85614 ","000000000000000081","4.0000","7","4004","0","1"
"200807","0005102941","0005304852","15301 ","000000000000000081","6.0000","3","4004","0","1"
"200807","0005103224","0005301336","07110-2368","000000000000000081","5.0000","6","4006","0","1"
"200807","0005112168","0005314844","37909-2873","000000000000000081","6.0000","3","4004","0","1"
"200807","0005103802","0005301005","02026-3103","000000000000000081","16.0000","5","4004","0","1"
"200807","0005100309","0005302783","43018 ","000000000000000262","4.0000","5","4006","5","1"
"200807","0005105184","0005302709","32082-3718","000000000000000081","3.0000","5","4006","0","1"
"200807","0005103802","0005301005","01474-1116","000000000000000081","5.0000","5","4004","0","1"
"200807","0005101443","0005304477","37640-8067","000000000000000081","5.0000","3","4004","0","1"
"200807","0005103229","0005304465","12401-7356","000000000000000081","11.0000","4","4004","0","1"
"200807","0005105184","0005302709","71909-7421","000000000000000081","6.0000","3","4006","0","1"
"200807","0005103802","0005301005","01760-1941","000000000000000081","6.0000","5","4004","0","1"
"200807","0005100309","0005302783","40505 ","000000000000000262","4.0000","5","4006","5","1"
"200807","0005111978","0005314549","94549-3958","000000000000000081","3.0000","8","4004","0","1"
"200807","0005110603","0005312708","45068-9203","000000000000000081","7.0000","1","4004","0","1"
"200807","0005102941","0005304852","46989 ","000000000000000081","4.0000","2","4004","0","1"
"200807","0005103802","0005301005","01010-0298","000000000000000081","5.0000","5","4004","0","1"
"200807","0005102941","0005304852","40311 ","000000000000000081","6.0000","2","4004","0","1"
"200807","0005102941","0005304852","36420 ","000000000000000081","3.0000","4","4004","0","1"
"200807","0005103229","0005304465","90056-1038","000000000000000081","7.0000","8","4004","0","1"

 

[penndro]

I ran this:

BULK INSERT AnalysisFileTest
FROM 'u:\product management\hari\extracted files\testdata.csv'
WITH
( FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\r\n'
)
GO

Got no errors (0 row(s) affected). So it ran but imported nothing.

 

[gmmastros]

The quotes are causing you problems. Can you control the way the data file is generated so that the quotes are not added to the file? It appears as though you don't need the file to be quote-comma delimited.

quote-comma delimited is nice if it's possible to have commas in your data, but that doesn't look like a possibility.

If you can't take out the quotes, then I suggest you use a format file. Format files are kind-of-a-pain to set up, but gives you lots of extra functionality,too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

Unfortunately I can't change the file so the FILEFORMAT option would be most fiesable. How would I go about doing that in this case?

 

[gmmastros]

This can really burn up some time. Here's some info to get you started, you'll have to clean it up a bit. Basically, you need to get a little fancy with your delimiters. Your format file needs to have a blank dummy column so that you can treat the first quote as a delimiter. This dummy column does not actually get loaded in to the table. Then, you treat the other delimiters as a Quote-Comma-Quote, except for the last one, which is treated as a quote-carriage return-line feed.

To make things simple, I created a temp table with all varchar columns (just to validate that this is working). First, the format file looks like this:

9.0
11
1  SQLCHAR  0  50 "\""    0  DummyColumn SQL_Latin1_General_CP1_CI_AS
2  SQLCHAR  0  50 "\",\""    1  BillingDate SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR  0  50 "\",\"" 2  Soldto      SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR  0  50 "\",\"" 3  PickupFrom  SQL_Latin1_General_CP1_CI_AS
5  SQLCHAR  0  50 "\",\"" 4  PostalCode  SQL_Latin1_General_CP1_CI_AS
6  SQLCHAR  0  50 "\",\"" 5  Matnr       SQL_Latin1_General_CP1_CI_AS
7  SQLCHAR  0  50 "\",\"" 6  Weight      SQL_Latin1_General_CP1_CI_AS
8  SQLCHAR  0  50 "\",\"" 7  Desloc      SQL_Latin1_General_CP1_CI_AS
9  SQLCHAR  0  50 "\",\"" 8  Shippoint   SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR  0  50 "\",\"" 9  Markup      SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR  0  50 "\"\r\n"  10 Pieces    SQL_Latin1_General_CP1_CI_AS

And the code I used to import the data was....

Create TABLE #AnalysisFile
(BillingDate VarChar(100),
Soldto VarChar(100),
PickupFrom VARCHAR(40),
PostalCode VARCHAR(40),
Matnr VarChar(100),
Weight VarChar(100),
Desloc VarChar(100),
Shippoint VarChar(100),
Markup VarChar(100),
Pieces VarChar(100))

BULK INSERT #AnalysisFile
    FROM '[!]C:\Data.txt[/!]'
    WITH
    (   FIRSTROW = 2,
        MAXERRORS = 0,
        FORMATFILE = '[!]C:\dataFormat.fmt[/!]'
    )

Select * From #AnalysisFile

Drop Table #AnalysisFile

Note that you will need to change the name of the files.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

How would I create that .fmt file.?

 

[gmmastros]

Copy/paste to notepad and save it. It's really just an ASCII file. Nothing special. I encourage you to test this using the sample data that you posted earlier (so that it's small, and therefore fast to test).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should mention (just so that it's clear). The contents of the fmt file should be this:

9.0
11
1  SQLCHAR  0  50 "\""    0  DummyColumn SQL_Latin1_General_CP1_CI_AS
2  SQLCHAR  0  50 "\",\""    1  BillingDate SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR  0  50 "\",\"" 2  Soldto      SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR  0  50 "\",\"" 3  PickupFrom  SQL_Latin1_General_CP1_CI_AS
5  SQLCHAR  0  50 "\",\"" 4  PostalCode  SQL_Latin1_General_CP1_CI_AS
6  SQLCHAR  0  50 "\",\"" 5  Matnr       SQL_Latin1_General_CP1_CI_AS
7  SQLCHAR  0  50 "\",\"" 6  Weight      SQL_Latin1_General_CP1_CI_AS
8  SQLCHAR  0  50 "\",\"" 7  Desloc      SQL_Latin1_General_CP1_CI_AS
9  SQLCHAR  0  50 "\",\"" 8  Shippoint   SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR  0  50 "\",\"" 9  Markup      SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR  0  50 "\"\r\n"  10 Pieces    SQL_Latin1_General_CP1_CI_AS

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

I copied the file. Saved it in notepad as .txt then chagned the file extension to .fmt

I am getting this message:

Msg 4862, Level 16, State 1, Line 13
Cannot bulk load because the file "u:\hari\fileformat.fmt" could not be read. Operating system error code (null).

It won't read .txt if I leave in txt format.

Not sure how to get fmt file.

 

[gmmastros]

Believe it or not, try putting a blank like at the the of the format file. This will probably allow it to work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

Hi George,

I could not understand your last comment. Do you suggest that i put a blank LINE at the end of the format file code?

If so again how I save the code to make a .fmt file?

 

[gmmastros]

Open it in notepad. Go to the bottom of the file. Hit enter to add a new line at the bottom. Save the file.

That should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

George,
Still no luck. Can you make me the .fmt file and attach here? That would be really appreciated.

Thanks
Sandi