Read a CSV file with SQL

Read a CSV file with SQL

This BlogPost was written by Andre Speek. Click here for other BlogPosts.

Last week a customer asked if it was possible to integrate numbers from a CSV file with a report. Now the Reporting Tool they used could only read from an SQL Database. So to do it directly was not possible.

But by importing the CSV file into the datbase, it was definitely possible. Great! But how can you automate this process so a new file can be imported daily?

First collect the data into a table

Before processing data from an external source, wheter it is from a file or from an API, I always import the raw data. Just to be sure I can always track back to the original data.

So for this example we are going to import data from this CSV file:

Let's first create a table to store the data:

if not exists (select name from sysobjects where name = 'Names')
  create table Names
   FirstName varchar(50),
   LastName varchar(50),
   Gender varchar(10)

Now let's import the data

With the table ready to store the data, now let's import the data from the file. Be sure to change the path to the file to the location where you downloaded the file.

bulk insert Names from 'C:\Names.csv'
 with (fieldterminator = ';', rowterminator = '\n')

To check if the data was inserted correctly, let's do a check:

select *
from Names

Now the data is stored in the database, you can perform any operation you like.

If the data needs to be imported on a regular basis, you can create a stored procedure to import the data and then transform the data any way you want.

Preventing reading the file again

When you are done importing the file, you might want to move the file to a different folder to prevent it from being read twice. To do that, SQL Server must be enabled to run Shell Commands.

By default SQL Server is not allowed to do that. But you can enable this feature with the following commands:

exec sp_configure 'show advanced option', '1'

exec sp_configure 'xp_cmdshell', 1

And with this feature enabled, you can now move the file to the designated folder. Be sure that folder exists.

EXEC xp_cmdshell 'move C:\Names.csv C:\Processed\Names.csv'

That's it for today

With this simple trick you can read data from CSV and move it into the SQL database. From there you can do with it whatever you want. Like combining the data with other data from the database in your reporting.

So, is this something you have used before? Or do you plan using it? Let me know how this one helped you out by leaving a comment.

Do you want to comment on this post? Leave a response here and I will get in touch with you!

You can leave a comment or send a question to Let me know if you got inspired and May the Source be with you!

An error has occurred. This application may no longer respond until reloaded. Reload 🗙