Solved

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

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not showing page correctly 3 40
Host asp.net pages 5 33
Connection String 16 54
Pagebreak issue while printing the aspx page 3 24
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
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 …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

11 Experts available now in Live!

Get 1:1 Help Now