Moving data from an Array to a Recordset

[Timtropolis]

Greetings all,

I have a problem and was wondering if I could get some help/hindsight.

I have a information file that I need to process. The file is HUGE. Plus it is denormalized, meaning that it has header info, type info and origination info, as well as the records being processed.

In order to even get this in the system, I first need to read it in as a FileSystemObject and save it into an array variable. I then take the raw file and strip out the junk. I then apply the Split function which breaks it down into records. After I'm done, I'm left with the detail records. Each record contains 90 fields for a total length of 1417 characters which is delimited by the pipe character (|). There are 1800 records in total.

My issue now is this. I have the data the way I want, what is the best way to get this into a recordset (or a table)??

My first thought was to enumerate thru the destination table and the array, so that way I didnt' have to write out all 90 fields in code but got errors when attempting to enumerate the array. I also thought of writing out to a txt file using the "write" method, which works, but I can't loop to the EOF (gives me an error).

If anyone has some suggestions, it would be most welcome

TIA,
Tim

 

[jdraw]

You could import the data into a temporary table. It wouldn't be formatted but it would be in Access. You can import pipe delimited.

Where is the data going ultimately -- ie 6 tables???
Do you have a data model of where the data will end up?

Is the incoming data in some sort of pattern ie 2 header records, 14 details records and a trailer record etc?

Why does it have to be in arrays?
You can manipulate a temp table.

Just a few comments.