csv file data into a table

  • Hi all,

    I am running SQL Server 2008R2 and trying to import .csv file data into a table.

    The problem is that i have some values in the file that are missing (NULL).

    Data:

    1,

    9999000002,

    8888000004,

    7777000019,0016000000KOCoG

    7777000020,0016000000fGXCb

    6666000021,

    5555000022,

    Table:

    CREATE TABLE [dbo].[A](

    [coid] [varchar](256) NULL,

    [said] [varchar](15) NULL

    );

    Code:

    bulk insert dbo.A

    from 'C:\TEMP\temp.csv'

    with (

    fieldterminator = ','

    ,rowterminator = ''

    );

    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 1, column 1 (coid).

    Question: is there way to filter NULL values? Perhaps there is another way, besides bulk insert to do it?

    Thanks,

  • rightontarget (11/12/2013)


    Hi all,

    I am running SQL Server 2008R2 and trying to import .csv file data into a table.

    The problem is that i have some values in the file that are missing (NULL).

    Data:

    1,

    9999000002,

    8888000004,

    7777000019,0016000000KOCoG

    7777000020,0016000000fGXCb

    6666000021,

    5555000022,

    Table:

    CREATE TABLE [dbo].[A](

    [coid] [varchar](256) NULL,

    [said] [varchar](15) NULL

    );

    Code:

    bulk insert dbo.A

    from 'C:\TEMP\temp.csv'

    with (

    fieldterminator = ','

    ,rowterminator = ''

    );

    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 1, column 1 (coid).

    Question: is there way to filter NULL values? Perhaps there is another way, besides bulk insert to do it?

    Thanks,

    Stop using a blank as a row terminatior and this problem will go away.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just to expand a little on Jeff's answer. You can use as the row terminator (new line character).

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

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