Import CSV file into SQL Server

Question

I am looking for help to import a .csv file into SQL Server using BULK INSERT and I have few basic questions.

Issues:

1. The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling these data?

2. If the client creates the CSV from Excel then the data that have comma are enclosed within "" (double quotes) [as the below example] so how do the import can handle this?

3. How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)

Here is the sample CSV with header:

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

And SQL statement to import:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Solution 1

Based SQL Server CSV Import

1) The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling these data?

Solution

If you're using , (comma) as a delimiter, then there is no way to differentiate between a comma as a field terminator and a comma in your data. I would use a different FIELDTERMINATOR like ||. Code would look like and this will handle comma and single slash perfectly.

2) If the client create the csv from excel then the data that have comma are enclosed within " ... " (double quotes) [as the below example] so how do the import can handle this?

Solution

If you're using BULK insert then there is no way to handle double quotes, data will be inserted with double quotes into rows. after inserting the data into table you could replace those double quotes with ''.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)?

Solution

To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

Solution 2

From How to import a CSV file into a database using SQL Server Management Studio, 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.

Solution 3

2) If the client create the csv from excel then the data that have comma are enclosed within " ... " (double quotes) [as the below example] so how do the import can handle this?

You should use FORMAT = 'CSV', FIELDQUOTE = '"' options:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Solution 4

The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import. This is described here.

Solution 5

Because they do not use the SQL import wizard, the steps would be as follows:

1. Right click on the database in the option tasks to import data,

2. Once the wizard is open, we select the type of data to be implied. In this case it would be the

Flat file source

We select the CSV file, you can configure the data type of the tables in the CSV, but it is best to bring it from the CSV.

3. Click Next and select in the last option that is

SQL client

Depending on our type of authentication we select it, once this is done, a very important option comes.

4. We can define the id of the table in the CSV (it is recommended that the columns of the CSV should be called the same as the fields in the table). In the option Edit Mappings we can see the preview of each table with the column of the spreadsheet, if we want the wizard to insert the id by default we leave the option unchecked.

Enable id insert

(usually not starting from 1), instead if we have a column with the id in the CSV we select the enable id insert, the next step is to end the wizard, we can review the changes here.

On the other hand, in the following window may come alerts, or warnings the ideal is to ignore this, only if they leave error is necessary to pay attention.

This link has images.

View more solutions

Comments

[Denis]

May be SSMS: How to import (Copy/Paste) data from excel can help (If you don't want to use BULK NSERT or don't have permissions for it).

[Walter Mitty]

This is beside the point, but your sample CSV file should load into MS Access without trouble.

[Prabhat]

Thanks for the help. Reg the Solution#1: Can we create || separated value file from Excel? Because around 20% of the source files are created using Excel by the client.

[Vishwanath Dalvi]

@Prabhat How you're loading Excel files into SQL Server?

[Prabhat]

These are not Excel files that I am loading. Client is using Excel to create .CSV files (for 20% of the source data that our application import). And I was asking if we create csv files using Excel how can we have || as column value separator?

[busytools]

a maybe more user-friendly wrapper around the BulkCopy classes busybulkcopy.codeplex.com

[qxotk]

If you have influence on how the client creates CSV files from Excel, you can teach them how to set the separator character in Excel (and well, it's no longer a "comma" separated file anymore, it would be pipe (|) separated, for instance. Given the hoops you're jumping through for this, and if you have SSIS - I recommend you check into it. Versions of SQL Server 2012 and later have a very robust SSIS designer (also in VS 2012 and later) that would enable your client to simply send you the excel files instead of csv.

[SierraOscar]

It would be nice if you gave attribution to the page where you copy/pasted this answer from...

[NReilingh]

Downvote: Importing XLS files with SSIS is terrible. SSIS will try to guess at the datatypes of the Excel data, but can guess wrong and there's nothing you can do about it. Much better to use CSV.

[Zee]

Well, I'd suggest csv too, but if you had read the OP's scenario, he had some special scenarios especially with delimiters which are not an issue with xls sheets. Usually special case scenarios like these do not require an extensive solution, but a fix that preserves the data. While uploading the file, SSIS lets you choose the data mapping between source and destination tables which again, eases the effort involved. Which is why this method was suggested as a quick hack.

[NReilingh]

SSIS can already handle CSV text delimiters. If you're using SSIS anyway, going to the trouble of saving your CSV as an XLS first just strikes me as adding potential breakage for no reason.

[DtechNet]

I'm not sure this is entirely accurate. You can deal with double quotes in SQL Bulk Insert. There is a Stack Overflow on this topic and one can use format files to teach Bulk Insert varying deliminators. stackoverflow.com/questions/25726385/… advancesharp.com/blog/1083/…

[bside]

It is not necessary to pre-create the table, it can be created during the import process

[kristianp]

Note that the FORMAT specifier is only available since SQL Server 2017.

[Auspex]

I love that you just cut & paste from a web page with the oh-so-useful line "Spend some time configuring how you want the data to be imported". That was everything I'm looking for: I don't seem to be able to configure it at all!

[Auspex]

Oh, and "Check the Use SQL Server Authentication radio button" is wrong, as you may very well want to use Windows Authentication. It's whichever works for you.

[Auspex]

Also, I routinely have CSV files too large for Excel.

[Vikas Lalwani]

thanks found a step by step procedure with images for implement above procedure, worth a look : qawithexperts.com/article/sql/…

[Jess]

The file has to be ON THE SERVER. Not on your local machine.

[massaskillz]

Set ROWTERMINATOR = '0x0A' for .CSV files generated from UNIX-based OS (Mac, Linux, etc.)

[massaskillz]

@Jess the file specified can be a UNC path (e.g., \\machinename\public) as long as permissions are configured correctly: dba.stackexchange.com/questions/44524/…