Extracting data from non-normalized text document

[fredalina]

I have data from a .txt file which is a report from a mainframe system. I have no control over the format of this data, and it is not something that will make a priority list for the mainframe people to change, so I'm stuck with it.

The report is by product line, approximately 10 items per product (Avg Daily Sales, MTD Sales, Wholesale Sales, Retail Sales, etc). The "columns" in the report are National total and regional totals. All I want is the total Retail Sales and total Wholesale sales, by product, at a national level, and I want to store this in a table.

I certainly can import this data, but I'm not sure how to extract just the pieces of data I need. I can perhaps use a DLookup() function, but this seems cumbersome and I can't quite wrap my head around how to go about it. Surely there is a simpler way? I am trying to keep this database as normalized as possible.

Thanks!

 

[boblarson]

Create an import specification and then import the entire text file to a transitional table each time. You can clear that table either just before you import or after you have finished each import. Then you can use Append or Update queries to move the data to the correct location in your normalized database.

 

[gemma-the-husky]

dont know if you have this sort of problem regularly, but in a REALLY powerful tool is something called monarch (might be monarch pro). Surprisingly it was quite hard to track down, when I identified it for a client. In a corporate setting it could prove a very useful investment

it can actually analyse prn files (and therefore obviously txt files) and enable you to selectively pull out certain bits into a csv file.

Not cheap, (a few hundred pounds/dollars) but not mega-costly, but for what it does, I think its unbeatable, and if you need that functionality, it would repay the cost handsomely.

 

[jdraw]

Just a thought - can you link to the mainframe source data with a ReadOnly account. If so, then you could do your own report/tables etc. If not, can you get the Mainframe people to create a file/query for you to better get the data in the format you need?

 

[fredalina]

Just a thought - can you link to the mainframe source data with a ReadOnly account. If so, then you could do your own report/tables etc. If not, can you get the Mainframe people to create a file/query for you to better get the data in the format you need?

Well, CAN i, or will it ever happen? CAN i? Yes, in theory, but the red tape is horrid so it's not likely to happen. Such a shame because it could help so many.

i've got something working for now; thanks for all your suggestions!