Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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 ?
,
Hi,
The schemas will not change so I will follow the suggested approach. Many thanks for your help
Thank you for providing the example - I will give this a whirl and let you know how it goes.
Gracie
Natalie