≡ Menu

Importing CSV files to an SQL Server Database

SQL Server

I regularly need to set up new tables in my SQL Server Database from data held externally as either text or csv. It sounds like an incredibly straightforward task but always sends me scurrying off to google for advice on How to Import CSV to SQL Server. It doesn’t help that I don’t have access to the SQL Server Import wizard.

Here’s my summary of the method for importing a csv file into a newly created SQL Server database table. I’m currently setting up a database to display all the organisational level reference cost data from 12/13 and I will use that as an example.

The 12/13 NHS organisational source data can be accessed here.

The method used follows the approach of CREATE Table, Import csv file using BULK INSERT and then the use of UPDATE Table to clean up the resultant table if required.

CREATE Table

Create TableHere’s the SQL CREATE TABLE syntax from w3schools

It’s a bit of pain to have to define all the field headings and data types.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE Ref_Cost_1213 --This defines the database I want to work with. In my case it already exists.
GO
CREATE TABLE Data_1 (
Org_code VARCHAR(10),
Department_code VARCHAR(100),
Service_code VARCHAR(100),
Currency VARCHAR(100),
unit_cost FLOAT,
activity DECIMAL,

bed_day DECIMAL,
mean FLOAT,
actual_cost FLOAT,
expected_cost FLOAT,
mapping_pot VARCHAR(10)
)
GO

BULK INSERT

The syntax for bulk import is quite extensive – see Microsoft developer network for details http://msdn.microsoft.com/en-gb/library/ms188365.aspx, the extract below shows a stripped down version that is sufficient for my needs.

1
2
3
4
5
6
7
8
9
10
11
BULK INSERT Data_1
FROM '\\Dw-syn\Data.csv'-path TO csv file
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter, in this case a comma but if you have control of the csv format you might want to use a pipe |
ROWTERMINATOR = '\n', --Use to shift the control to next row
ERRORFILE = '\\Dw-syn\ErrorRows.csv', --This is a useful addition to store all the data rows that could not be imported
TABLOCK
)
GO

UPDATE TABLE

If you look at the source csv data in notepad you will see that it is littered with double quotation or speech marks and unfortunately these are also imported into the SQL table using the BULK INSERT method.

You could revise the csv file but in this instance it is easier to REPLACE the speech marks in my newly created table.

Update table

The syntax for SQL UPDATE again from w3schools

This script works its way through all the affected columns (the varchars), removing the double quotation marks.

1
2
3
4
5
6
7
UPDATE Data_1
SET
Org_code = REPLACE(Org_code,'"',''),
Department_code = REPLACE(Department_code,'"',''),
Service_code = REPLACE(Service_code,'"',''),
Currency = REPLACE(Currency,'"',''),
mapping_pot = REPLACE(mapping_pot,'"','')

Reference Sources

Stack Overflow – useful answer to the question, How to Import CSV file into SQL Server, and provides tips on dealing with quotation marks and commas within text fields.
Gov.uk – source data for the 12/13 reference costs
W3schools – no nonsense syntax help for SQL

Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.