A little problem I had recently was to import a CSV file with about 16mb of data, into a Microsoft SQL 2005 database, using Microsoft SQL Studio Express. A csv file is a file with data that has "comma seperated values" (CSV) and by using SQL bulk load we can read this file from disk and insert the data into our table. Here is a quick example of how to do this:
First we create a test table:
USE dbtest
GO
CREATE TABLE tblCSVTest
(ID INT,
Name VARCHAR(150)
)
GO
Then make sure your CSV is created and available in an accessible path. E.g. “C:\mytest.csv”. Your CSV file must be present on the actual same server as your SQL server instance.
BULK
INSERT tblCSVTest
FROM 'c:\csvtest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check data in the table.
SELECT *
FROM tblCSVTest
Depending on your CSV file, you should now have the data loaded into tblCSVTest. On another note I ran into a permission's error when trying this the first time. “you do not have permission to use the bulk load statement”. Take a look at my post for the solution to this permission problem.
I also have to point out that as a good resource to these type of issues and problems, Pinal Dave of SQL Authority seems to have most things covered and answered.