Bulk Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bulk Insert

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-04 : 03:55:48

Hi forumer's,

How can i solve these error. i'm using bulk insert to dump into temp table. the file that im using is CSV file. even i remove the comma from QtyOrder still i got this kind of error. I think this is the error ". i pullout this records through Crystal report and convert to excel file then convert to csv file.

Error:

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (QtyOrder).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (QtyOrder).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (QtyOrder).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (QtyOrder).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (QtyOrder).


Here are the data from my excel csv file.

VENDACCOUNT	ITEMID	NAME	QTYORDERED	REMAINPURCHPHYSICAL	PURCHPRICE	PURCHID	VENDORREF	CREATEDDATETIME	ORIGINALETA	CURRENTETA	LINEAMOUNT	Model
HTI	HTC1017	"ST - Switch, Volume/Power x 3
36H00726-00M,36H00726-10M"	14,800.00	14,800.00	0.78	PO00007948	PH0057768	6/8/2012 18:04	7/20/2012 0:00	7/20/2012 0:00	11,544.00	INCREDIBLE
HTI	HTC1017	"ST - Switch, Volume/Power x 3
36H00726-00M,36H00726-10M"	24,200.00	24,200.00	0.78	PO00007926	PH0057759	6/8/2012 16:15	7/13/2012 0:00	7/13/2012 0:00	18,876.00	INCREDIBLE
HTI	HTC1021	"ST - Battery Connector
75H00993-00M
EVO
June SB
PH0057759"	4,600.00	4,600.00	1.33	PO00007926	PH0057759	6/8/2012 16:15	7/13/2012 0:00	7/13/2012 0:00	6,118.00	INCREDIBLE
HTI	HTC1021	"ST - Battery Connector
75H00993-00M"	4,000.00	4,000.00	1.33	PO00007948	PH0057768	6/8/2012 18:04	7/20/2012 0:00	7/20/2012 0:00	5,320.00	INCREDIBLE
HTI	HTC1031	"Switch, Optical Joystick
36H00864-00M
Incred 
June SB
PH0057762"	200	200	8.24	PO00007928	PH0057762	6/8/2012 16:26	7/13/2012 0:00	7/13/2012 0:00	1,648.00	INCREDIBLE
HTI	HTC1031	"Switch, Optical Joystick
36H00864-00M"	700	700	8.24	PO00007950	PH0057773	6/8/2012 18:09	7/20/2012 0:00	7/20/2012 0:00	5,768.00	INCREDIBLE
HTI	HTC1031	"Switch, Optical Joystick
36H00864-00M
Incred 
LTB
PH0058040"	600	600	8.24	PO00008291	PH0058040	6/21/2012 16:02	8/8/2012 0:00	8/8/2012 0:00	4,944.00	INCREDIBLE
HTI	HTC1070	"ST - RF Connector
75H00806-00M"	100	100	0.9	PO00008022	PH0057844	6/11/2012 18:17	6/22/2012 0:00	6/22/2012 0:00	90	DROIDERIS

My temp table structure:

Create table ##USOpenPO
(VendorAccount nvarchar(35),
Itemid nvarchar(50),
Description nvarchar(100),
QtyOrder numeric(28,12),
QtyOpen numeric(28,12),
Uprice numeric(28,12),
ReferenceNum nvarchar(35),
PONumber nvarchar(35),
CreatedDate datetime,
OriginalETA datetime,
CurrentETA datetime,
OpenCost numeric(28,12),
Model nvarchar(50))

THank you in advance..

jov

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-04 : 04:13:59

My guess is that you aren't catering for the quote dilimited description value.
In the file the 4th column heading is QTYORDERED whereas in the table it is Description.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-04 : 04:33:36

Sorry, i post the wrong data structure.
here is the correct one.

Create table ##USOpenPO
(VendorAccount nvarchar(35),
Itemid nvarchar(50),
Description nvarchar(100),
QtyOrder numeric(28,12),
QtyOpen numeric(28,12),
Uprice numeric(28,12),
ReferenceNum nvarchar(35),
PONumber nvarchar(35),
CreatedDate datetime,
OriginalETA datetime,
CurrentETA datetime,
OpenCost numeric(28,12),
Model nvarchar(50))

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-04 : 04:54:12

With some adjustment, change the location of description and model and its working.
but the result is not correct. please see the data. any input guys. thanks.

