October 28, 2021
Today, there are easier ways to import CSV to SQL Server. You can code less or use graphical tools. You can even get the CSV from cloud storage to your on-premise SQL Server. This post will focus on how you can do it in 3 easy ways.
I read about comma-separated-values, or CSV, in Wikipedia. Can you believe that CSV was supported back in 1972? For what reason?
To exchange data between 2 or more systems of different platforms and architectures. Today, the reason is still the same. Imagine you have 2 proprietary systems. One is using a proprietary NoSQL database like PayPal’s. And the other is using SQL Server. It’s like a Japanese talking to an Estonian. Understanding is almost impossible. What is the solution you will ask?
Export a CSV file from the NoSQL database. Then, import the CSV file to SQL Server. Rather than invent new software, exchange a mature and common file format. That will be easier and less time-consuming.
Here are common scenarios when CSV can be useful:
Though this can be done using JSON or XML, CSV is simpler to generate from the source. If the data requirement is non-hierarchical, CSV can be a good fit. What about the target system using SQL Server? It is rated 4.5 out of 5 in Gartner Peer Insights. SQL Server in the cloud or Azure SQL is also one of the top 3 DBMS of 2020. So, learning to import CSV to SQL Server is worth the effort. You will ask how to do it. The answer is described in detail below.
Before we start, we need a sample CSV and a target table in SQL Server. Check it out in the screenshot below.
This will be used to upload to SQL Server using 3 of the different ways to import CSV. So, download a copy of the actor.csv file from here. Remember where you saved it. You’ll need it later in this tutorial.
Meanwhile, the target table is structured the same in SQL Server.
In this tutorial, I’m using the server name MS-SQLSERVER. And the database name is CSV-MSSQL-TEST.
BULK INSERT is a command in SQL Server to import data files into a database table. It can be used to upload various file formats, including CSV. If you love a little coding, this is the way to go. Here’s the code on how to bulk insert CSV into SQL Server:
-- truncate the table first TRUNCATE TABLE dbo.Actors; GO -- import the file BULK INSERT dbo.Actors FROM 'C:\Documents\Skyvia\csv-to-mssql\actor.csv' WITH ( FORMAT='CSV', FIRSTROW=2 ) GO
The first command simply deletes all the records in the target table. Then, the BULK INSERT command includes the target table and the CSV file. The location of the CSV should follow the rules of the Universal Naming Convention (UNC). You also must tell SQL Server what file it’s dealing with. In this case, FORMAT=CSV because the file is in CSV format. Finally, specify what row the data starts. FIRSTROW = 2 because the first row contains the column names.
You can issue a BULK INSERT command from SQL Server Management Studio or any other SQL Server tool. Another way to do it is in PowerShell.
Pros:
Cons:
If you don’t prefer coding, another useful tool is the Import Data from SQL Server Management Studio (SSMS). Here’s how to import CSV to MSSQL table using SSMS.
First, you need to go to Object Explorer and select the target database. Expand the Databases folder. Check it out below.
In this example, the target database is CSV-MSSQL-TEST. Right-click that database and then select Tasks. And then, click Import Data.
The SQL Server Import and Export Wizard window will appear with a welcome screen. From here, click Next.
This part will let you pick the data source. For this, click the drop-down list and select Flat File Source. Afterwards, click Next.
Now, you need to specify the file. Click Browse and specify the path of the CSV file as shown in the screenshot. If you have downloaded it, specify the download location. Then, in the file type, select CSV files (*.csv). And afterwards – actor.csv. Proceed further by clicking Open.
Can’t SSMS detect the correct data types for each column? In our sample data, detection of the correct types and sizes is off. So, we need to set it up ourselves.
Pro tip: Whenever you import data from one platform to another, it’s best to match the correct types and sizes. Why? To avoid errors and headaches.
Let’s start by clicking Advanced. Then, you will see a list of columns. Click each and set the type and size.
Based on the data types and sizes, refer to table below on what to set for each column.
Column Name | DataType | OutputColumnWidth |
---|---|---|
id | four-byte signed integer [DT_I4] | N/A |
lastname | string [DT_STR] | 20 |
firstname | string [DT_STR] | 20 |
middlename | string [DT_STR] | 20 |
suffix | string [DT_STR] | 3 |
Now, you need to specify the SQL Server as the target of the import process. To do that, select Microsoft OLE DB Provider for SQL Server. Then, select the SQL Server name and enter the necessary credentials. Select the Database name and click Next.
After selecting the database, you also need to specify the table. Choose the Actors table.
Then select Edit Mappings to see if the columns from the source match the target.
Once the Column Mappings window appears, check if the columns from the source and target match. You can either pick delete rows in the destination table or append rows. In our case, we want to start cleaning. If you attempted to import using BULK INSERT earlier, there is data present in the target table. And so, an error will occur. To avoid that, choose Delete rows in the destination table. And then, click OK.
Finally, to end our setup for the target table, click Next.
You can save the entire import configuration to an SSIS package. If you choose this, you can schedule the package to run at regular intervals. In our case, we will just run it immediately. For better understanding, check out the next screenshot.
Then, choose Next to see a summary of your chosen settings. Or click Finish to run the import process. You will see the progress in the next window if you click Finish.
That’s it for importing CSV to SQL Server using Import Data in SSMS. You can also choose Import Flat File, but it will always dump to a new table. And you cannot save it to an SSIS package.
Pros:
Cons:
Finally, we will use a cloud ETL tool to import the CSV file to SQL Server. One of the possible ways of importing CSV to SQL Server is from cloud storage like Google Drive. In this section, you will see how to use Skyvia to import the CSV file from Google Drive to SQL Server on a local PC. Before you can use it, you need an account on Skyvia and Google. Both are free to register.
The elements of a successful import using Skyvia are the following:
To start, you need to log in to Skyvia. You will be redirected to your default workspace. Then, from here, you can create all the 3 things above. Here’s how.
To create an Agent or a tunnel to a remote SQL Server, click NEW. Then, select Agent.
Once you’re on the next page, name your agent Skyvia-MyPC. Then, download and install the Skyvia agent. Please pay attention to where you install the agent. You will need this when you download the Agent Key. Finally, download the agent key file and store it where you install the Skyvia Agent.
From here, your agent configuration has been completed. But you need to test it. So, run the installed Skyvia agent application. Here’s a screenshot of the installed Skyvia Agent and Key files.
Here’s what it looks like after running the agent.
Once done, you can check the status on Skyvia. Here’s a screenshot of a good connection. This means that now it is possible to connect to your SQL Server from Skyvia.
First, let us create a connection to Google Drive where the CSV is located. For this, click NEW and click Connection.
On the next page, you need to select a connector. Click Google Drive.
Then name the connection MyGDrive, and sign in to your Google account. Click Create Connection to create a connection. Here’s a completed Google Drive connection screenshot.
Second, let’s create a SQL Server connection.
Click NEW again and select Connection. Refer to the screenshot above. Then click SQL Server. Name your connection CSV-MSSQL-TEST. After that click Agent and select the Skyvia-MyPC agent created earlier. Then enter the server name, credentials, and database name. Here’s a screenshot of a completed SQL Server connection.
We’re almost done. The final part is to create the Skyvia package. So, start by clicking NEW and then Import. This will create an import package.
In the opened package editor, name your package CSV-SQL-Test and indicate the source and target. Our source is MyGDrive Google Drive connection. And the target is CSV-MSSQL-TEST, the SQL Server connection.
Some other things to pay attention to:
It’s time to create the tasks. First, whatever existing record there is in the Actors table, it should be deleted. Under Tasks, click Add new. A new window will appear. Then, select the actor.csv file in Google Drive. Then, make the Text Qualifier blank and the Code Page Western European Windows (1252). Finally, set the id column to DT_I4 (Integer).
Click Next step to proceed further. On the second page, under Operation, click Delete. This will delete the records in the target. Click Next step again to proceed to mapping settings. You will see a mapping between the id column of the source and target. Click Save.
Finally, the first task is completed.
The second and last task is to insert the rows in the CSV file to SQL Server. The steps are almost the same, except you need to define all column types and sizes based on Table 1 earlier. Then, click Next step and select the Insert operation instead of Delete. Finally, Save the task.
You can see below the screenshot of the completed package. Click Save to save the package.
To run the package, click the Run button in the upper right corner of the page. Then, click Monitor to see the progress. See a sample screenshot below.
Pros:
Cons:
You can use these 3 ways to import CSV to SQL Server: all are viable tools depending on your needs.
Was this post useful? If yes, please share it on your favorite social media platforms.
Comments