How to Efficiently Import CSV Files into SQL Server Using BULK INSERT and Handle Common Issues?

Use BULK INSERT to import CSV files into SQL Server

Using BULK INSERT to import CSV files into SQL Server is a common operation, but you may encounter unexpected challenges. This article details how to resolve these issues:

1. Processing commas in CSV data

To handle commas in field values, use a different field separator. For example:

<code class="language-sql">BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '||',  -- 将分隔符更改为 '||' (管道符)
    ROWTERMINATOR = '\n',
    TABLOCK
)</code>

2. Handling double quotes in Excel

Unfortunately, BULK INSERT cannot handle double quotes containing data. After importing the data, use the SQL statement to remove the quotes:

<code class="language-sql">UPDATE SchoolsTemp
SET Description = REPLACE(Description, '"', '')</code>

3. Tracking error data

To track invalid data rows that were skipped during the import process, use the ERRORFILE attribute:

<code class="language-sql">BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '||',
    ROWTERMINATOR = '\n',
    ERRORFILE = 'C:\CSVData\SchoolsErrorRows.csv',
    TABLOCK
)</code>

Lines containing errors will be written to the specified error file.

The above is the detailed content of How to Efficiently Import CSV Files into SQL Server Using BULK INSERT and Handle Common Issues?. For more information, please follow other related articles on the PHP Chinese website!