How to import csv files

Question

How can I import CSV file data into SQL Server 2000 table? I need to insert data from CSV file to table twice a day. Table has more then 20 fields but I only need to insert value into 6 fields.

Answer 1

i face same problem before i can suggest start reading here The author covers:"This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps."

Answer 2

You describe two distinct problems:

1. the CSV import, and
2. the extraction of data into only those 6 fields.

So break your solution down into two steps:

1. import the CSV into a raw staging table, and
2. then insert into your six 'live' fields from that staging table.

There is a function for the first part, called BULK INSERT, the syntax looks like this:

BULK INSERT target_staging_table_in_database
FROM 'C:\Path_to\CSV_file.csv'
WITH
(
    DATAFILETYPE = 'CHAR'
    ,FIRSTROW = 2
    ,FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '\n'
);

Adjust to taste, and consult the docs for more options. You might also want to TRUNCATE or DELETE FROM your staging table before doing the bulk insert so you don't have any old data in there.

Once you get the information into the database, doing an UPDATE or INSERT into those six fields should be straightforward.

Answer 3

You can make of use SQL Server Integration services(SSIS). It's jusy one time task to create the Package. Next time onwards just run that package.

You can also try Bulk Insert as daniel explained.

You can also try Import export wizard in SQL Server 2000.

Answer 4

I need to insert data from CSV file to table twice a day.

Use DTS to perform the import, then schedule it.

Answer 5

For SQL 2000, I would use DTS. You can then shedule this as a job when your happy with it. Below is a good Microsoft link explaining how to use it.

Data Transformation Services (DTS)