Solved

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

Posted on 2004-09-26
5
2,350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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