Solved

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

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
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 …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 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

19 Experts available now in Live!

Get 1:1 Help Now