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 spli

Solution 1:

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 -T9.041SQLCHAR0100","1FNameSQL_Latin1_General_CP1_CI_AS2SQLCHAR0100","2LNameSQL_Latin1_General_CP1_CI_AS3SQLCHAR0100","3CountrySQL_Latin1_General_CP1_CI_AS4SQLCHAR0100"\r\n"0AgeSQL_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

Solution 2:

I'd create a temporary table, bulk insert the lot, select into the new table what you need and drop the temporary table.

Something like

CREATETABLE dbo.TempImport
(
    FirstName varchar(255),
    LastName varchar(255),
    Country varchar(255),
    Age varchar(255)
)
GO
BULK INSERT dbo.TempImport FROM'PathToMyTextFile'WITH (FIELDTERMINATOR =';', ROWTERMINATOR ='\n')
GO
INSERTINTO dbo.ExistingTable
(
    FName,
    LName,
    Country
)
SELECT  FirstName,
       LastName,
       Country
FROM       dbo.TempImport
GO
DROPTABLE dbo.TempImport
GO

Solution 3:

I now prefer to use XML format files like this with BULK INSERT or OPENROWSET:

<?xml version="1.0"?><BCPFORMATxmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RECORD><FIELDID="1"xsi:type="CharTerm"TERMINATOR="|"COLLATION="SQL_Latin1_General_CP1_CI_AS"/><FIELDID="2"xsi:type="CharTerm"TERMINATOR="|"MAX_LENGTH="37"/><FIELDID="3"xsi:type="CharTerm"TERMINATOR="|"MAX_LENGTH="41"/><FIELDID="4"xsi:type="CharTerm"TERMINATOR="|"MAX_LENGTH="17"/><FIELDID="5"xsi:type="CharTerm"TERMINATOR="\r\n"MAX_LENGTH="10"COLLATION="SQL_Latin1_General_CP1_CI_AS"/></RECORD><ROW><COLUMNSOURCE="1"NAME="i"xsi:type="SQLCHAR"/><COLUMNSOURCE="2"NAME="j"xsi:type="SQLUNIQUEID"/><COLUMNSOURCE="3"NAME="k"xsi:type="SQLNUMERIC"PRECISION="18"SCALE="0"/><COLUMNSOURCE="4"NAME="l"xsi:type="SQLBINARY"/><COLUMNSOURCE="5"NAME="m"xsi:type="SQLVARYCHAR"/></ROW></BCPFORMAT>

Then you can use the server-side BULK INSERT command as follows:

BULK INSERT foo FROM'\\mydomain.com\bar\bletch'WITH (FORMATFILE='foo.xml', ERRORFILE='foo.errors',  FIRSTROW =1, BATCHSIZE=10000)

alternatively, if you want to modify the data 'in-flight', you can use the

INSERT foo(i, j,k)
SELECT foo_delimited.i, foo_delimited.j, foo_delimited.k * 2OPENROWSET(BULK 'foo',
                   FORMATFILE= 'foo.xml')
        AS foo_delimited

Solution 4:

For info, with the same structure, you can use this kind of statement:

bcp schema.Table in"/Samples/AdventureWorksDW/DimCurrency.csv" \
    -S db.url \
    -d databaseName \ 
    -U userName \
    -P pwd  \
    -t ; `# The field separator ` \
    -c  `# Insert as character and doesn't ask the data type`  \ 
    -q `# With quote -- seems to be mandatory` 

See example