locked
Bulk Data Error, MYSQL to SQL Server RRS feed

  • Question

  • Hi, Everyone,

    I have data in bulk, like this. (consist of 2 column id and name). The sparator is "|"

    01|Jhonson
    02|Jhonson
    Park

    on the top example, seen name with id 02 is very long so, data of name make a new line.
    and then i have error, cause sql server assume, "Park" is different row. 

    Script make a bulk :
    SELECT * FROM test   INTO OUTFILE '/tmp/database/test.csv' FIELDS TERMINATED BY '|';

    Script to import data use bulk.
    BULK INSERT dbo.TEST FROM 'C:\csv\TEST.csv' WITH (DATAFILETYPE = 'char', BATCHSIZE=100,FIELDTERMINATOR='|',ROWTERMINATOR='0x0a');N

    Note :

    Fyi, I will migrate data from mysql to sql server. When i create a bulk, this in mysql and then when i insert data bulk in the sql server.

    error :

    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 175, column 1 (id).

    Thanks for your help, i wait your opinioun about this problem.

    thanks,
    -infi-

    Friday, August 15, 2014 10:56 AM

Answers

  • When you define the delimiter for a field, the delimiter must be a character sequence that cannot appear in the data for that field. That is, there is no problems having line breaks in the data - as long as it is not in the last field. On the other the hand, this is the only field you have can have a pipe character.

    So you either need to change the order of the fields, or add a different terminator.

    Since I don't know MySQL, I don't know exactly what you can do with SELECT INTO OUTFILE, but an obvious possibiliy is:

    SELECT *, '*' FROM test   INTO OUTFILE '/tmp/database/test.csv' FIELDS TERMINATED BY '|';

    Then you modify the BULK INSERT statement as:

    BULK INSERT dbo.TEST FROM 'C:\csv\TEST.csv' WITH (DATAFILETYPE = 'char', BATCHSIZE=100,FIELDTERMINATOR='|',ROWTERMINATOR='|*\n');

    Now, hopefully the \n is not interpreted as \r\n here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 15, 2014 12:55 PM