SEPDI File Format Import.

[Nisusmage]

Hello.

I have a problem with importing a flat file (SEPDI) with a header and a trailer to the data. File definition. http://www.qlink.co.za/QLHome/documenta ... neral.html.

I've looked at the CFF stage, however I'm not convinced that this would work.

The of my file is as follows:

Code: Select all

SEPDI Header Record
    TRANSACTION Records
    TRANSACTION Records
    TRANSACTION Records
SEPDI Trailer Record

I need to somehow strip the header and trailer out to be used in the job for validation and import the transaction data into a SQL Server.

Can anyone help.?

[battaliou]

You can simply use a sequential file stage in combo with a transformer. I'm assuming that this is a CSV file rather than fixed width. Either way you can use your transformer to constrain any rows that begin with the string SEPDI. i.e. DSLink.Type[1,5] <> 'SEPDI'

[Nisusmage]

You can simply use a sequential file stage in combo with a transformer. I'm assuming that this is a CSV file rather than fixed width. Either way you can use your transformer to constrain any rows that begin with the string SEPDI. i.e. DSLink.Type[1,5] <> 'SEPDI'

The file is fixed width and the first row has a different definition from the last row and the main data has a completely different definition.

Perhaps I should give you a example of the data.

Code: Select all

QTOP00000000004354306543605071003200710
QDEL00104580       Beginsel                 SC      000000709300010               
QDEL00180864       Bates                    EF      0000001409300010
QUPD00976943       Jooste                   PA     00000000000000010
QUPD00316897       Molema                   N       000071001000000000010                             
QUPD00408032       An Nakene                AN     000001310010000000 
QUPD00408032       An Nakene                AN      00001456000000 
QUPD00408032       An Nakene                AN      000003561000000010                             
QUPD00408032       An Nakene                AG      0000000000000010   
QEND00000000565600000000000356543607           

This isn't real data, but it would be very close to this format.

I don't think a Sequnetial stage would work here because the rows have different schema's.

Any ideas?

[ray.wurlod]

A Sequential File stage will work perfectly well. Each line is read as a single VarChar field (I'm assuming there are line terminators) then parsed in a downstream Transformer stage onto three output links (header, transaction and trailer) based on the first four characters (QTOP, QEND or Qxxx). Parsing can use substring techniques since the columns are fixed width.

[Nisusmage]

A Sequential File stage will work perfectly well. Each line is read as a single VarChar field (I'm assuming there are line terminators) then parsed in a downstream Transformer stage onto three output ...

Thanks Ray, I'm going to give that a try after I've finished trying something else. That did dawn on me when I was working on this other idea (which is prob more complex). I found this beautiful command line (free) prog called SFK.exe (i think there's a unix version as well) and I found a way to filter the data and push it to 3 different files based on the prefix of the line. This should work and I'm almost done, I'm going to finish it for archive purposes.

Thanks for the help, I now have 2 solutions.
I'll let you know and update this post if I have any problems.