Solved

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

Posted on 2004-09-26
5
2,334 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please explain: Aspect Oriented Programming 2 84
JavaScript error 1 52
How do see the validation errors on ASP.NET MVC 4 37
Vb.net dynamic formulas in runtime 11 62
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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now