[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Bulk import dosent work

Posted on 2012-08-13
7
Medium Priority
?
539 Views
Last Modified: 2012-08-29
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
Comment
Question by:team2005
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38287554
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
 
LVL 2

Author Comment

by:team2005
ID: 38287735
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38287764
> 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:team2005
ID: 38290847
Hi!

Have a texteditor, that show LF at the end of the lines ?
0
 
LVL 2

Author Comment

by:team2005
ID: 38291030
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
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 38291308
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
 
LVL 2

Author Closing Comment

by:team2005
ID: 38344442
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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