In this article, we’re going to look at 3 ways to convert a CSV file to SQL. This is a common use case, so hopefully, you’ll find the following information useful in your day-to-day work.
Introduction
Comma Separated Values (CSV) files are commonly used to exchange data from one system or application to another. The simple format of the csv file makes them ideal for exporting data from one place and importing it to another. A common use case, for example, is to export the data from an accounting or sales application as a csv file and open it in Excel. Once in Excel, users can view and manipulate the data, and prepare reports, often using pivot tables or charts. But what if you want to perform some more complex data manipulations than are possible in Excel? Or what if the data has to be stored and managed in a database for other reasons? In this post, we’ll look at some of the options available for importing csv to sql server.
The options we’ll look at are:
- a SQL script
- the SQL Server Management Studio (SSMS) ‘Import Flat File’ wizard
- an Excel add-in such as SQL Spreads
There are other options too. For example, you can create an SSIS package to do the import, or write a PowerShell script. However, the ones here are generally simpler to work through.
We’re going to be using a set of sample sales data that you can get here. The sample includes 10,000 records.
Option 1 : CSV to SQL using a SQL script
The SQL script to import CSV to SQL is quite straightforward. It simply involves creating a table and then performing an insert of the data. But, as you’ll soon see, whilst this is fine for a small dataset, if you need to import lots of data, then it will be slow and error-prone.
The first step is to create a dummy database. In SQL Server Management Studio (SSMS), execute the following script:
CREATE DATABASE csv_demoand then create the sales table in the dummy database by executing the following script:
CREATE TABLE sales1(
Order_ID INTEGER NOT NULL PRIMARY KEY
,Region VARCHAR(28) NOT NULL
,Country VARCHAR(32) NOT NULL
,Item_Type VARCHAR(15) NOT NULL
,Sales_Channel VARCHAR(7) NOT NULL
,Order_Priority VARCHAR(1) NOT NULL
,Order_Date DATE NOT NULL
,Ship_Date DATE NOT NULL
,Units_Sold INTEGER NOT NULL
,Unit_Price NUMERIC(6,2) NOT NULL
,Unit_Cost NUMERIC(6,2) NOT NULL
,Total_Revenue NUMERIC(10,2) NOT NULL
,Total_Cost NUMERIC(10,2) NOT NULL
,Total_Profit NUMERIC(10,2) NOT NULL
);The insert will look something like this, which shows the values for a single row:
INSERT INTO sales1(Order_ID,Region,Country,Item_Type,Sales_Channel,Order_Priority,Order_Date,Ship_Date,Units_Sold,Unit_Price,Unit_Cost,Total_Revenue,Total_Cost,Total_Profit)
VALUES (292494523,'Sub-Saharan Africa','Chad','Office Supplies','Online','L','1/27/2011','2/12/2011',4484,651.21,524.96,2920025.64,2353920.64,566105);We need to insert the values in exactly the correct order to match the order of the columns in the table and add quotes around the string values and date values. We obviously need to find a quick way to construct this part of the script. In Excel, we can do this using the CONCAT function to build up the necessary INSERT string for each row.
=CONCAT("INSERT INTO sales1(Order_ID,Region,Country,Item_Type,Sales_Channel,Order_Priority,Order_Date,Ship_Date,Units_Sold,Unit_Price,Unit_Cost,Total_Revenue,Total_Cost,Total_Profit) VALUES (",A2,",'",B2,"','",C2,"','",D2,"','",E2,"','",F2,"','",TEXT(G2,"mm/dd/yy"),"','",TEXT(H2,"mm/dd/yy"),"',",I2,",",J2,",",K2,",",L2,",",M2,",",N2,");")Once we have the INSERT statement for each row, we can then copy from Excel and paste into SSMS and execute the query. If you’ve been following along on your own using the sample data, you’ll no doubt have seen an error in SSMS when you ran the query:
Msg 102, Level 15, State 1, Line 1375
Incorrect syntax near 'Ivoire'.I’m sure you can guess the problem, and it highlights one of the main drawbacks of this approach. If there are any apostrophes in the values in any of the text columns, you’ll need to cater for them. In this case, we would need to change ‘Cote d’Ivoire’ to ‘Cote d’’Ivoire’ using a find and replace function.
Option 2 : CSV to SQL using the SSMS ‘Import Flat File’ wizard
Because csv files are so common, SQL Server Management Studio (SSMS) has a standard way of importing them to SQL Server. We’ll now look at this simple wizard-driven approach to importing csv to SQL Server. For more information, you can check out this Microsoft resource.
We’re going to use the same database that we created earlier (‘csv_demo’), and create a new table called ‘sales2’.
Now we just need to follow the steps in the wizard, which you can launch by selecting the csv_demo database that we created earlier and right-clicking and selecting Tasks > Import Flat File
The wizard dialog is now displayed as shown below.
Go ahead and click the ‘Next’ button.
Step 1: select the csv file
You can now select the csv file that you want to import. Note that the wizard will automatically populate the table name with the name of the file, but you can change it if you want to. I have changed it to ‘sales2’.
Click on the ‘Next’ button.
Step 2: preview the data
The wizard now displays a preview of the data. You’ll see that columns are re-named where necessary to remove spaces or invalid characters. You can modify them in the next step of the wizard if necessary.
Click ‘Next’.
Step 3: modify the columns as required
The Wizard now displays the schema that has been generated based on its inspection of the data in the csv file. It’s important here for you to take note of the data types that have been detected by the wizard and make any changes as necessary. You can also set a Primary Key for the dataset and specify whether Nulls are allowed. In the screenshot below, I’ve chosen the ‘Order_ID’ column as the PK and left the ‘Allow Nulls’ unchecked, which is the default.
Click ‘Next’ to see a summary of the operation including the database name, table name, and csv file to be imported. We can now click on ‘Finish’.
If everything went well with the import, then the screen below is displayed. This shows the operations that have been completed by the wizard, which in our case is an ‘Insert Data’ operation.
Step 5: check the data
You can check that the data has been imported correctly by executing a couple of SQL statements:
Option 3: CSV to SQL using the SQL Spreads Create Table feature
For this option, you’ll need to download and install SQL Spreads. The download is here, and the installation process is described here.
Once SQL Spreads is installed, open the sales.csv file in Excel. The SQL Spreads ribbon tab is now visible.
Click on the SQL Spreads ribbon tab and then click on the ‘Create new SQL Table’ button:
When you click the ‘Create new SQL Table’ button, you’ll see the following message. This simply means that to do the import correctly, SQL Spreads needs to know the extent of the dataset to import.
Click ‘OK’ and then convert the data range into a table by pressing CTRL + T. You can now go back and click the ‘Create new SQL Table’ button. You’ll be presented with the following dialog where you can specify the table name – in this case, I have entered ‘sales3’ as the table name:
We could now click the ‘OK’ button to create the table in SQL Server and import that data. This would use some default settings which we’ll look at now by clicking the ‘More Settings…’ button. The additional settings allow us to change any of the data types, set a Primary Key, and also specify whether an auto-incrementing ID column should be created. In this example, the sales.csv file already has a unique identifier column, ‘Order ID’, so I have set that as the PK and un-checked the auto-increment option.
Click ‘OK’. SQL Spreads will now create the table in SQL Server and import the data.
We can now go into SSMS to see the new table and the data:
Note that to see the new table in the SQL Spreads Designer, click the Design mode button twice to close and reopen the Designer.
This will refresh the list of databases and tables, and you will see your new table in the list.
Now that the data is in a table in SQL Server and also Excel, you can manage the data in either place and the data will always be synchronized.
Summary
We’ve looked at 3 options to import a csv data file into SQL Server:
- SQL Script: this uses a couple of simple SQL scripts in SQL Server Management Studio (SSMS) to convert from csv to sql.
- SSMS Import Flat File Wizard: a simple and robust import wizard in SSMS.
- SQL Spreads Create Table feature: a simple and robust wizard in Excel using the SQL Spreads Excel Add-in.
Option (1) is simple but would be cumbersome to use for anything other than a simple CSV file with a few columns.
Options (2) and (3) are both user-friendly and reliable. The key difference is that Option (3) can be used by anybody that can use Excel, and doesn’t require any SQL DBA skills at all. This makes it a great choice for people and organizations that like the ease and flexibility of Excel in terms of data management, but also need the advantages of databases like SQL Server.
Download a trial version of SQL Spreads today to import your data from csv to SQL Server.
*This article was originally published on May 12, 2021 and was updated on January 31 2022 to refresh some screenshots.
