Hi
I’m hoping that someone can lead me in the right direction here.
I am trying to work out how I can create an import routine within Excel 2007 to allow for the processing of files which contain headers and trailer records.
The files are received as fixed width records and comprise of three discrete components
Header record – always the first three lines (records) in the file
Transaction details – will contain a variable number of lines
Trailer record – always the last line of the file
Using the Import wizard, I can ‘skip the first three lines’ of the file and then import the transaction records according to the data format that I have. However, this does not allow me to save the header information and will import the trailer information with the transactions.
A sample of how the file will look
(the bold text is to show where a different section starts and is not in the files themselves)
HEADERS rows 3
HDR0 ****990100
HDR1AAABBCCZ0001 X 01 13375
UHL212345678034 00000000 000 Andy M Report
TRANSACTIONS – N Rows
99889912312312X99ABC123998877880A9800000019787Description 1 61378704
99889912312312Y99ABC123998877880A9900000235305Description 2 62147680
99889912312312A22ABC456998877880A9900000070333Description 3 62649012
99889912312312B88ABC456998877880A9900000034640Description 4 62660436
TRAILER 1 Row
XYZABCDEF123456000001211210000530000001
Ideally, I’m looking for something that will allow me to say there are three header rows – copy positions 1 to 4 to a worksheet named SUMMARY with a label X and then positions 5 to 18 to the same sheet with label Y.
Then the fourth row – import the rows into a sheet named DETAIL split the file into X fields (all as text) with from headers along the lines of
Positions 1 to 6 – Label = Date
7 to 14 - Source
15 to 36 - Reference
37 to 47 – Amount
Etc.
Keep doing this until the row starts XYZ in which case import the entire row into the SUMMARY sheet with a label – Trailer
I’ve tried writing a macro after I have imported the into a new sheet – this works in part but I’m sure that there’s a more elegant way
I have searched the forums – but can’t find anything that works on the file type that I have – if someone does know of a previous thread that gets me somewhere close to what I am looking for – please let me know
Thanks in advance for any assistance, or even reading this and wishing me luck .
Cheers
Andy
I’m hoping that someone can lead me in the right direction here.
I am trying to work out how I can create an import routine within Excel 2007 to allow for the processing of files which contain headers and trailer records.
The files are received as fixed width records and comprise of three discrete components
Header record – always the first three lines (records) in the file
Transaction details – will contain a variable number of lines
Trailer record – always the last line of the file
Using the Import wizard, I can ‘skip the first three lines’ of the file and then import the transaction records according to the data format that I have. However, this does not allow me to save the header information and will import the trailer information with the transactions.
A sample of how the file will look
(the bold text is to show where a different section starts and is not in the files themselves)
HEADERS rows 3
HDR0 ****990100
HDR1AAABBCCZ0001 X 01 13375
UHL212345678034 00000000 000 Andy M Report
TRANSACTIONS – N Rows
99889912312312X99ABC123998877880A9800000019787Description 1 61378704
99889912312312Y99ABC123998877880A9900000235305Description 2 62147680
99889912312312A22ABC456998877880A9900000070333Description 3 62649012
99889912312312B88ABC456998877880A9900000034640Description 4 62660436
TRAILER 1 Row
XYZABCDEF123456000001211210000530000001
Ideally, I’m looking for something that will allow me to say there are three header rows – copy positions 1 to 4 to a worksheet named SUMMARY with a label X and then positions 5 to 18 to the same sheet with label Y.
Then the fourth row – import the rows into a sheet named DETAIL split the file into X fields (all as text) with from headers along the lines of
Positions 1 to 6 – Label = Date
7 to 14 - Source
15 to 36 - Reference
37 to 47 – Amount
Etc.
Keep doing this until the row starts XYZ in which case import the entire row into the SUMMARY sheet with a label – Trailer
I’ve tried writing a macro after I have imported the into a new sheet – this works in part but I’m sure that there’s a more elegant way
I have searched the forums – but can’t find anything that works on the file type that I have – if someone does know of a previous thread that gets me somewhere close to what I am looking for – please let me know
Thanks in advance for any assistance, or even reading this and wishing me luck .
Cheers
Andy
