The easiest way to import CSV into SQl Server 2005

Question

I have some 5,000 files from each CSV data that I need to import into SQL Server 2005.

It was just with DTS. I used to try to use SSIS, and it seemed about 10 times bigger, and I eventually gave up.

What would be the easiest way to import csv data into sql server? Ideally, a tool or method would also create a table, since there were about 150 fields in it, this would simplify things.

Sometimes there should be 1 or 2 rows with this data, which may need to be changed manually, since they are not imported correctly.

Answer 1

try the following:

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 contents of the table:

SELECT *
FROM CSVTest
GO