views:

461

answers:

1

I'm having problems bulk inserting data into my SQL Express 2005 DB.

I'm using the following code:

BULK INSERT [dbEPi].[dbo].[postcode]
FROM 'C:\Development\postnummerRegister.txt'
WITH 
(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

and I get the following error:

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 5. 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)".

The datafile I'm trying to import, can be located here.

Any help appreaciated.

Update 1 Here's an example of the data I'm using:

0001    OSLO 0301 OSLO K
0010    OSLO 0301 OSLO F
0014    OSLO 0301 OSLO K
0015    OSLO 0301 OSLO K
0016    OSLO 0301 OSLO K
0017    OSLO 0301 OSLO K
0018    OSLO 0301 OSLO G
0020    OSLO 0301 OSLO K
0021    OSLO 0301 OSLO K

Update 2

I managed to import them now. All I did was copy the text into a new file, and save this as test.txt. I have no idea why this works. It might be because of the file encoding. The file was UTF8 encoded.

+2  A: 

Firstly the rowterminators appear to be windows line endings 0D 0A in the file. (CR LF). I believe this translates to \r\n. However the issue still holds.

There appears to be a hotfix from MS http://support.microsoft.com/kb/935446

pjp
I've tried \r\n too, but got the same error message.
Steven
See hotfix link
pjp
I managed to import them now. All I did was copy the text into a new file, and save this as test.txt. I have no idea why this works. It might be because of the file encoding? The file should be UTF8 encoded.
Steven
Maybe it's these characters Ø, Æ, Å I hadn't noticed them looking at the top part of the file...
pjp
No, that can't be. Because I just copied the same data over in a new file and saved it, Ande as long as it is unicode, it should handle Æ, Ø and Å quite well. I think I "broke" the file when I changed the file encoding using PSPad.
Steven