Link to home
Start Free TrialLog in
Avatar of bpfsr
bpfsrFlag for United States of America

asked on

Getting error message - Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (price).

I have created a table as follows:
CREATE TABLE DBase_Editor
(
      sku nvarchar (max) not null,
      title nvarchar (max) null,
      price money not null,
      condition nvarchar (max) not null,
      author nvarchar (max) null,
      sellers int null,
      usedsellers int null,
      isbn varchar (13) not null,
      availability nvarchar (max) null,
      comments money null
)

But when I try to load the attached file using

BULK INSERT DBase_Editor
    FROM 'G:\DBase Editor.csv'
    WITH
    (
        Firstrow = 2,
            FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    )


I get the above message. (Please note I saved it as a .txt file for purpose of posting it here only as EE doesn't accept CSV files. I do have it saved as CSV when I try to load it). I am getting A LOT of them, in fact I get so many it says "exceeded maximum number of errors" See below. Any help appreciated.

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (price).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 15, column 3 (price).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 16, column 10 (comments).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17, column 10 (comments).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 22, column 10 (comments).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 23, column 10 (comments).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 32, column 3 (price).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 33, column 3 (price).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 46, column 10 (comments).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 49, column 6 (sellers).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 70, column 3 (price).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".



DBase-Editor.txt
Avatar of appari
appari
Flag of India image

can you post few lines from the actual CSV data with header. no need to attach, just paste it here.
Avatar of bpfsr

ASKER

From a text editor:
SKU,Title,Price,Condition,Author,Sellers,UsedSellers,ISBN,Availability,Comments
0000000000_Good,Brother Why?,0.01,Used - Good,,1285,649,,,3.99
0000000000_VeryGood,Brother Why?,0.01,Used - Very Good,,1285,649,,,3.99
0001004344_BrandNew,Mog The Forgetful Cat (Book & Tape),9.22,New,Judith Kerr,17,10,1004344,Usually ships in 24 hours,3.49
0001004344_Good,Mog The Forgetful Cat (Book & Tape),6.7,Used - Good,Judith Kerr,17,10,1004344,Usually ships in 24 hours,3.49
0001004344_LikeNew,Mog The Forgetful Cat (Book & Tape),6.7,Used - Like New,Judith Kerr,17,10,1004344,Usually ships in 24 hours,
0001004344_VeryGood,Mog The Forgetful Cat (Book & Tape),10.18,Used - Very Good,Judith Kerr,17,10,1004344,Usually ships in 24 hours,3.49
0001006614_BrandNew,Butterfly Lion (Book & Tape),"99,999.00",New,Michael Marpugo,1,1,1006614,,3.49
0001006614_VeryGood,Butterfly Lion (Book & Tape),130.9,Used - Very Good,Michael Marpugo,1,1,1006614,,3.49
0001007068_BrandNew,Mog and the Vee Ee Tee (Mog),10.18,New,Judith Kerr,7,4,1007068,Usually ships in 24 hours,3.49
0001007068_LikeNew,Mog and the Vee Ee Tee (Mog),10.18,Used - Like New,Judith Kerr,7,4,1007068,Usually ships in 24 hours,
0001007068_VeryGood,Mog and the Vee Ee Tee (Mog),10.18,Used - Very Good,Judith Kerr,7,4,1007068,Usually ships in 24 hours,3.49
0001010719_VeryGood,Mog and the Baby (Mog),182.6,Used - Very Good,Judith Kerr,2,2,1010719,,3.49
0001025236_BrandNew,The Princess of the Chalet School (The Chalet School),17.98,New,Elinor Brent-Dyer,3,0,1025236,Usually ships in 2 to 5 weeks,3.49
0001025236_VeryGood,The Princess of the Chalet School (The Chalet School),"99,999.00",Used - Very Good,Elinor Brent-Dyer,3,0,1025236,Usually ships in 2 to 5 weeks,3.49
0001025252_BrandNew,The Big Mog Tape,22.72,New,Judith Kerr,7,4,1025252,"In stock soon. Order now to get in line. First come, first served.",3.49
0001025252_VeryGood,The Big Mog Tape,8.98,Used - Very Good,Judith Kerr,7,4,1025252,"In stock soon. Order now to get in line. First come, first served.",
0001046403_BrandNew,Wuthering Heights (HarperCollinsAudioBooks),14.57,New,Emily Bronte,10,6,1046403,Usually ships in 24 hours,3.49
0001046403_VeryGood,Wuthering Heights (HarperCollinsAudioBooks),7.24,Used - Very Good,Emily Bronte,10,6,1046403,Usually ships in 24 hours,3.49
0001046535_LikeNew,As The Crow Flies,9.98,Used - Like New,Jeffrey Archer,9,7,1046535,,3.49
0001046535_VeryGood,As The Crow Flies,6.97,Used - Very Good,Jeffrey Archer,9,7,1046535,,
0001046748_BrandNew,The Hound of the Baskervilles (HarperCollinsAudioBooks),15.14,New,Sir Arthur Conan Doyle,10,5,1046748,"In stock soon. Order now to get in line. First come, first served.",
0001046748_VeryGood,The Hound of the Baskervilles (HarperCollinsAudioBooks),11.62,Used - Very Good,Sir Arthur Conan Doyle,10,5,1046748,"In stock soon. Order now to get in line. First come, first served.",3.49
0001046810_BrandNew,Tess of the D'Urbervilles (HarperCollinsAudioBooks),12.49,New,Thomas Hardy,9,5,1046810,Usually ships in 24 hours,
0001046810_Good,Tess of the D'Urbervilles (HarperCollinsAudioBooks),14.57,Used - Good,Thomas Hardy,9,5,1046810,Usually ships in 24 hours,
0001046810_LikeNew,Tess of the D'Urbervilles (HarperCollinsAudioBooks),1.42,Used - Like New,Thomas Hardy,9,5,1046810,Usually ships in 24 hours,
0001046837_BrandNew,Far from the Madding Crowd (HarperCollinsAudioBooks),13.74,New,Thomas Hardy,6,3,1046837,Usually ships in 24 hours,
0001046837_LikeNew,Far from the Madding Crowd (HarperCollinsAudioBooks),13.75,Used - Like New,Thomas Hardy,6,3,1046837,Usually ships in 24 hours,
0001046934_BrandNew,The Prime of Miss Jean Brodie (HarperCollinsAudioBooks),9.99,New,Muriel Spark,13,6,1046934,Usually ships in 24 hours,3.49
0001046934_LikeNew,The Prime of Miss Jean Brodie (HarperCollinsAudioBooks),12.94,Used - Like New,Muriel Spark,13,6,1046934,Usually ships in 24 hours,3.49
0001046934_VeryGood,The Prime of Miss Jean Brodie (HarperCollinsAudioBooks),14.02,Used - Very Good,Muriel Spark,13,6,1046934,Usually ships in 24 hours,3.49
0001047574_Acceptable,The Adventures of Huckleberry Finn,"99,999.00",Used - Acceptable,Mark Twain,7,5,1047574,,3.49
0001047574_LikeNew,The Adventures of Huckleberry Finn,"99,999.00",Used - Like New,Mark Twain,7,5,1047574,,3.49


From Excel

SKU      Title      Price      Condition      Author      Sellers      UsedSellers      ISBN      Availability      Comments
0000000000_Good      Brother Why?      0.01      Used - Good            1285      649                  3.99
0000000000_VeryGood      Brother Why?      0.01      Used - Very Good            1285      649                  3.99
0001004344_BrandNew      Mog The Forgetful Cat (Book & Tape)      9.22      New      Judith Kerr      17      10      1004344      Usually ships in 24 hours      3.49
0001004344_Good      Mog The Forgetful Cat (Book & Tape)      6.7      Used - Good      Judith Kerr      17      10      1004344      Usually ships in 24 hours      3.49
0001004344_LikeNew      Mog The Forgetful Cat (Book & Tape)      6.7      Used - Like New      Judith Kerr      17      10      1004344      Usually ships in 24 hours      
0001004344_VeryGood      Mog The Forgetful Cat (Book & Tape)      10.18      Used - Very Good      Judith Kerr      17      10      1004344      Usually ships in 24 hours      3.49
0001006614_BrandNew      Butterfly Lion (Book & Tape)      99,999.00      New      Michael Marpugo      1      1      1006614            3.49
0001006614_VeryGood      Butterfly Lion (Book & Tape)      130.9      Used - Very Good      Michael Marpugo      1      1      1006614            3.49
0001007068_BrandNew      Mog and the Vee Ee Tee (Mog)      10.18      New      Judith Kerr      7      4      1007068      Usually ships in 24 hours      3.49
0001007068_LikeNew      Mog and the Vee Ee Tee (Mog)      10.18      Used - Like New      Judith Kerr      7      4      1007068      Usually ships in 24 hours      
0001007068_VeryGood      Mog and the Vee Ee Tee (Mog)      10.18      Used - Very Good      Judith Kerr      7      4      1007068      Usually ships in 24 hours      3.49
0001010719_VeryGood      Mog and the Baby (Mog)      182.6      Used - Very Good      Judith Kerr      2      2      1010719            3.49
0001025236_BrandNew      The Princess of the Chalet School (The Chalet School)      17.98      New      Elinor Brent-Dyer      3      0      1025236      Usually ships in 2 to 5 weeks      3.49
0001025236_VeryGood      The Princess of the Chalet School (The Chalet School)      99,999.00      Used - Very Good      Elinor Brent-Dyer      3      0      1025236      Usually ships in 2 to 5 weeks      3.49
0001025252_BrandNew      The Big Mog Tape      22.72      New      Judith Kerr      7      4      1025252      In stock soon. Order now to get in line. First come, first served.      3.49
0001025252_VeryGood      The Big Mog Tape      8.98      Used - Very Good      Judith Kerr      7      4      1025252      In stock soon. Order now to get in line. First come, first served.      
0001046403_BrandNew      Wuthering Heights (HarperCollinsAudioBooks)      14.57      New      Emily Bronte      10      6      1046403      Usually ships in 24 hours      3.49
0001046403_VeryGood      Wuthering Heights (HarperCollinsAudioBooks)      7.24      Used - Very Good      Emily Bronte      10      6      1046403      Usually ships in 24 hours      3.49
0001046535_LikeNew      As The Crow Flies      9.98      Used - Like New      Jeffrey Archer      9      7      1046535            3.49
0001046535_VeryGood      As The Crow Flies      6.97      Used - Very Good      Jeffrey Archer      9      7      1046535            
0001046748_BrandNew      The Hound of the Baskervilles (HarperCollinsAudioBooks)      15.14      New      Sir Arthur Conan Doyle      10      5      1046748      In stock soon. Order now to get in line. First come, first served.      
0001046748_VeryGood      The Hound of the Baskervilles (HarperCollinsAudioBooks)      11.62      Used - Very Good      Sir Arthur Conan Doyle      10      5      1046748      In stock soon. Order now to get in line. First come, first served.      3.49
0001046810_BrandNew      Tess of the D'Urbervilles (HarperCollinsAudioBooks)      12.49      New      Thomas Hardy      9      5      1046810      Usually ships in 24 hours      
0001046810_Good      Tess of the D'Urbervilles (HarperCollinsAudioBooks)      14.57      Used - Good      Thomas Hardy      9      5      1046810      Usually ships in 24 hours      
0001046810_LikeNew      Tess of the D'Urbervilles (HarperCollinsAudioBooks)      1.42      Used - Like New      Thomas Hardy      9      5      1046810      Usually ships in 24 hours      
0001046837_BrandNew      Far from the Madding Crowd (HarperCollinsAudioBooks)      13.74      New      Thomas Hardy      6      3      1046837      Usually ships in 24 hours      
0001046837_LikeNew      Far from the Madding Crowd (HarperCollinsAudioBooks)      13.75      Used - Like New      Thomas Hardy      6      3      1046837      Usually ships in 24 hours      
0001046934_BrandNew      The Prime of Miss Jean Brodie (HarperCollinsAudioBooks)      9.99      New      Muriel Spark      13      6      1046934      Usually ships in 24 hours      3.49
0001046934_LikeNew      The Prime of Miss Jean Brodie (HarperCollinsAudioBooks)      12.94      Used - Like New      Muriel Spark      13      6      1046934      Usually ships in 24 hours      3.49
0001046934_VeryGood      The Prime of Miss Jean Brodie (HarperCollinsAudioBooks)      14.02      Used - Very Good      Muriel Spark      13      6      1046934      Usually ships in 24 hours      3.49
0001047574_Acceptable      The Adventures of Huckleberry Finn      99,999.00      Used - Acceptable      Mark Twain      7      5      1047574            3.49
0001047574_LikeNew      The Adventures of Huckleberry Finn      99,999.00      Used - Like New      Mark Twain      7      5      1047574            3.49
0001047574_VeryGood      The Adventures of Huckleberry Finn      26.98      Used - Very Good      Mark Twain      7      5      1047574            
Avatar of Mark Wills
hey @appari, was posted already - see above...

well, for starters, you have a fieldterminator of ',' but looks a lot like a tab character (the defailt fieldterminator) - is that correct ? If you noticed, the error corresponds with the field "99,999.00"  which might be causing problems give the fieldterminator mentioned, and the column it is being mapped into (ie money)...

suggest you change price to a varchar and try importing into a temporary version of that table - then have to strip the special characters...

second, it looks like ascii to me, and probably not the error given the above, but, what is the collation of your server and database ?
CREATE TABLE tmp_DBase_Editor 
( 
      sku nvarchar (max) not null,
      title nvarchar (max) null,
      price nvarchar(max) not null,
      condition nvarchar (max) not null,
      author nvarchar (max) null,
      sellers int null,
      usedsellers int null,
      isbn varchar (13) not null,
      availability nvarchar (max) null,
      comments money null 
)
 
 
BULK INSERT tmp_DBase_Editor 
    FROM 'c:\ee1\DBase-Editor.csv' 
    WITH 
    ( 
        Firstrow = 2,
--            FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

Open in new window

apologies, posted my answer and did not see yours. Seems to be comma delimited, but still the error is caused by that same field...
looking at it you have a few instances where quote encapsulation is used seemingly arbitrarily....

meaning you are going to have to do it "the hard way".

how big is it ? is this a once off or repetitive excercise ?
Here you go...

try this lot... Need a format file to imprt as a single line, then break apart that line into individual columns that you can then manipulate...

save the attached format file as .fmt in g:\
if exists (select null from information_schema.tables where table_name = 'tmp_dbase_editor') drop table tmp_dbase_editor
 
CREATE TABLE tmp_DBase_Editor 
( 
      sku nvarchar (max) not null,
      title nvarchar (max) null,
      price nvarchar(max) not null,
      condition nvarchar (max) not null,
      author nvarchar (max) null,
      sellers int null,
      usedsellers int null,
      isbn varchar (13) not null,
      availability nvarchar (max) null,
      comments money null 
)
 
--create a new unstring function
 
create function [dbo].[uStr](@colnum int, @incoming varchar(max))
returns varchar(1000)
as
Begin
 
   declare @int int
   declare @str varchar(1000)
 
   set @int = 0
   set @incoming = @incoming + ',  '
   while len(@incoming) > 1 and @int < 2000 --failsafe of 2000 columns
   begin
 
      set @int = @int + 1
      if substring(@incoming,1,1) = '"'
         begin
            set @str = substring(@incoming,2,charindex('",',substring(@incoming,2,len(@incoming)))-1)
            set @incoming = substring(@incoming,charindex('",',@incoming)+2,len(@incoming))
         end
      else  
         begin 
            set @str = substring(@incoming,1,charindex(',',substring(@incoming,1,len(@incoming)))-1)
            set @incoming = substring(@incoming,charindex(',',@incoming)+1,len(@incoming))
         end
 
      if @int = @colnum return @str
   end 
 
return ''
end
go
 
-- then use that function, nominating the column number to return 
 
insert tmp_DBase_Editor ( sku, title, price, condition, author, sellers, usedsellers, isbn, availability, comments)
 
SELECT dbo.ustr(1,line),dbo.ustr(2,line),convert(money,replace(dbo.ustr(3,line),',','')),dbo.ustr(4,line),dbo.ustr(5,line),
       dbo.ustr(6,line),dbo.ustr(7,line),dbo.ustr(8,line),dbo.ustr(9,line),convert(money,replace(dbo.ustr(10,line),',',''))
 
FROM OPENROWSET(BULK 'G:\dbase_editor.csv' , 
FORMATFILE = 'G:\single_line.fmt',FIRSTROW = 2, MAXERRORS=10) AS A

Open in new window

single-line.fmt.txt
Oh, and price can be money again (see line 7)
Avatar of bpfsr

ASKER

Still getting error message. Keeps saying
Msg 4860, Level 16, State 3, Line 4
Cannot bulk load. The file "G:\single_line.fmt" does not exist.

I have checked and double check that the file is there in that format and I keep getting the same message. I tried starting the procedure from the start and this time I got the error messages:

Msg 2714, Level 16, State 3, Procedure uStr, Line 29
There is already an object named 'uStr' in the database.
Msg 4860, Level 16, State 3, Line 4
Cannot bulk load. The file "G:\single_line.fmt" does not exist
did you rename it from single_line.fmt.txt to single_line.fmt ?

you only create that function once... it is then there ready to be used time and again...

just in case...

using windows explorer, navigate to that folder, then on the right hand side, right click in some "white-space" to get the pop-up window (or use the menu bar File, then New) and select Text Document. It will position you in the filename so you can type in a new name. Type in single_line.fmt and then hit enter. It will ask you if you want to change the name, or, might error because it is already there. If you can change the name successfully, then right click on it and open with (then select notepad - you may need to choose). Simply copy and past the 3 lines below into that file and then save and exit it.

Try the select part (by it's own) again...

Then you can try incorporating the insert statement.
8.0
1
1       SQLCHAR       0      8000     "\r\n"  6     line             ""

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bpfsr

ASKER

I don't know why but when I used Windows explorer to do what you said I noticed although the files were showing in the right hand pane, the plus sign to expand the G drive was not there. Didn't know if that had anything to do with this or not but I decided why not just move the single_line file to the C drive? I did that and changed it to .txt just for good measure and that worked. Thanks for haning in there.
The other "gotcha" is that attachments trnasform the underscore to a hyphen in file names...

Pleased to be of assistance, and glad you have the load working... Now, onto the update now that tmp_Dbase_Editor is up and running :) Will catch you in your other posting !