This is the source data.. csv file
VENDACCOUNT	ITEMID	Model	QTYORDERED	REMAINPURCHPHYSICAL	PURCHPRICE	PURCHID	VENDORREF	CREATEDDATETIME	ORIGINALETA	CURRENTETA	LINEAMOUNT	itemname
ALP	SAM1209	D700	700	700	9.14	PO00007389	PH0057346	 05/17/2012	 07/23/2012	 07/23/2012	6398.00	Speaker
ISP	SAM1209	D700	400	400	7.75	PO00008275	PH0058003	 06/20/2012	 08/23/2012	 08/23/2012	3100.00	Speaker
ISP	SAM1209	D700	700	700	8.57	PO00007394	PH0056711	 05/17/2012	 07/20/2012	 07/20/2012	5999.00	Speaker
ALP	SAM1209	D700	1000	446	9.14	PO00006701	PH0056710	 04/17/2012	 06/22/2012	 06/22/2012	9140.00	Speaker
ALP	SAM1209	D700	400	400	9.14	PO00008279	PH0058002	 06/20/2012	 08/23/2012	 08/23/2012	3656.00	Speaker
ISP	SAM1209	D700	1000	1000	7.75	PO00006683	PH0056711	 04/16/2012	 06/22/2012	 06/22/2012	7750.00	Speaker
ISP	SAM1209	D700	100	100	8.57	PO00007396	PH0057345	 05/17/2012	 07/20/2012	 07/20/2012	857.00	Speaker


result of bulk insert:
VendorAccount	Itemid	Description	QtyOrder	QtyOpen	Uprice	ReferenceNum	PONumber	CreatedDate	OriginalETA	CurrentETA	OpenCost	Model
ISP	SAM1209	"Speaker GH59-09780A"	700.00	700.00	8.57	PO00007394	PH0056711	5/17/2012	7/20/2012	7/20/2012	"5	999.00",D700                                                                                        
ALP	SAM1209	"Speaker GH59-09780A"	"1	000.00"	446.00	9.14	PO00006701	PH0056710	4/17/2012	6/22/2012	6/22/2012	"9,140.00",D700                                                                                     
ALP	SAM1209	"Speaker GH59-09780A"	400.00	400.00	9.14	PO00008279	PH0058002	6/20/2012	8/23/2012	8/23/2012	"3	656.00",D700                                                                                        
ISP	SAM1209	"Speaker GH59-09780A"	"1	000.00"	"1	000.00"	7.75	PO00006683	PH0056711	4/16/2012	6/22/2012	6/22/2012,"7,750.00",D700                                                                           
ISP	SAM1209	"Speaker GH59-09780A D700  June SB PH0057345"	100.00	100.00	8.57	PO00007396	PH0057345	5/17/2012	7/20/2012	7/20/2012	857.00	D700                                                                                                
ALP	SAM1209	"Speaker GH59-09780A"	700.00	700.00	9.14	PO00007389	PH0057346	5/17/2012	7/23/2012	7/23/2012	"6	398.00",D700                                                                                        
ISP	SAM1209	"Speaker GH59-09780A"	400.00	400.00	7.75	PO00008275	PH0058003	6/20/2012	8/23/2012	8/23/2012	"3	100.00",D700

Btw, when i paste the data to notepad, i think the alignment is disoriented maybe this is the cause of the problem.

VENDACCOUNT	ITEMID	Model	QTYORDERED	REMAINPURCHPHYSICAL	PURCHPRICE	PURCHID	VENDORREF	CREATEDDATETIME	ORIGINALETA	CURRENTETA	LINEAMOUNT	itemname
HTI	HTC1031	INCREDIBLE	600	600	8.24	PO00008291	PH0058040	 06/21/2012	 08/08/2012	 08/08/2012	4944	Switch, Optical Joystick
HTI	HTC1070	DROIDERIS	100	100	0.9	PO00008022	PH0057844	 06/11/2012	 06/22/2012	 06/22/2012	90	ST - RF Connector
HTI	HTC1070	DROIDERIS	150	150	0.9	PO00007912		 06/08/2012	 07/20/2012	 07/20/2012	135	ST - RF Connector
HTI	HTC1117	TOUCHPRO2	150	150	0.03	PO00007912		 06/08/2012	 07/20/2012	 07/20/2012	4.5	Tamper Proof Sticker
HTI	HTC1124	TOUCHPRO2	3800	3800	3.06	PO00007948	PH0057768	 06/08/2012	 07/20/2012	 07/20/2012	11628	ST - Vibrator
HTI	HTC1124	TOUCHPRO2	1400	1400	3.06	PO00007926	PH0057759	 06/08/2012	 07/13/2012	 07/13/2012	4284	ST - Vibrator
HTI	HTC1128	TOUCHPRO2	150	150	2.02	PO00007912		 06/08/2012	 07/20/2012	 07/20/2012	303	ST - Mic
HTI	HTC1158	6277	500	500	3.8	PO00007243	PH0057155	 05/11/2012	 06/20/2012	 06/20/2012	1900	Loudspeaker
HTI	HTC1159	6277	26050	26050	3.28	PO00008288	PH0058040	 06/21/2012	 08/10/2012	 08/10/2012	85444	Receiver Speaker

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-04 : 05:18:00

It's working now...thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources