Advice on loading fixed width multi record structures

[#1]

Hi

I have a record set which comprises of several header and detail records, all structured differently with a fixed width schema ARRGHHH!.

Example:

H01 INV12345 90012 11/07/2013

H02 ABC Ltd

D01 PO12345

D02 END CAP 00001

D03 VAL0000010

Schema H01:

1 - 3 = Record ID

5 - 16 = Invoice Number

etc....

The individual record types (H01 & D02 etc) all have their own unique schema but I need all of the information to appear together in my target tables if it's to be of any use. I have tried importing using the fixed width wizards etc and whilst I can get the data in, it's not usable as there is no key to link the records together. Do I need an extension to be able to load this data?

Many thanks for your help

Natalie

[maxgro · Accepted solution]

File:

load * inline

[

String

H01 INV12345 90012 11/07/2013

H02 ABC Ltd

D01 PO12345

D02 END CAP 00001

D03 VAL0000010

];

Table:

load

subfield(String, ' ' , 2) as Field1,

subfield(String, ' ' , 3) as Field2,

subfield(String, ' ' , 4) as Field3

resident File

where left(String,3)='H01';

concatenate (Table) load

subfield(String, ' ' , 2) as Field2,

subfield(String, ' ' , 3) as Field4

resident File

where left(String,3)='H02';

// concatenate .......

// concatenate .......

// concatenate .......

drop table File;

[whiteline]

Hi.

You can't do it automatically. Load every data as one field and parse every row using string functions.

If schemes are not supposed to change, it's easy to hardcode the logic.

Could you describe other schemes ?

     ,

[#4]

Hi,

The schemas will not change so I will follow the suggested approach. Many thanks for your help

[#5]

Thank you for providing the example - I will give this a whirl and let you know how it goes.

Gracie

Natalie