Efficient CSV Import into SQL Server Using BULK INSERT and Alternative Methods

Introduction

Importing data from a CSV file into SQL Server is a common task that database administrators and developers encounter. The BULK INSERT command in SQL Server provides an efficient way to import large volumes of data quickly. This tutorial will guide you through the process of importing a CSV file into SQL Server using BULK INSERT, handling special cases such as commas within fields, double-quoted strings, and tracking errors during the import process.

Prerequisites

Before starting, ensure that:

1. You have access to an instance of SQL Server.
2. The target table (SchoolsTemp in this example) is created with appropriate columns matching your CSV data.
3. Necessary permissions are granted for importing data into the database.

Understanding the BULK INSERT Command

The BULK INSERT command allows you to load a large volume of data from a file into SQL Server tables efficiently. It supports various options to handle different data formats and delimiters.

Basic Syntax

BULK INSERT [destination_table]
FROM 'file_path'
WITH (
    FIELDTERMINATOR = delimiter,
    ROWTERMINATOR = row_delimiter,
    FIRSTROW = starting_row,
    ERRORFILE = error_file_path,
    FORMAT = file_format, 
    FIELDQUOTE = field_quote_character
)

Key Options

• FIELDTERMINATOR: Specifies the character that separates fields in your CSV file.
• ROWTERMINATOR: Defines the newline character that denotes a new row.
• FIRSTROW: Indicates which line to start reading from (useful for skipping headers).
• ERRORFILE: Path where rows with errors during import are logged.
• FORMAT and FIELDQUOTE: Specifically useful when dealing with data enclosed in quotes.

Handling Special Cases

1. Commas Within Fields

When your CSV fields contain commas, they can be enclosed within double quotes (a standard practice when exported from Excel). Use the FIELDQUOTE option to specify that double quotes are used for quoting fields:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH (
    FORMAT = 'CSV',
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  
    ROWTERMINATOR = '\n',   
    TABLOCK
)

2. Double-Quoted Fields

When your CSV data is exported from Excel, fields containing commas are typically enclosed in double quotes. The FORMAT and FIELDQUOTE options help SQL Server understand this format:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH (
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  
    ROWTERMINATOR = '\n',   
    TABLOCK
)

3. Error Tracking

To track rows that fail during import, use the ERRORFILE option to specify a file path where errors will be logged:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  
    ROWTERMINATOR = '\n',   
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
)

Alternative Methods

Using SQL Server Management Studio (SSMS)

For those preferring a graphical interface, SSMS offers an import wizard:

1. Right-click on the target database and select Tasks > Import Data.
2. Choose Flat File Source as your data source and browse to your CSV file.
3. Follow prompts to configure data types and mappings.
4. Execute the package to complete the import.

Using Excel’s Alternate Delimiters

If commas are causing issues, consider saving the Excel file with a different delimiter (e.g., pipe |). This requires changing Windows’ list separator setting temporarily:

1. Open Control Panel > Region > Additional Settings.
2. Change the List separator to a character like |.
3. Save the Excel file as CSV from Excel’s "Save As" menu.

Best Practices

• Always back up your database before performing bulk operations.
• Test imports on a smaller data set to ensure correct configuration.
• Regularly review error files to identify and rectify data issues.

By understanding these techniques, you can efficiently manage the import of CSV data into SQL Server while handling common challenges related to formatting and errors.