Import Data from a Text or CSV file into SQL Server

I was recently in need of importing data from a CSV (Comma Separated File) to a table in SQL Server 2008.
After searching in Google for a little while, found this blog entry from Pinal Dave (SQL Authority) which always provides good content.

Besides passing along the technique described in Pinal's blog post, I would like to expand by explaining a few a snags that I ran into.

My source data in the CSV file, looks something like this:

HOU009,Windows Phone 7,Will Martinez,11/10/2011,Houston; TX,999,2
HOU010,WPF for Business Applications,Will Martinez,11/15/2011,Houston; TX,695,1

More an less I have all the data needed to fill the columns of my target table. My assumption is that my column id of type uniqueidentifier is going to be auto-generated. This is my table:

CREATE TABLE [dbo].[Courses](
   [id] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
   [code] [varchar](50) NOT NULL,
   [description] [varchar](200) NULL,
   [instructor] [varchar](50) NULL,
   [date] [date] NULL,
       [venue] [varchar](50) NULL,
   [price] [money] NULL,
       [duration] [int] NULL,
 CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([id] ASC,[code] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  ON [PRIMARY]) ON [PRIMARY]

As you may notice, I have two datatypes that may be challenging, first my primary key is  of type uniqueidentifier and also, I'm using a date datatype.

Initially I tried to import my data from the CSV file using the below statement:

bulk insert [dbo].[Courses]
from 'C:\Courses.csv'
with (fieldterminator = ',', rowterminator = '\n')
go

On my first try I did not have any luck. I ran into the below issue:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Courses.csv" could not be opened. Operating system error code 5(Access is denied.).

Moved my file to the Public directory and then make sure permissions were set to 'Everyone'. No luck, although I did not get the "access denied" error. My insert returns 0 row(s) affected.

At this point my guess was that I had a couple of issues:
- First I did not have a value for the Id column which is our primary key
- My column of type date could be a problem if there is an implicit conversion of string data

To work around the problem, I decided to create a new temporary table, without a primary key and with a datetime datafield.  Here is the table:

CREATE TABLE [dbo].[CoursesTemp](
   [code] [varchar](50) NOT NULL,
   [description] [varchar](200) NULL,
   [instructor] [varchar](50) NULL,
   [date] [datetime] NULL,
   [venue] [varchar](50) NULL,
   [price] [money] NULL,
   [duration] [int] NULL)

Ran my insert statement again:
bulk insert [dbo].[CoursesTemp]
from 'C:\Users\Public\Downloads\Courses.csv'
with (fieldterminator = ',', rowterminator = '\n')
go

Success! Finally got to see what I was waiting for...(20 row(s) affected)

At this point, life is easy. I can use the data I just inserted in the temporary table and use it to insert into the "live" table.

insert [dbo].[Courses]
  (code, description, instructor, date, venue, duration)
select
   code, description, instructor, cast(date as date), venue,
   duration
from [dbo].[CoursesTemp]

Notice that, my Id column is not listed, since it has a uniqueidentifier with a default of  NEWSEQUENTIALID , it automatically generates a GUID for each record.
To deal with the issue of the date field, noticed that there is a CAST statement that will convert the data to the appropriate datatype.

Below is how the data looks in my table.



Now I have the data I needed and my id column has nice GUIDs generated for every record.
I'm ready to get some work done.

Hope this helps somebody out there,
Will



CodeProject