• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Bulk import dosent work

Hi!

Have a csv.file That contains of this lines:
103013;EST! EST!! EST!!!75 CL;Svakvin  7 - 15%;Svakvin hvit;Hvit Italia;GRUPPO ITALIANO VINI;BOT;0,750;LTR;Svakvin hvit;8000100645974;A10105
103014;BOLLINGER SPEC CUVEEBRUT, 75 CL;Svakvin  7 - 15%;Svakvin musserende;Muss.Frank Champagne;CHAMPAGNE BOLLINGER SA;BOT;0,750;LTR;Svakvin musserende;3052853075909;A10105

I am using this stored procedure, to import this csv-file

ALTER PROCEDURE "dbo"."VEC_Artikkelimport"
@PathFileName varchar(100),
@FileType int
AS

DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  SET @SQL = "BULK INSERT dbo.VEC_Artikkel_TMP FROM '"+@PathFileName+"' WITH (CODEPAGE='ACP', FIELDTERMINATOR = '"";""') "
 END
ELSE
 BEGIN
  SET @SQL = "BULK INSERT dbo.VEC_Artikkel_TMP FROM '"+@PathFileName+"' WITH (CODEPAGE='RAW', FIELDTERMINATOR = ';',ROWTERMINATOR = '\n') "
 END

EXEC (@SQL)

UPDATE dbo.VEC_Artikkel
SET dbo.VEC_Artikkel.Varenr = dbo.VEC_Artikkel_TMP.Varenr,
dbo.VEC_Artikkel.Varenavn = dbo.VEC_Artikkel_TMP.Varenavn,
dbo.VEC_Artikkel.Prodgrpa = dbo.VEC_Artikkel_TMP.Prodgrpa,
dbo.VEC_Artikkel.Prodgrpb = dbo.VEC_Artikkel_TMP.Prodgrpb,
dbo.VEC_Artikkel.Prodgrpc = dbo.VEC_Artikkel_TMP.Prodgrpc,
dbo.VEC_Artikkel.Produsnt = dbo.VEC_Artikkel_TMP.Produsnt,
dbo.VEC_Artikkel.Pakknenh = dbo.VEC_Artikkel_TMP.Pakknenh,
dbo.VEC_Artikkel.Mengde = dbo.VEC_Artikkel_TMP.Mengde,
dbo.VEC_Artikkel.Kmengde = dbo.VEC_Artikkel_TMP.Kmengde,
dbo.VEC_Artikkel.Varebesk = dbo.VEC_Artikkel_TMP.Varebesk,
dbo.VEC_Artikkel.Eannr = dbo.VEC_Artikkel_TMP.Eannr,
dbo.VEC_Artikkel.Impnr = dbo.VEC_Artikkel_TMP.Impnr

FROM dbo.VEC_Artikkel_TMP
INNER JOIN dbo.VEC_Artikkel
   ON dbo.VEC_Artikkel_TMP.Varenr = dbo.VEC_Artikkel.Varenr

INSERT INTO dbo.VEC_Artikkel (Varenr,Varenavn, 
Prodgrpa,Prodgrpb,Prodgrpc,Pakknenh,Mengde,Kmengde,Varebesk,Eannr,Impnr)

SELECT dbo.VEC_Artikkel_TMP.Varenr,dbo.VEC_Artikkel_TMP.Varenavn, 
dbo.VEC_Artikkel_TMP.Prodgrpa,dbo.VEC_Artikkel_TMP.Prodgrpb,
dbo.VEC_Artikkel_TMP.Prodgrpc,dbo.VEC_Artikkel_TMP.Pakknenh,
dbo.VEC_Artikkel_TMP.Mengde,dbo.VEC_Artikkel_TMP.Kmengde,
dbo.VEC_Artikkel_TMP.Varebesk,dbo.VEC_Artikkel_TMP.Eannr,
dbo.VEC_Artikkel_TMP.Impnr

FROM dbo.VEC_Artikkel_TMP
LEFT OUTER JOIN dbo.VEC_Artikkel
   ON dbo.VEC_Artikkel_TMP.Varenr = dbo.VEC_Artikkel.Varenr
WHERE dbo.VEC_Artikkel.Varenr IS NULL



TRUNCATE TABLE dbo.VEC_Artikkel_TMP

Open in new window


But get this error message:

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 12 (Impnr).

What is wrong ?
0
team2005
Asked:
team2005
  • 4
  • 3
1 Solution
 
Robert SchuttSoftware EngineerCommented:
The error message 'truncation' would seem to indicate that one of the lines contains an 'Impnr' that is longer than the definition of the field. This could also be caused by a previous field containing a separator. I would suggest checking that first, maybe just in an editor or is the file too big? Have you got any tools available to let loose some regular expression on the file? awk, perl perhaps? first counting the number of ; per line and otherwise checking the length of the last field should tell you what's wrong. If no tools available I could write you a little VB script.
0
 
team2005Author Commented:
Hi!

When i saved the file like this:

From:
103013;EST! EST!! EST!!!75 CL;Svakvin  7 - 15%;Svakvin hvit;Hvit Italia;GRUPPO ITALIANO VINI;BOT;0,750;LTR;Svakvin hvit;8000100645974;A10105103014;BOLLINGER SPEC CUVEEBRUT, 75 CL;Svakvin  7 - 15%;Svakvin musserende;Muss.Frank Champagne;CHAMPAGNE BOLLINGER SA;BOT;0,750;LTR;Svakvin musserende;3052853075909;A10105

Open in new window


TO:

103013;EST! EST!! EST!!!75 CL;Svakvin  7 - 15%;Svakvin hvit;Hvit Italia;GRUPPO ITALIANO VINI;BOT;0,750;LTR;Svakvin hvit;8000100645974;A10105
103014;BOLLINGER SPEC CUVEEBRUT, 75 CL;Svakvin  7 - 15%;Svakvin musserende;Muss.Frank Champagne;CHAMPAGNE BOLLINGER SA;BOT;0,750;LTR;Svakvin musserende;3052853075909;A10105

Open in new window


It works, seems that the end of the line contains TAB ?

I tryed to use ROWTERMINATOR =\t insted, but dosent work

Strange ?
0
 
Robert SchuttSoftware EngineerCommented:
> Strange ?

yes, very. I don't see the tab in your post by the way but maybe it got lost in the copy/paste. Then again, maybe there are some other character(s) there, can you check that, maybe with a hex viewer/editor?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
team2005Author Commented:
Hi!

Have a texteditor, that show LF at the end of the lines ?
0
 
team2005Author Commented:
Hi!

Have tryed this change now:

SET @SQL = "BULK INSERT dbo.VEC_Artikkel_TMP FROM '"+@PathFileName+"' WITH (CODEPAGE='ACP', FIELDTERMINATOR = ';', ROWTERMINATOR = ''' + CHAR(10) + ''')"

Open in new window


But gives me this error message:
sg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 12. Verify that the field terminator and row terminator are specified correctly.
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)".

This is so hard to fix ? :(
0
 
Robert SchuttSoftware EngineerCommented:
Ok, if there's only a LF (and no CR) then this is a unix file. The statement you tried with char(10) is correct, I just tried it and it works, here are some screenshots:
capture hex filecapture sqlI'll also attach the data file I used so you can compare it/try it. hopefully it will still hold the LF when uploaded -> it does (in preview) but you have to use right click -> save target as...
data-lf.txt
0
 
team2005Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now