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 ‘;’ .

Example

CSV FILEcontents:

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

SQL Person Table

Columns: FName,LName,Country

Answer

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

CREATE TABLE 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
INSERT INTO dbo.ExistingTable
(
    FName,
    LName,
    Country
)
SELECT  FirstName,
       LastName,
       Country
FROM       dbo.TempImport
GO
DROP TABLE dbo.TempImport
GO