Easiest way to import CSV into SQl Server 2005

Question

I have several files about 5k each of CSV data I need to import into SQL Server 2005.

This used to be simple with DTS. I tried to use SSIS previously and it seemed to be about 10x as much effort and I eventually gave up.

What would be the simplest way to import the csv data into sql server? Ideally, the tool or method would create the table as well, since there are about 150 fields in it, this would simplify things.

Sometimes with this data, there will be 1 or 2 rows that may need to be manually modified because they are not importing correctly.

Solution

try this:

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

here is a summary of the code from the link:

Create Table:

CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

import data:

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
    FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '\n'
    --,FIRSTROW = 2
    --,MAXERRORS = 0
)
GO

use the content of the table:

SELECT *
FROM CSVTest
GO