Bulk insert Unicode Text in SQL Server 2008 results in "unexpected end of file"

49 views Asked by At

I'm trying to create a script that updates a table using a unicode text previously saved in Excel.

My SQL Server version is:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   
Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2 \<X64\> (Build 9200: )

The table is created like this:

CREATE TABLE ActualizarMasiva
(
    [barra] [varchar](150) NOT NULL,
    [articulo] [varchar](150) NULL,
    [und] [decimal](28, 0) NULL,
    [costo] [float] NULL,
    [precio1] [float] NULL,
    [margen1] [float] NULL,
    [precio4] [float] NULL,
    [margen4] [float] NULL,
    tipo_imp [varchar](50) NULL,
    [capacidad] [float] NULL,
    tipo_licor [varchar](50) NULL,
    grado_al [decimal](10,2) NULL
) ON [PRIMARY]

Note that any column can be null except for the first one, and I used varchar(50) even when the data is smaller following this suggestion.

I have a format file, with the following information:

Format specifying the format and lenghts

I've read these articles from Microsoft:

Use BULK INSERT and XML Format File

Structure of Non-XML Format Files

Specify Field and Row Terminators (SQL Server).

And this is the error message after running the bulk insert:

Error message

The log says:

Row 39 File Offset 7415 ErrorFile Offset 0 - HRESULT 0x80004005

But Row 39 has the same format as any other, so I'm not really seeing the error perse.

I've tried using ROWTERMINATOR = '\r' or '\n' and any combination of those two together. I've also tried changing the prefix length and use \0 for the Null terminator if that would be the case. I also checked with Notepad++ and here's a print of it:

Unicode Text

Here's the bulk insert script:

BULK INSERT ActualizarMasiva
FROM 'C:\Users\Administrador\Documents\CSV corrida masiva\actualizarmasivaprueba.txt'
WITH (
    FIRSTROW = 2,   --Contiene headers
    DATAFILETYPE = 'char',
    FORMATFILE = 'C:\Users\Administrador\Documents\CSV corrida masiva\FormatoActualizacionMasiva.fmt',
    ERRORFILE = 'C:\Users\Administrador\Documents\CSV corrida masiva\errorLog'
    );

Maybe I'm missing something but right now I'm not seeing it.

0

There are 0 answers