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.

Improve this question
2
  • Hi, i am not completely sure just something to check after last field is there \t before the \n ? Commented Aug 18, 2009 at 8:46
  • There is no tab after the last field. Just line terminator. Commented Aug 18, 2009 at 9:05

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
Maybe it's these characters Ø, Æ, Å I hadn't noticed them looking at the top part of the file...
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.