Search code examples
sqlsql-serverbulkinsertbulk

Bulk INSERT not inserting data

When running the code below management studio shows an error in which it is set down.

I need to insert simple text data into the tables through BULK INSERT. The code for insertion is as follows:

BULK INSERT Pais FROM 'C:\[bulkdata]\shared\01-Pais.txt' 
WITH(MAXERRORS = 0, CODEPAGE = 'ACP', FIELDTERMINATOR = 'ø', ROWTERMINATOR = '\n', KEEPNULLS,
ERRORFILE = 'C:\[bulkdata]\shared\teste.txt')

The file I'm using to test this command has only one line for inserting data, teste.txt:

BrasilØ01058

The error returned is as follows:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

teste.txt:

BrasilØ01058BrasilØ01058

It seems to me that this is trying to insert the value twice on the same line, but as the setting shows, it usually inserts into other projects. Can my user be without permission? I have already created a specific user with all permissions and also set the server the permission to perform bulk operations.

teste.txt.Error.Txt:

Row 1 File Offset 0 ErrorFile Offset 0 - HRESULT 0x80004005

enter image description here

Solution

  • It seems to be a character issue, you can try with another one like §. You just need to do a little tweak:

    FIELDTERMINATOR = '§'

    And your 01-Pais.txt file:

    Brasil§01058

    Update: It seems that also a mix of database collation and file encoding issue, could have messing up the bulk operation. Saving the file as ANSI, instead of UTF-8 seems to have corrected the original issue. It seems that also using CODEPAGE=1252 or DATAFILETYPE = 'widechar' should have fixed the issue.