How Can I Efficiently Import a CSV File with Commas and Double Quotes into SQL Server Using BULK INSERT?

Use BULK INSERT to import CSV files into SQL Server

Challenges of handling comma separated data

When importing CSV files into SQL Server using BULK INSERT, handling data that contains commas within fields can be a challenge. To resolve this issue, consider using a different field delimiter, such as the pipe character (|).

Handling double quotes

If the CSV file is exported from Excel, field values ​​containing commas will be enclosed in double quotes. Unfortunately, BULK INSERT cannot handle double quotes directly. As a workaround, you can import the data into the staging table and then use an UPDATE statement to remove the double quotes.

Track invalid rows

To track rows that failed to import due to invalid data, use the ERRORFILE attribute. This property specifies an error file location to which rows that cannot be imported are written. The following modified BULK INSERT statement demonstrates this:

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- CSV 字段分隔符
    ROWTERMINATOR = '\n',   -- 用于将控制转移到下一行
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

The above is the detailed content of How Can I Efficiently Import a CSV File with Commas and Double Quotes into SQL Server Using BULK INSERT?. For more information, please follow other related articles on the PHP Chinese website!