Sql – Use bcp to import csv file to sql 2005 or 2008


I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ';' .


CSV FILEcontents:


SQL Person Table

Columns: FName,LName,Country

Best Solution

You can use a format file when importing with bcp:

Create a format file for your table:

 bcp [table_name] format nul -f [format_file_name.fmt] -c -T 

1       SQLCHAR       0       100     ","      1     FName             SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","      2     LName             SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Country           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   0     Age               SQL_Latin1_General_CP1_CI_AS

Edit the import file. The trick is to add a dummy row for the field you want to skip, and add a '0' as server column order.

Then import the data using this format file, specifying your inputfile, this format file and the seperator:

bcp [table_name] in [data_file_name] -t , -f [format_file_name.fmt] -T