BULK INSERT Intro

[mickeyj2]

Hi All,

Can someone clue me in on BULK INSERT? I've used it to create a table of one column, and everything worked fine, but now I have a TXT file of about 11 fields. I've tried importing the data by using this as a comma-delimited CSV file, and I've tried the import by using it as a tab-delimited TXT file.

I keep getting several error messages (this one on using the tab-delimited TXT file):


Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 11. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


All of the fields in my table can accept nulls. The table created fine. Here's my code:



USE im
CREATE TABLE Customer_Contacts
(
ContactName nvarchar(50),
CompanyName nvarchar(100),
Address1 nvarchar(255),
Address2 nvarchar(255),
Address3 nvarchar(255),
City nvarchar(32),
State nvarchar(32),
Zip nvarchar(32),
Country nvarchar(50),
Phone nvarchar(32),
Salesrep nvarchar(50)
)

BULK INSERT Customer_Contacts
FROM 'e:\temp\iv\customer_contacts.txt'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n\r',
KEEPNULLS
)




I am NOT familiar with a FORMAT FILE, and I wasn't sure even if I needed one.

NOTE: Some of the data's missing in these fields, but that shouldn't be a problem,should it?
When I was using the comma-delimited file, there was a dangling comma at the end of the row. I tried deleting that, but still didn't work.

Any help is greatly appreciated.

How come seemingly straightforward commands behave with such contrariness in Microsoft? It's never like the tutorials.

If someone could also briefly outline the steps to using the BULK INSERT, please, I'd be grateful.

I thought all I need was a comma-delimited .TXT file, and the command.

Thanks in advance,

mickeyj2

 

[mickeyj2]

Oh, by the way, I've seen several posts about quotes around the data. My files don't have any quotes around any of the data.

Thanks.

 

[gmmastros]

Try removing the [!]\r[/!] from your ROWTERMINATOR. If this doesn't work for you, paste a couple lines of the data file here so we can take a look. It appears as though your data file could contain contact information, so use made up names, addresses, and phone numbers (to protect the innocent).

-George

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

 

[SQLSister]

I agree with George, it looks to me as if your rowterminator is not correct and so it is trying to put too much data into the last column.

You may also have a data problem where your delimiter is also included in one of the fields (a common problem in address fields!).

Usually when I first start developing an import, I bring the file in manually through the wizard first into a work table that the wizard creates. Usually I can then see what issues I might have with the bulk insert rather than trying to solve them randomly. For the row delimiter for instance, you can try different ones until the sample data splits correctly, then you will know which one to specify in your bulk insert. Same thing with the column delimiter. Especially if the file is too large to easily open in Notepad, this lets you get the data in to see it before you try to programmically bring it in.

If this data came from an export of Outlook contacts, we have also had difficulty with Outlook adding in unprintable characters that messed up the import (just mentioning so you know to look for nonalphnumeric characters after you get it in).

"NOTHING is more important in a database than integrity." ESquared

 

[mickeyj2]

Hi,

Thank you for your comments.

A couple of things. I did try importing the data using the import wizard, but it complained about my 11th column (that's the last column that I'm having trouble wtih).

I'm copying a sample of my data, and this is dummy data not identifying real people. There are four rows and I'll separate them with a blank row. Notice how some of the data is missing? Is this a problem?
The data follows...
Form Name: ,G5328,,,,,,,,,

Characteristic,G5328.CHAR_DESC,Value,,,,,,,,
M3,TIER 1 CONTACTS MAILING,Y,,,,,,,,

Contact Name,Customer,Adr1,Adr2,Adr3,City,State,Zip,Ctry,Phone,Slsm
ALDEN BRASHEAR,ACME INTERNATIONAL,P.O.BOX 6578,,,LODI,CA,940746578,US,1 792-589-4581,CF

