Import CSV file into SQL Server

All of the answers here work great if your data is "clean" (no data constraint violations, etc.) and you have access to putting the file on the server. Some of the answers provided here stop at the first error (PK violation, data-loss error, etc.) and give you one error at a time if using SSMS's built in Import Task. If you want to gather all errors at once (in case you want to tell the person that gave you the .csv file to clean up their data), I recommend the following as an answer. This answer also gives you complete flexibility as you are "writing" the SQL yourself.

Note: I'm going to assume you are running a Windows OS and have access to Excel and SSMS. If not, I'm sure you can tweak this answer to fit your needs.

1. Using Excel, open your .csv file. In an empty column you will write a formula that will build individual `INSERT`statements like `=CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO")` where A1 is a cell that has the first name data and A2 has the last name data for example.
- `CHAR(10)` adds a newline character to the final result and `GO` will allow us to run this `INSERT` and continue to the next even if there are any errors.

2. Highlight the cell with your `=CONCATENATION()` formula
3. Shift + End to highlight the same column in the rest of your rows
4. In the ribbon > Home > Editing > Fill > Click Down
- This applies the formula all the way down the sheet so you don't have to copy-paste, drag, etc. down potentially thousands of rows by hand
5. Ctrl + C to copy the formulated SQL `INSERT` statements
6. Paste into SSMS
7. You will notice Excel, probably unexpectedly, added double quotes around each of your `INSERT` and `GO` commands. This is a *"feature" (?)* of copying multi-line values out of Excel. You can simply find and replace `"INSERT` and `GO"` with `INSERT` and `GO` respectively to clean that up.
8. Finally you are ready to run your import process
9. After the process completes, check the Messages window for any errors. You can select all the content (Ctrl + A) and copy into Excel and use a column filter to remove any successful messages and you are left with any and all the errors.

This process will definitely take longer than other answers here, but if your data is "dirty" and full of SQL violations, you can at least gather all the errors at one time and send them to the person that gave you the data, if that is your scenario.

As it was stated above, you need to add FORMAT and FIELDQUOTE options to bulk insert .CSV data into SQL Server. For your case SQL statement will look like this:

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

Though `BULK INSERT` in SSMS is great for a one-time import job, depending on your use case you may need some other options inside SSMS or using 3rd parties. [Here][1] is a detailed guide describing various options to import CSV files to SQL Server, including ways to automate (I mean schedule) the process and specify FTP or file storages for CSV location.


[1]:

[To see links please register here]

From *[How to import a CSV file into a database using SQL Server Management Studio][1]*, from 2013-11-05:

> First create a table in your database into which you will be importing
> the CSV file. After the table is created:
>
> - Log into your database using SQL Server Management Studio
>
> - Right click on your database and select **Tasks -> Import Data...**
>
> - Click the **Next >** button
>
> - For the Data Source, select **Flat File Source**. Then use the **Browse** button to select the CSV file. Spend some time configuring how you want the data to be imported before clicking on the **Next >** button.
>
> - For the Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the **Server name**; Check **Use SQL Server Authentication**, enter the **User name**, **Password**, and **Database** before clicking on the **Next >** button.
>
> - On the Select Source Tables and Views window, you can Edit Mappings before clicking on the **Next >** button.
>
> - Check the **Run immediately** check box and click on the **Next >** button.
>
> - Click on the **Finish** button to run the package.


[1]:

[To see links please register here]

Maybe not exactly what you're asking, but another option is to use the [CSV Lint plug-in for Notepad++][1]

The plug-in can validate the csv data beforehand, meaning check for bad data like missing quotes, incorrect decimal separator, datetime formatting errors etc. And instead of `BULK INSERT` it can convert the csv file to an SQL insert script.

[![enter image description here][2]][2]

The SQL script will contain `INSERT` statements for each csv line in batches of 1000 records, and also adjust any datetime and decimal values. The plug-in automatically detects datatypes in the csv, and it will include a `CREATE TABLE` part with the correct data types for each column.

[![enter image description here][3]][3]


[1]:

[To see links please register here]

[2]:

[3]:

If anyone wants to import csv using powershell

## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input SQL Server Variables and CSV path
$csvPath = "D:\Orders.csv"
$csvDelimiter = ","
$serverName = "DESKTOP-DOG5T0Q\SQLEXPRESS"
$databaseName = "OrderDetails"
$tableSchema = "dbo"
$tableName = "Orders"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -header "Id","Country","Price","OrderQuantity" -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

Source: [Import csv into SQL server (with query OR without query using SSMS)][1]


[1]:

[To see links please register here]