Parsing data from legacy system

[OrangeHopper]

We do similar things here. Monthly reports, produced by Oracle's Discoverer, are imported into Excel and macros are run to extract and reformat. Unfortunately, it took a few days for the Excel/VB novice (i.e. me) to sort it out.

<enter tongue-in-check, not too serious mode>
My parse2 stuff is designed to tackle this sort of problem. I could put a page togther for you where you paste the report and it outputs the data you want extracted.
</enter tongue-in-check, not too serious mode>

[Peoplesoft bloke]

How automated or repeatable does it need to be?
If "not very", you could open it in Excel, use the Text-To-Columns function, and save it as CSV. You could (probably) automate all of that with macros too.

I'll only need to do it once "In anger", but I'm running it with smaller test versions of the data files to prove it before loading the full size data files. I'm also using test versions of the data when away from site to avoid taking sensitive data off site.
I'd discounted Excel as the wizard only offered to open text files as fixed width or delimited - and this file, although fixed width, has 70 lines per record.

[Sysman]

If it's a one off muck around...

But if it's something which will occur again, do it properly.

COBOL was designed for this job

Quotation on request.

[thunderlizard]

You could still use Excel: just copy-and-paste the text in starting at A1 (then it won't bother you with the import wizard thing) and use Text-to-columns to split it. Even with multiple rows per record, as long as it's consistent, you can write a macro to get the data items you want using Range.Offset, because they'll all be in fixed positions relative to the top-left cell of each record.


Excel's great. 90% of the world's financial sector can't be wrong! (except when they are...)

[xoggoth]

Agree with thunderlizard, find excel is really good for this sort of thing, with or without using its vba. Could post you a message with example with read/write to text files from execl vba if you decide to go that route.

[BrilloPad]

You could still use Excel: just copy-and-paste the text in starting at A1 (then it won't bother you with the import wizard thing) and use Text-to-columns to split it. Even with multiple rows per record, as long as it's consistent, you can write a macro to get the data items you want using Range.Offset, because they'll all be in fixed positions relative to the top-left cell of each record.


Excel's great. 90% of the world's financial sector can't be wrong! (except when they are...)

most credit sysystems at IBs are written in excel - the better ones use mysql. I know ClientCo was threatened with closure by IFA several times - perhaps if they had been they might have put together proper systems and understood their risk better.