Import data from CSV file into a table

Question

Post reply

Import data from CSV file into a table

thegreatsamarth

SSChasing Mays

Points: 608
More actions
July 31, 2008 at 3:15 am

#194987

Hi..
I'm trying to import data from a CSV file into a table.
I've a CSV file (same as AdventureworksDW.dbo.DimEmployee) with a Field Delimiter "tab".
Also, the table has an IDENTITY column (column 1, Employee Key).
Here is a line from my CSVFile:
1181441780744646610511GuyGilbertR0Production Technician - WC601996-07-31 00:00:00.0001972-05-15 00:00:00.000adventure-works\guy1guy1@adventure-works.com320-555-0195MGuy Gilbert320-555-01950M112.4500213010Production1996-07-31 00:00:00.000Current
I am using a Format File which is created by using BCP command:
bcp AdventureworksDW.dbo.DimEmployee format nul -c -x -f DimEmployee.xml -T
The SQL Statement, i m using is:
SELECT * INTO TestDB.dbo.DimEmployee
FROM
OPENROWSET
(
BULK 'C:\CSVFiles\DimEmployee.csv',
FORMATFILE = 'C:\FormatFiles\DimEmployee.xml'
)
AS DimEmployee
But i get these error:
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (EmployeeKey).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (EmployeeKey).
.
.
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I've tried playing around with the format file and the CODEPAGE option but I keep getting errors.
Yaa... but when i import the data through "BULK INSERT" command with FIELDTERMINATOR and ROWTERMINATOR option (but not the Format File), it is easily importing the whole data.
Can anyone help me with this problem so I can import this data using Format File?
File Attached: DimEmployee.txt (XML Format File)
--Samarth

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

How about using the BULK INSERT command (can be viewed in BOL)?

"-=Still Learning=-"

Lester Policarpio

thegreatsamarth SSChasing Mays Points: 608 More actions · Answer 2

Lester Policarpio (7/31/2008)
How about using the BULK INSERT command (can be viewed in BOL)?

You can refer BULK INSERT Statement in BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

I've used the below code to import data thru BULK INSERT:

BULK INSERT Test.dbo.DimEmployee

FROM 'C:\CSVFiles\DimEmployee.csv'

WITH

(

/*Not working with FORMAT FILE*/

--FORMATFILE = 'C:\FormatFiles\DimEmployee.xml'

FIELDTERMINATOR = '\t'

,ROWTERMINATOR = 'slash n'

)

So, anyone can help me out of my problem (mentioned on top)???

--Samarth