[elancry]

Hi All

I have in the lab a bettery tester that produce results files as text. The text file structure is: first two lines are information of the experiment and from line 3 each line is a record which is composed of 10 items tab delimited.
I need to read every record but I need to compare few items from the current record with the same items from previous and next records in order to write the current record to the database.
I tried to read the text file line by line but in order to extract the items from previous and next records I am always skipping one line (while extracting the items from the next line).

How can I overcome this skipping?

My code is as follows:

Dim fnum As Integer
Dim lActive As Long
Dim PreRec As String
Dim CurRec As String
Dim NexRec As String
Dim strPreRec() As String
Dim strCurRec() As String
Dim strNexRec() As String

Do Until EOF

Line Input #fnum, PreRec ' extract Previous Line
Line Input #fnum, CurRec ' extract Current Line
Line Input #fnum, NexRec ' extract Next Line

strPreRec = Split(PreRec, vbTab)
strCurRec = Split(CurRec, vbTab)
strNexRec = Split(NexRec, vbTab)

' Here comes the comparison with previous and next records

' Write the record to the database
Loop

Thanks in advance

Eli

[EmptyVessel]

Hi Eli,

If your text file is not too large then one option is to read the entire thing into a single string. Split the string by your new line character(s) such that every line is an element of a string array. The advantage of this approach is that you can move forward and back throughout the array as many times as is necessary.

Another method would be to read the entire text file into a temporary database table then use DB tools to filter the final output to your destination table.

[soathiam]

Hi Elancry,

my approach would have been like emptyVessel said, using a temporary database, or if the file not too large, two datatables; one (on which you do your data treatment) containing all the data and the second one, just the data you want to push to your database.

[elancry]

Hi EmptyVessel and soathiam,

First, thanks for your replies.

My files can contain more then 100k records although most of my files are around 10k-20k.
I think that the concept of temp db is more rational. Can you direct me to code sample dealing with temp db?

Thanks again

Eli

[EmptyVessel]

Depends on what database you are using.

If you are talking MS Access then you could use the ADOX object to Create an entirely new temp .mdb file, new temp.table, Populate it, Query it then toss the whole thing when done.

Basically you would use your original concept but enter every line beyond line two.

Higher end DB's or DB servers allow temporary creation of tables and views.

--- A third possibility ---

Read the entire source file one line at a time padding each tab separated field with space characters such that each field member is the same size. output to a random access file. This would give a consistent record size for each line.

Open your random access file and read any line offset (Record) you desire in whatever order you like, Filter and dump to your final DB. Toss the temporary random access file.

This option trades memory usage for disk space usage. (Pretty much the same as using a database)

[Tinbendr]

Originally Posted by elancry

... I am always skipping one line (while extracting the items from the next line).

How can I overcome this skipping?

I don't quite understand this. Are you reading line 1 or 2 from the next record, or are you reading a blank line? Give us an example of what you're trying to describe.

[DougT]

I think we need to see all your code rather than edited extracts.

If I understand the file structure you have something like:

Description1
Description2
Result1
Result2
Result3
Result4
.
.
etc

The method of reading in 3 lines at a time is OK when you start, but you must remember that once you've processed the current record, the 'old' next record must become the 'current' record, the old 'current' record must become the 'previous' record. You must then read one more record which becomes the new 'Next' record. The very first result doesn't have a 'previous' record and the very last one doesn't have a 'next' record. You'll have to take those 'special' conditions into account as well.