Import Tagged File?

[lameid]

Some months you just wish you could call in and say, "I'm hibernating this month, see you on the first." (Ok slight exageration).

The company I work for needs to interface with a utility's system to handle customer sign-ups, retrieve quantities used etc. for customers served by the utility and (at the customer's option) supplied by us.

Unforunately this means that the utility defined the file layout.

Instead of giving us normalized flat text files or slightly worse multiple tables in the same file, I have the privelege of importing a tagged file.

Each line begins with a field called a record indicator which defines what type of record it is. There are thirteen distinct record layouts. The records are tab delimited. Great you think, simply write a low level routine that breaks each record type out into its own file and import them separately. But it's not that simple. Each record's parent record may be defined by the record that preceded it or the last record of a particular type that preceded it (no foreign keys in detail records). This is a particularly nebulous point in the specification as to what the parent record should be. I'll get clarification at some point on this but I thought I'd throw what I've been able to discern out there for help...

The only approach that comes to my mind is to write a low level file parser for the record types and have it concatenate foreign keys (and possibly Primary Keys) into the appropriate record types and then import.

I'm hoping that someone knows of an object that I can include into Access to make my life easier or has the truly brilliant methodology to make this work easily.

 

[JeremyNYC]

Call in and hibernate.

Just kidding.

I think you're going to have to wait to hear about the keys to be able to make much sense of this, but you've got me curious here. No foreign keys? That's pretty amazing.

If the fk info is in the previous record, or the previous record of a particular type, depending on what type of record you're looking at, then you will be able to get down and write some code that does all this. Sounds kind of fun, actually. But then, I don't have to take the heat if it goes wrong.

In any case, post back when you get more info, as it is an interesting problem.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[lameid]

Open, FreeFile, Print, file handles...
Capturing the last recordtype that is x and associating to all the detail records of yz recordtype... Lots of fun (Unless the Groundhog sees his shadow).

Alternately I could just open recordsets and write the data direct to tables. Somehow I think this would take longer than manipulating keys into the files and then importing (both in lines of code and execution time).

I'll keep you posted.