Can't BULK INSERT Into SQL Table From CSV File

Question

I seem to be unable to run a bulk insert on a SQL table from a CSV file that gets sent through FTP to our server. It runs without error, but alters 0 rows.

If I copy the data into another file, it works, but I need to be able to do this automatically without messing with the file myself. Opening them both, the only differences I can see are that the line breaks are CRLF on the new file, and just LF on the original. Encoding looks to be the same as well on both, so I must be missing something not in the other similar questions asked.

Sample script below:

    BULK INSERT dbo.t_process_order_import
        FROM 'C:\Root\Product Data\H888 ProcOrd.csv'
        WITH
        (
            FIRSTROW = 2, -- as 1st one is header
            FIELDTERMINATOR = '|',
            ROWTERMINATOR = '\n',
            TABLOCK
        )

Sample CSV Data:

ProcessOrder|ProductNumber|MaterialDescription|OrderQuantity|WorkCenter|StartDate|StartTime
001001101111|000000000000101111|TEST|40500.000 |CKET02|20201014|220000
001001221111|000000000000101111|TEST|14124.000 |GHFD02|20210225|032000

Which dbms are you using? (BULK INSERT is a product specific feature.) — jarlh, Jun 16 '22 at 18:01
You need to use `FORMAT='CSV'` otherwise it won't interpret quotes correctly — Charlieface, Jun 16 '22 at 19:11

user_0 · Accepted Answer · 2022-06-16T18:01:56.127

1

You need to use ROWTERMINATOR='0x0a'
Your code will become:

BULK INSERT dbo.t_process_order_import
    FROM 'C:\Root\Product Data\H888 ProcOrd.csv'
    WITH
    (
        FIRSTROW = 2, -- as 1st one is header
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '0x0a',
        TABLOCK
    )

As suggested, I try to improve with my source: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver16

At paragraph "Specifying \n as a Row Terminator for Bulk Import"

Reporting here what is important for the question:

When you specify \n as a row terminator for bulk import, or implicitly use the default row terminator, bcp and the BULK INSERT statement expect a carriage return-line feed combination (CRLF) as the row terminator. If your source file uses a line feed character only (LF) as the row terminator - as is typical in files generated on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. For example, in a BULK INSERT statement

edited Jun 16 '22 at 18:01

answered Jun 16 '22 at 17:57

user_0

3,173
20
33

1

it'd be worth mentioning what `0x0a` is (https://stackoverflow.com/questions/33208518/difference-between-0x0a-and-0x0d) and _why_ they need to use that instead of `\n` – WOUNDEDStevenJones Jun 16 '22 at 17:59
1

@WOUNDEDStevenJones, you are right! Have my up vote and my explanation. – user_0 Jun 16 '22 at 18:03
That was the fix! Thank you! I'll mark as the answer shortly. And thanks for the link @WOUNDEDStevenJones, that makes sense now. – HA-11-SIAWN Jun 16 '22 at 18:05
You also need `FORMAT='CSV'` otherwise quotes don't get interpreted correctly – Charlieface Jun 16 '22 at 19:11

Can't BULK INSERT Into SQL Table From CSV File

1 Answers1