Importing txt file with multiple headers

[Droopy]

I'm trying to import a .txt file that has 3 lines of headings on every 'page'. I don't need to import the headers and just want the actual data to line up into columns. Is it possible to bypass these header rows on import?

e.g. This is an example of the text file:

----- PFD ----- YEAR TO DATE TRIAL BALANCE: AUGUST 03 PAGE 1
ABCDEF/S 2300.1 THIS MONTH IS STILL OPEN 22/08/03 09:30
-------------------------------------------------------------------------------------
ACCOUNT TYPE DESCRIPTION DEBIT CREDIT
-------------------------------------------------------------------------------------
ACCOUNT A1

------------------------ ------------------------
.00 .00
===========================
LEDGER IN BALANCE
LEDGER IN BALANCE
----- PFD ----- YEAR TO DATE TRIAL BALANCE: AUGUST 03 PAGE 2
ABCDE/S 2300.1 THIS MONTH IS STILL OPEN 22/08/03 09:30
-------------------------------------------------------------------------------------
ACCOUNT TYPE DESCRIPTION DEBIT CREDIT
------------------------------------------------------------------------------------- ACCOUNTB AB 1234567-0 P DESCRIPTION1 ACCOUNT 1,326.48
AB 8357602-0 B DESCRIPTION2 2,762,722.86
AB 0834053-0 P DESCRIPTION3 300.00
AB 0384992-0 B DESCRIPTION5 2,617,277.64

 

[Droopy]

And here's a small sample of the file using the Excel HTML add-in:

sample.txt
	A	B	C	D
1	-----JSW-----YEARTODATETRIALBALANCE:AUGUST03PAGE2
2	MPFNTB/S2300.1THISMONTHISSTILLOPEN22/08/0309:30
3	------------------------------------------------------------------------------------------------------------------------------------
4	ACCOUNTTYPEDESCRIPTIONDEBITCREDIT
5	------------------------------------------------------------------------------------------------------------------------------------
6	MESOPOTAMIA
7	AU0007416-0PEXAMPLEDESCRIPTION1,326.48
8	AU0009535-0BEXAMPLEDESCRIPTION2,762,722.86
9	AU0022208-0PEXAMPLEDESCRIPTION6,135.75
10	AU0022246-0PEXAMPLEDESCRIPTION3,405.00
11	-----JSW-----YEARTODATETRIALBALANCE:AUGUST03PAGE4
12	MPFNTB/S2300.1THISMONTHISSTILLOPEN22/08/0309:30
13	------------------------------------------------------------------------------------------------------------------------------------
14	ACCOUNTTYPEDESCRIPTIONDEBITCREDIT
15	------------------------------------------------------------------------------------------------------------------------------------
16	MESOPOTAMIA
17	AU0907316-0PEXAMPLEDESCRIPTION5,830.75
18	AU0907844-0PEXAMPLEDESCRIPTION1,624.88
19	------------------------------------------------
20	63,355,499.6264,510,104.90
21	================================================
22	OUTOFBALANCE.001,154,605.28
23	-----JSW-----YEARTODATETRIALBALANCE:AUGUST03PAGE5
24	MPFNTB/S2300.1THISMONTHISSTILLOPEN22/08/0309:30
25	------------------------------------------------------------------------------------------------------------------------------------
26	ACCOUNTTYPEDESCRIPTIONDEBITCREDIT
27	------------------------------------------------------------------------------------------------------------------------------------
28	CANADA
sample

 

[bat17]

I think that you will have to do this with brute force; this is not a table with headers but a formatted report. You will need to check several copies of the reports to see that the formatting is consistent!

I think that the way to do it is to read the file in one line at a time.
Check for the 21 spaces that precede your data line.
Then double check that it is data by looking for the <2 Letters, Space, 7 digits, hyphen, 1 digit> pattern.
On finding the first data line you will know the previous line was your group header.
Keep checking and saving lines until that block of data ends.
Start looking for a new block of data, remembering that the line before will be your group header.

You will need to look for rules to split your data line; hopefully your credit/debit amount will either start or end at a fixed number of characters in from the start of the line.

Without a more detailed look at the actual text file it is a job to try to pull out patterns that can be used as rules.

HTH

Peter