Post reply

BULK insert from a csv/txt file

  • April 16, 2019 at 8:48 am

    #3404691

    I want to import from a csv file with a bulk insert.

    BULK INSERT ...
    FROM 'C:\....txt'
    WITH
    (

    FIELDTERMINATOR = '~', --CSV field delimiter
    ROWTERMINATOR = '\n', --Use to shift the control to next row
    TABLOCK
    )

    It works many times, but I have a problem I cannot solve.

    If there is a field in my table, that is declared (for example) as decimal, there is a problem, because in the csv file the number is 1.234,5 and not 1234.5

    Is it possible to replace in a column all data automaticly?

    To code: remove in colum 3 the dot and replace the comma with a dot

    Thanks

  • April 16, 2019 at 9:02 am

    #3405091

    I guess you can load the data in some sort of staging table first - clean it up there and then load it in the destination table

    Thanks

  • April 16, 2019 at 3:06 pm

    #3410608

    There is no easy way to do this with BULK INSERT. A CSV is comma separated. Formatting a number with a comma creates a separation to a new field. I know culturally this is correct, but from the standpoint of machine readability, this won't work.

     

    You need to text parse the file and replace the commas with periods, and remove the periods that represent the change between a thousands and hundreds place. Doing this is complex, as the computer needs to understand this is a number and not a string, which is how it is formatted now.

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

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