HENRY ROBINSON,ACME SOIL EQUIPMENT,P.O.BOX 50068,,,SANTA CLARA,CA,93568,US,864-980-4689,AB
JANE PROBST,THE ACME GROUP,1578 CHICAGO STREET,,,ERIE,PA,15024,US,1-654-333-2210,

TERENCE ALVIN,ACME INDUSTRIES,152 OCEAN BOULEVARD,SUITE 500A,,BROOKLYN,NY,11218,US,1-404-555-1212,

Thanks in advance.
mickeyj2


Thanks,
mickeyj2

 

[gmmastros]

Hmmm......

Your data is comma delimited, but you have a FIELDTERMINATOR = '\t'. The [!]\t[/!] indicates a tab delimited file.

I copy/pasted your data in to notepad, removed the extra lines, changed the FIELDTERMINATOR = ',' and ROWTERMINATOR = '\n'. Your data imported fine.

As such, I still think the real problem here is the row terminator. I recommend opening your file using a hex editor (there are many free ones you can download from the internet). Open the file in the hex editor, and you should be able to see what the row terminator is.

For example.... the end of the second line looks like this...

[tt]
65 2c 2c 2c 2c 2c 2c 2c 2c [!]0d 0a[/!] 4d 33 2c 54 49 e,,,,,,,,[!]..[/!]M3,TI[/tt]

Notice the 0d 0a, which corresponds to the 2 periods. In the hex editor I am using, unprintable characters are displayed with a period. od 0a corresponds to Carriage Return/Line Feed. This was put there by notepad, and is probably the reason why my import worked and yours failed. You are probably using something else instead of carriage return/line feed. The first thing you need to do is to make sure you know what this character(s) is. Without that, you may not ever get this to work.





-George

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

 

[mickeyj2]

Hi,

Ok. I should have clarified. I've tried several BULK INSERT statements. At first I had a comma-delimited file and then out of frustration, I tried making a tabbed file. That's where the \t came from. Also initially, my rowterminator was '\n\.

So right now, I'm using the following (along with the comma delimited file, CC.txt):

BULK INSERT Customer_Contacts
FROM 'e:\temp\iv\CC.txt'
WITH
(
FIRSTROW = 7,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)

I finally got the SAMPLE_CONTACTS.XLS FILE (the one I sent you), but now when I apply this sample insert statement to my entire file, I get these errors:


Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 223, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 224, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 225, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 226, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 227, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 228, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 229, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 230, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 263, column 11 (Column 10).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 264, column 11 (Column 10).



Also, I'll try viewing this in the Hex Editor, and I'll let you know what I find.

Thanks,
mickeyj2

 

[gmmastros]

Wait a minute. Are you getting data from Excel? If so.... there's an easier method.

http://support.microsoft.com/kb/321686

I prefer the method shown in: Use Distributed Queries. In particular, the OpenRowSet method.



-George

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

 

[mickeyj2]

Hmmm.... I guess this is a little over my head. I'm not understanding what I'm looking for. I'm see many "0d" and "0a" fields, but they're not at the end of my line? Why is this so convuluated? Someone ran a CSV file for me, and I haven't modified it at all, but just want to import. And I see a couple of periods. After I note this, what am I supposed to do again?

Thanks in advance.

 

[mickeyj2]

Hi gmmastros,

Thanks for all of your advice. I did try the followindg:


SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

And after enabling Ad Hoc Distributed Queries, I received this message:


Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".



You know, I think I'm going to give up on Microsoft, at least for this. It has taken longer (as usual) researching this problem than it would be to just type out the recs.

Thanks anyway for your help.

I appreciate it.

mickeyj2

 

[mickeyj2]

Hello,

I'm back. After many frustrating tries, I think the best thing to do is to use SSIS to create a package, let SSIS clean it, and write the files into the table using a BULK INSERT. I also used a format file to accomplish this.

It finally worked on a Friday evening right before I was heading home.

Hope this helps someone who was as frustrated as I was.

mickeyj2