Parsing data from legacy system

[Peoplesoft bloke]

I have to get data from a legacy system - I can run a report that outputs the data we need in a report (text file) that has 69 lines per record. I need a quick and effecient way to read the 69 lines for each record and find the bits of data we want (each one is in a fixed position), write all of the data to a CSV or simlar file so I can grab it and import it.

I reckon I could cobble something up in PeopleCode (which is similar to VB) but it would take several days (I am not a programmer by any stretch of the imagination) and probably would not work. Any pointers to tools/example scripts anyone has would be gratefully recieved. I know it's a cheek, but if you don't ask..........

BTW I have access to MS Office apps so could use VB in that - also have a linux box running Ubuntu so could use any handy utilites that might be lying around. I have tried Googling (a lot) but must be typing in the wong questions......

[Spacecadet]

Sql Server Integration Services (SSIS) was born for this

[bored]

Perhaps, write a Perl or Python script to do that?

[oracleslave]

Offshore it and throw resource at the problem

[TheFaQQer]

Assuming an Oracle 9i or above database, can you not pull the report extract in as an external table, and just select against it in SQL to get the data that you want?

[Peoplesoft bloke]

Assuming an Oracle 9i or above database, can you not pull the report extract in as an external table, and just select against it in SQL to get the data that you want?

Brill idea - many thanks - sadly, they won't give me that kind of access to the Oracle DB and I don't have one of my own I can use (maybe I should though).

I do, however, have SSIS on a VM I can use so I'll give that a go later - last time I used SSIS it was happy with fixed length or csv (or similar) but wanted a record per line - however I haven't used it for while so I'll look again.

Thanks for all the replies.

[Spacecadet]

Brill idea - many thanks - sadly, they won't give me that kind of access to the Oracle DB and I don't have one of my own I can use (maybe I should though).

I do, however, have SSIS on a VM I can use so I'll give that a go later - last time I used SSIS it was happy with fixed length or csv (or similar) but wanted a record per line - however I haven't used it for while so I'll look again.

Thanks for all the replies.

good choice
You may need to write a tiny bit of VB to handle the non standard text file

[where did my id go?]

awk?
SQL-Loader?

I'm a bit of a luddite though - I'd probably do it with a bit of c.

any issues with the format? control characters? whitespace?

[Platypus]

I have to get data from a legacy system - I can run a report that outputs the data we need in a report (text file) that has 69 lines per record. I need a quick and effecient way to read the 69 lines for each record and find the bits of data we want (each one is in a fixed position), write all of the data to a CSV or simlar file so I can grab it and import it.

I reckon I could cobble something up in PeopleCode (which is similar to VB) but it would take several days (I am not a programmer by any stretch of the imagination) and probably would not work. Any pointers to tools/example scripts anyone has would be gratefully recieved. I know it's a cheek, but if you don't ask..........

BTW I have access to MS Office apps so could use VB in that - also have a linux box running Ubuntu so could use any handy utilites that might be lying around. I have tried Googling (a lot) but must be typing in the wong questions......

Perl is born for stuff like this. You could probably pay someone on here to do it for you in a few hours, then charge your client $$$.

[thunderlizard]

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.