Explain Codes LogoExplain Codes Logo

Import CSV file into SQL Server

sql
csv-import
data-cleanup
performance
Anton ShumikhinbyAnton Shumikhin·Aug 7, 2024
⚡TLDR

Get your CSV data into SQL Server swiftly with BULK INSERT:

BULK INSERT YourTable
FROM 'your_file.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);

Replace YourTable and your_file.csv to suit your scenario. FORMAT='CSV' ensures smooth handling of typical CSV characteristics. FIRSTROW=2 is handy to omit headers. Don't forget to ensure CSV columns match YourTable schema before importing.

Unleash the parsing prowess of BULK INSERT when handling finicky CSV files from Excel:

BULK INSERT YourTable
FROM 'your_file.csv'
WITH (
    FIELDTERMINATOR = '||', // Because who likes to be normal ('||' - new hipster comma)
    ROWTERMINATOR = '\n',
    FIELDQUOTE = '"', // Handles double quotes like a bouncer at a bar
    ERRORFILE ='error_rows.csv' // Because everybody makes mistakes
);

CSV gotchas & BULK INSERT tricks

You've got your CSV and a SQL Server. Now, let's handle some common curveballs CSVs can throw:

Double-quotes and commas in data

Use || as your field terminator to handle the hipster commas in your CSV data:

WITH (FIELDTERMINATOR = '||')

After importing, you could consider replacing double quotes with an empty string.

Errors while importing

Spot and handle the troublemakers in your data with ERRORFILE. It writes rows with issues into a file specified by you:

WITH (ERRORFILE = 'csv_bad_data.log')

CSV and SQL table schema mismatch

Ensure your CSV column structure matches your destination table schema to avoid a mess.

Skip headers in CSV by using FIRSTROW = 2.

Speeding up your import process

Improve your import performance with TABLOCK to lock the destination table:

WITH (TABLOCK) // All mine! (Cue evil laugh 😈)

Data visualization inside SQL Server

Let's stick to basics. Imagine you're a librarian and each book 📘 in your library represents a table in SQL Server and each page line an exhibit (🖼️) resting in that book is a CSV row:

📚 -> SQL Database
📘 -> SQL Table
🖼️ -> CSV Row

In reality, Importing CSV into SQL Server is lining up those exhibits inside each book. Step-by-step, you are opening each book, reading each line, and as you read, you catalogue it into SQL.

Large imports: Taming the beast

Dealing with substantial datasets? No worries. SSMS Import Wizard and SQL Server Integration Services (SSIS) have got your back.

SQL Server Management Studio's Import Wizard

Visual configuration, quick setup with a step-by-step guide, run it and grab a coffee.

SQL Server Integration Services (SSIS)

Batch it, schedule it, transform it. SSIS has superpowers for bigger tasks.

Security: Who gets in?

Before your data can set foot in SQL Server, it needs the right passkey. Always ensure you're using the right SQL Server Authentication credentials or Windows Authentication permissions.

Post-import cleanup: Time for a shower

CSV files exported from Excel may contain extra quotes. Run a cleanup operation after the import:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, '"', '') // Because “cleanliness is next to godliness” 💦

The Locale War: Commas vs Periods

Regional settings can meddle with commas and periods. Check these:

  • Windows Regional Settings: Change the list separator if needed.
  • Save CSV files with a different separator to prevent import issues.

Pre-flight checks: Before you import

Importing data is like surgery. Don't do it without diagnosing. Some crucial health checks:

  1. Data type compatibility: Match CSV columns' types with SQL table fields.
  2. Preview: Do a quick eyeball test of CSV in a text editor.
  3. Dry run: Practice on a dummy table to keep surprises at bay.

Final word: The import mantra

Maintain data integrity. Always be vigilant of your CSV content, trace errant data, and tweak your import strategies for your specific case.

explain-codes / Sql / Import CSV file into SQL Server
Linked

How to dump the data of some SQLite3 tables?

Python and SQLite: insert into table

How to add header row to a pandas DataFrame

Import SQL file into mysql

Pandas read in table without headers

Python Pandas Error tokenizing data

Search and replace part of string in database

CSV gotchas & BULK INSERT tricksData visualization inside SQL ServerLarge imports: Taming the beastSecurity: Who gets in?Post-import cleanup: Time for a showerThe Locale War: Commas vs PeriodsPre-flight checks: Before you importFinal word: The import mantra
Linked

How to dump the data of some SQLite3 tables?

Python and SQLite: insert into table

How to add header row to a pandas DataFrame

Import SQL file into mysql

Pandas read in table without headers

Python Pandas Error tokenizing data

Search and replace part of string in database