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

Here’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.

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.

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.

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.

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