Read a CSV file with SQL
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: Names.zip.
Let's first create a table to store the data:
begin
create table Names
(
FirstName varchar(50),
LastName varchar(50),
Gender varchar(10)
)
end
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.
with (fieldterminator = ';', rowterminator = '\n')
To check if the data was inserted correctly, let's do a check:
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:
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
And with this feature enabled, you can now move the file to the designated folder. Be sure that folder exists.
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.
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!