DTS Import error (II)

[easyit]

Hi,

I've posted the same question before, but since I have not found a solution yet, I try again.

I've a text file that i want to import. Origin SAP-ISU. Due to the many spaces between the columns, it can only be imported as a fixed field.

The problem is that at certain lines, two lines go into one record.
if you look at the file (below), you see that some lines have the last column(s) not filled (the CrLf is not in the last column). Whenever a last column isn't filled, the following line goes into the same record (Somehow the CrLf is not recognized, or used to create a new record).
How can I get the import right? I need the import function to complete the whole line before inserting a new record.

Can I manipulate something in VB? Has anybody experienced the same trouble?

IMPORT FILE:

Contract     CRblk AanF  Gem   CR          Product   AanA  AO  Portie    Instl       Inhuis-     Status1 Status2 Geblokkeerd   Aanpassings   Normaal
3005568961   XX    F     1     5033468881  NG        A     01  KC0907    6012456690  01.05.2006     X       X    XX            9999          508001313

                                           E               01  NC1211    6001475071  05.10.2005
                                           N               01  NC1211    6001475084  01.12.2000
                                           N               01  NC1211    6001475084  05.10.2005
                                           N               01  NC1211    6001479264  31.10.2005
                                           N               01  NC1211    6001479264  01.12.2000
                                           N               01  NC1217    6001479368  11.01.2002
                                           N               01  NC1217    6001479368  01.12.2000
                                           E               01  NC1216    6001483405  01.12.2000
                                           N               01  NC1216    6001483414  01.12.2000
                                           N               01  NC1216    6001483473  01.12.2000
                                           N               01  NC1216    6001483473  14.10.2006
3005558083               1     5033424792  N               01  KC1202    6009560551  02.03.2006                                              505001826
3005558175               1     5033472005  G               01  KC1017    6008941146  01.03.2006                                              519000116
3005558175               1     5033472006  NG              01  KC1017    6012571123  01.03.2006                                              522000031
3005558456               1     5033412503  N               01  KC0926    6014957830  14.08.2006             X
3005558459               1     5033412956  N               01  KC0926    6014957836  14.08.2006             X
3005558489               1     5033427457  E               03  NC0619    6001717824  03.10.2005                                              511000837
3005558489               1     5033427457  E         A     01  NC0619    6001717824  03.10.2005                                              511000837
3005558489               1     5033427458  N               01  NC0619    6001717835  03.10.2005                                              511000837
3005558489               1     5033427458  N               03  NC0619    6001717835  03.10.2005                                              511000837
3005558495         F                                           RC0407                00.00.0000                                              415079227
3005558496         F                                       03  NC0619                00.00.0000                                              490055643
3005558519         F                                       03  EC0216                00.00.0000                                              450073302
3005558671               1     5033417180  N               03  RC1020    6012226148  01.01.2006             X    K1
3005558671               1     5033417181  E               03  RC1020    6012226149  01.01.2006             X    K1
3005558671               1     5033417182  G               03  RC1020    6012226150  01.01.2006             X    K1
3005558671               1     5033417183  NG              03  RC1020    6013558208  01.01.2006             X    K1
3005558677               1     5033417461  E               03  KC0507    6010323131  13.12.2003             X

EasyIT

"Do you think that’s air you're breathing?

 

[onpnt]

First thought...use a ActiveX Script to replace the spaces with a delimiter and then bcp to slam the data into the table. after that have a stored procedure update based on the missing values. Or really you could do the conditioning in the ActiveX but that will hinder performance

aside from that, how is the txt file formatted and how is it being imported as is.

btw...yes, you would use vbscript in an ActiveX Script Task as mentioned

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[AlexCuse]

Also, have you tried changing your row delimiter from CR-LF to <none> in package? That might be sufficient (I can't see your data though, too wide for my screen).

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[easyit]

Onpnt, I tried Vb as active xscript, but it doesn't even detect the CRLF.
Trying the <none> option that Alex mentioned, that good be a solution; let you know....

EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

...<none> can't work either; the number of characters per line is different (after the ending of a line there is no more data at all), so the stream is chopped into wrong records; hence the same problem as before.




EasyIT

"Do you think that’s air you're breathing?

 

[AlexCuse]

In your fixed file layout, do you specify where the file ends? If you have specified start and end point for each column, I'd think you shouldn't have this problem.

Why is the number of characters per line different?

And I don't think you properly interpreted onpnt's suggestion, ActiveX will never detect a Cr-Lf when one is not there.

Ignorance of certain subjects is a great part of wisdom

 

[easyit]

...if you look at the 4th line the example, after the column "inhuis-" there is no more data; in the file the CRLF is placed after the '5' of '05.10.2005'.
With <none> the file is handled as a stream (isn't it?). I can set the end point, but then after each iteration of this number of chars a new record is created.

Could I ask you to copy paste the data above in a text file and try to import it in table with a DTS package? There should be 30 records after the import (discarding the empty line 3), I only get 20.

You're probably right about onpnt's suggestion; What I've done is analyze each column and if it is null enter a value in the destination column anyway - with no result.

onpnt; what is "bcp"?






EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

Hi,

Just a note; I'm of course very interested in a solution, so if anybody thinks of something...
I'm going on a short holiday, so I cannot react to responses.

I'll be back in the new year.

EasyIT

"Do you think that’s air you're breathing?

 

[AlexCuse]

I got 29 lines (I assumed that you meant top row to be column headers)

Your problem is in how you are trying to import. You are not trying to import a fixed-field text file, you are trying to import a space-delimited text file. So try using a space as your column delimiter and cr-lf as line delimiter, and see how it works for you.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom