Post reply

Help with BULK INSERT of Unicode File

  • February 9, 2010 at 10:26 pm

    #386285

    Hi fellow SQL friends,

    I'm having issues using BULK INSERT to import data from a unicode data

    file I created. I know this seems to be a common issue but I've spent

    a great amount of time googling, trying various BULK INSERT options &

    even manually editing the data file...but to no avail. Here's a quick

    recap of what I did:

    1. Exported data from <Table 1> to data file using the import/export

    wizard. Since <Table 1> has some nvarchar columns, I checked the

    "Unicode" box as an option during the export process. I used 'double

    quotes' as the text qualifier; 'comma' as field terminator; '{CR}{LF}'

    as row terminator.

    2. Now when I turn around trying to BULK INSERT the data file I just

    created from step 1 above into the a table (<Table 2>) with the same

    exact structure as <Table 1> using the following:

    bulk insert <Table 2> from 'File.txt' with

    (

    FORMATFILE = 'FormatFile.fmt',

    DATAFILETYPE = 'widenative',

    errorfile = 'Error_Output.txt',

    batchsize = 100,

    FIELDTERMINATOR = ',\0',

    ROWTERMINATOR = '\r\0\0'

    );

    Here's the error msg I get:

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (DateTime).

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

    Can anyone shed some light please? I would really appreciate some help because I've spent way more time than I need to on this.

    Thanks in advance!

  • February 10, 2010 at 6:38 am

    #1116761

    Hi,

    Looks like your row and field terminators aren't being recognised or data within your file is being misinterpreted. Have you seen this?

    http://msdn.microsoft.com/en-us/library/ms191485.aspx

    Note from link:

    When you use native or Unicode native format, use length prefixes rather than field terminators. Native format data can conflict with terminators because a native-format data file is stored in the Microsoft SQL Server internal binary data format.

    Iain

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply