Ruth,
Let me take a moment to summarize what I think the overall process is:
You receieve delimited text files which you need to 'parse' and append to an existing (Ms. A '97) db table.
The text files have two types of records. The first simply represents a new record to add to the table, while the second is simply additional fields which should be added to the preceeding record. These "tag" records exist due to a limitation of the originating system (the text file 'builder').
The tag records are recognized by the first dield being empty. When this occurs, the first (empty) field should be ignored, but the remainder of the fields should be added to the previous record. Further, the Third from the last field in the TAG record caries information which you use in some different process (not addressed / explined).
This thread is specifically concerned with this "third from last" field, while the other recent thread discusses the (possible) parseing mechanisim to 'groom' the delimited text file for import to your table.
'_______________________________________________________
'_______________________________________________________
'_______________________________________________________
Please re-state the situation as noted above, correcting the errors / mis-understandings.
'_______________________________________________________
'_______________________________________________________
'_______________________________________________________
Several steps in the text-to excel-to access appear to be un-necessary, and to interfeer with your goal in THIS thread. My thought is that recordsets are generally intended to be more-or-less regular. Attempting to process 'records' with different numbers of ("VALID"?) fields in a recordset is -at best- cumbersome. Since you need to groom (e.g. parse) the records during the import process, you may as well extract the weird field (" ... third from the last ... " during this process.
To start w/, the easiest way to get the records and fields from the delimited text file has been presented. You appear to reject this approach because you don't understand the process. If you're stuck in that position, I can not really help enough to continue this. On the other hand, the process in quite simplistic -and VERY efficient.
The get the file process is just creating a string variable which is the size of the delimited file, and then Get"ting" the contents of the file in the string variable. This is not conceptually different than the process of reading any test file on a line by line basis (e.g. Line Input ... ) - it just does all of hte lines at once.
Seperating the individual lines is nothing more than searching the BIG string for the 'new line' indicator, and placing each bunch of 'stuff' (characters) in a different array element when the delimiter is found.
Seperating the individual fields of each line is just the same process as seperating the lines - using a different criteria for the seperation.
Following this approach, you get each field from the original delimited text file in three steps. Of course, the fields are in array(s) - but that just simplifies the search for the third from the last, which is -at this point- just ubound or the array - 2. Since in the use of the split procedure, no additional "fields " or aray elements are created. So, at this point of the process, you would extract the "field" and save it seperatly.
Somewhere in the process, you need to deal with the 'tag' records mentioned above.
If I have read the threads correctly, these are simply the field arrays where the first field is "empty". Since, in my conceptual approach, the whole delimited text file is just one big array of arrays, searching for the 'tag' records is just a loop through the secondary array, finding those where the first array element ie empty.
Also, there is a mention of variable number of fields even between the records. This is one of the elements of the larger issue, where you need to know how many (if any) fields to add to the table. Since you are looping through the fields array, just accumulate the largest value of UBound in the secondary array - which will be the mininum field count you will require.
But, back at 'tag' line. Once you find a tag line, you need to append all of the tag line array elements (except the 0th) to the preceeding array - and then 'delete' the tag line one. Since deleting an array element dynamically goes further than I think you are able & willing, I will suggest that the 0th element be 'populated' with a flag symbol which will be checked when actually appending records.
If you are still reading this, please provide some feedback.
Assuming we are still 'together', the only issues left are the pading the taable with additional fields as required, and actually appending the records. You appear to know how to append the fields, and the above discusses obtaining the necessary field count, so I'm done with that.
To actually append the records, you either need to use ADO (not a normal part of ver '97) or add the records through code. Assuming the later.
For each 'record'
[tab]with .ret
[tab][tab].addnew
[tab][tab][tab]For each field
[tab][tab][tab][tab]Assign array element to the field
[tab][tab][tab]Next Field
[tab][tab].Update
[tab]End With
Next Record
or something akin to it.
P.S. I wont be suprised if this goee into the balck hole of un-answered.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
