Solved

csv file import:  Bulk insert data conversion error (type mismatch)

Posted on 2004-09-26
5
2,339 Views
Last Modified: 2008-01-09
Hi,

Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.

column             datatype
--------             -----------
program           nvarchar(20)
description       nvarchar(50)
pId                  int  

pId has been set to primary key with auto_increment.

My csv file has 2 columns of data and it looks like follows:

program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"


Now i use BULK INSERT like this

"BULK INSERT ord_programs FROM 'C:\datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)"

to import  data into my table in SQL server and it gives me this error

"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"

I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...  

Please help me out guys and please post a snippet of code if you have.

Thank You.

0
Comment
Question by:dn_learner
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:kiranghag
ID: 12157584
the problem could be that the file has two columns and your table has three.
try specifying the columns which are present in the csv file only.
0
 
LVL 5

Accepted Solution

by:
ajitanand earned 500 total points
ID: 12157661
First thing this is an SQL Question. Right?

The problem lies in the difference in the column count in table and the BULK INSERT statement/csv file.

You have 2 options
1. Have a format file - It is possible to skip importing a table column if the field does not exist in the data file by specifying 0 prefix length, 0 length, 0 server column order, and no terminator. This effectively states that the data field does not exist in the data file, and that the server column should not have data loaded into it.

BULK INSERT vtemp_table
FROM 'C:\test.blk' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2, FORMATFILE='c:\test.fmt')


or

2. Create a view that only exposes the 2 columns of the table as per the csv file, and BULK INSERT into the view instead of the table:

CREATE VIEW vtemp_table as SELECT program, description FROM temp_table
-- --Assuming you have the table as CREATE TABLE temp_table (program nvarchar(20), description nvarchar(50), pId int)

BULK INSERT vtemp_table
FROM 'C:\test.blk' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)




rgds,
Ajit Anand
0
 

Author Comment

by:dn_learner
ID: 12160933
Hey Kiran,

Could you please be more specific as to how i can specify the columns in the csv file to be imported?   I have a filed pId, which is a primary key with auto_increment ...

please let me know ASAP ....
0
 

Author Comment

by:dn_learner
ID: 12160982
Hey ajitanand,

Thanks for the prompt resonse.

Yes, this is an SQL question as well as ASP.Net/VB.Net question.

Could you please guide me on how i can format the file test.fmt where i do not want to import a third column into the table.

Iam new to the world of .Net and i haven't worked with file imports such as this and also the format .fmt files.

Thanks.
0
 
LVL 5

Expert Comment

by:ajitanand
ID: 12166801
Hello,

The bcp utility can be used to create the format files easily.
sample command (to be given on windows command shell)

C:\>bcp theDB..temp_table out c:\test.txt -Smyserver -Usa -Pmypassword


Below is a format file (between the 2 dashed lines) that you can use in your case.
[save it to a file, say temp_table.fmt on the sql server machine]
---------------------------------
8.0
3
1       SQLCHAR       0       20      ","            1      program         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      "\r\n"            2      description     SQL_Latin1_General_CP1_CI_AS
3       SQLINT        0       0       ""                0      pId             ""
---------------------------------


Then the bulk copy command in T-SQL appears as:

BULK INSERT temp_table
FROM 'C:\tmp.txt' WITH (FORMATFILE='c:\temp_table.fmt', FIRSTROW=2)




rgds,
Ajit Anand
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question