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

bcpsqlsql-server-2005sql-server-2008tsql

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 ';' .

Example

CSV FILEcontents:

FirstName;LastName;Country;Age
Roger;Mouthout;Belgium;55

SQL Person Table

Columns: FName,LName,Country

Best Answer

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 



 9.0
4
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
Related Topic