How to Import a CSV file into a Sql Server table.
There are several ways to import a csv file into Sql Server. For this article we are going to explore different methods using Sql Server Management Studio (SSMS).
We are going to use the following CSV file and database table for demo purposes.
-- Create a new table called '[Countries]' in schema '[dbo]' -- Drop the table if it already exists IF OBJECT_ID('[dbo].[Countries]', 'U') IS NOT NULL DROP TABLE [dbo].[Countries] GO -- Create the table in the specified schema CREATE TABLE [dbo].[Countries] ( [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column [Country_Desc] NVARCHAR(50) NOT NULL, [Continent_Dec] NVARCHAR(50) NOT NULL -- Specify more columns here ); GO
Ways to Import a csv file into a Sql Server Table
1. Import CSV file using Sql Server Management Studio.
Step 1: Select Database
Go To SSMS “Object Explorer” then Select the Database where you want to import the CSV, right click on it go to Task> Import Data.
Step 2: Defining Data source for Importing CSV to Sql Server Table
Sql Server Import/Export Wizard window will open, go to “Choose a Data Source” and proceed to select “Flat File Source” as “Data source” then click on Browse and select the CSV file that you want to import to Sql Server table.
Leave Format part options as default (In CSV file delimiter is comma).
In case your CSV file has a header (Column names), proceed to check “Column names in the first data row” option, if not proceed to leave it unchecked.
Then proceed to go to “Columns” on the left menu, there you can define the row and column delimiters for the file (Comma as we are talking about a CSV file), there you will see a preview Column information, if the preview does not match what you are expecting you have to pick a different “Column delimiter”.
After that go to “Advanced” on the left menu, there you can pick the data type and size of each column.
Do not forget that if rows size does not match the sizes of your CSV rows importation process can get an error or return incorrect results.
Then you can go Preview option and check if displayed information does match your expectations for importing it into a Sql Server Table.
Step 3: Defining Data Destination for Importing CSV to Sql Server Table
Proceed to click on next button, Destination window is going to open, there we have to pick the database where we are going to load our CSV file.
On the destination section proceed to select “SQL Server Native Client 11.0” and fill the requested data for connecting into your database, after this is done proceed to click on “Next” button.
Step 4: Mapping table and fields
In “Source table” window on destination section we have two options, if we want to create a new table to fill it with our CSV information, we have to pick a name of a table that does not currently exist in our database. for example if I type [New_Countries] instead of [Countries], [New_Countries] table is going to be created (As I don’t have that table in my DB) with the structure that we defined in “Data Source” > “Columns” part of the importation process.
If we want to import a CSV file to an existing Sql table, we have to pick a name of a table that already exist then proceed to click on “Edit Mappings”, for this we are going to create a table that matches our CSV file structure.
After that we are going to click on “Edit Mappings”.
On the mapping section make sure that “Source” and “Destination” columns match, click on “OK” then “Review Data Type Mapping” is going to be open and check if there are no issues on mapping.
Step 5: Checking Import results
After that click on “Next” then “Finish”, Csv data loading is going to start.
If everything goes well you will receive a Success, if not it is going to fail and you will need to go back and review the step where process is failing.
2. Import CSV using Bulk Insert
Bulk insert Imports a data file into a database table in a specified format, Something that I don’t like about this option is the fact that you need to have access to database server for uploading the file, something that only DBAs typically have.
For this example we are going to use Countries table and Countries CSV file.
Step 1: Move data to destination Sql Server Folder.
Proceed to copy CSV file to Server path.
Step 2: Execute the following Query on SSMS
Proceed to Execute the following query
BULK INSERT Countries FROM 'C:\CSVDATA\Countries.csv' -- Sql Server Folder WITH ( FIRSTROW = 2, --Taking in count we have a header FIELDTERMINATOR = ',', --CSV field delimiter ROWTERMINATOR = '\n', --next row ERRORFILE = 'C:\CSVDATA\ErrorCountries.log', TABLOCK )
After Executing the query you will receive a message telling the number of rows imported to the target table
If you query the target table you can check that rows were imported as expected.
Before using this method take into account this:
- File needs to be on path ‘C:\CSVDATA\Countries.csv’ in Database server as Bulk Insert is going to look into that folder for uploading data.
- In case there is an error in loading file rows, error file generates into path ‘C:\CSVDATA\ErrorCountries.log’ in Database Server.
- Your SQL Server Service needs to be log on with an account that have permission to read/write the target folder.
- CSV and table row order must be the same in order to match and load data into the table.
If data is not too complex or large, consider using SSMS or Bulk Insert (only if you have access to database server) for importing Csv files into a Sql Server table, else try to use an ETL software for that, Sql Server integration services is a good option.