Solved

Text to Sql Server Process Question

Posted on 2014-04-08
6
245 Views
Last Modified: 2014-04-14
I have an RFID timing system that returns race finishers via a line in a text file.  Lets call that text file "raw reads".  Each participant is read at least once but usually several times due to the fact that there are several finish line mats, each with it's own reader.

I have written code to parse that data into an array (basically pulling out their best time and their bib number), shown in a list box.  At this point I send that to a sql server database that collects the other information about each participant (name, gender, age, etc).  This works fine, but...

Since I refresh this array multiple times during the race, and some races get pretty big (several thousand runners), I would like to not have to go back and get all data from the "raw reads" text file.  I would like to just pick up where I left off.  How can I do that?

Here is the code I use to get the data from the "raw reads" text file:
    k = 0
    
    If Not sRFIDFile = vbNullString Then
        frmRFID2.lstRawData.Clear
        frmRFID2.lstRawData.AddItem "NO" & vbTab & "DATA"
        
        iFileNum = FreeFile()

        Open sRFIDFile For Input As iFileNum
        sFileData = Input(LOF(iFileNum), #iFileNum)
        Close iFileNum
        
        LineArr = Split(sFileData, vbCrLf)
        sFileData = vbNullString
        
        For i = 0 To UBound(LineArr) - 1
            strFields = Split(LineArr(i), ",")

            sThisLine = LineArr(i)
            
            sThisBib = strFields(1)
            sThisChip = strFields(2)
            sThisTime = strFields(3)
                
            If Len(sThisBib) <= 4 Then
                sThisTime = Replace(sThisTime, """", "")
                sngThisTime = ConvertToSeconds(sThisTime)
                iThisBib = CInt(sThisBib)
            
                If sngThisTime > sngStartTime + sngMinTime Then
                    RawRslts(0, k) = k + 1
                    RawRslts(1, k) = sThisBib
                    RawRslts(2, k) = sThisTime
                    RawRslts(3, k) = sThisChip

                    frmRFID2.lstRawData.AddItem k + 1 & vbTab & LineArr(i)

                    k = k + 1
                End If
            End If
        Next i
    End If

Open in new window


Any suggestions on how to better manage this process would be much appreciated.

Note:  I have set the RawRslts array dim at 11000 to avoid any ReDim Preserve issues.  Is there a better way to do that?
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 39987492
This may be a dumb question but is there anyway to make the files with a timestamp?

Such as Mat1_raw_reads_1404181132.txt then the next Mat1_raw_reads_1404181133.txt. That way you would have your app just do a continuous dir for the files to open and read in succession and if the file has been read you skip it on the next pass.

Rough code example:

PathName = "C:\timerfiles\"

FileName =  Dir("C:\timerfiles\" & "*raw_reads*.txt")

Do while FileName <> ""

If FileName Not in select ReadFiles FROM FileList WHERE ReadFiles <> FileName 

sRFIDFile  = PathName & FileName 
        Open sRFIDFile For Input As iFileNum
        sFileData = Input(LOF(iFileNum), #iFileNum)
        Close iFileNum
        
' The process code
FileName = Dir
LOOP

Open in new window

0
 

Author Comment

by:Bob Schneider
ID: 39987505
I might be able to append the files but I really want to keep that file intact since it comes right out of the timer...kind of sacred data.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 39987560
I'm saying that file1 is a stand alone file from file2, from file3, and so on. Not a matter of appending them together.

So a directory would look like:
Mat1_raw_reads_1404181132.txt
Mat1_raw_reads_1404181133.txt
Mat1_raw_reads_1404181134.txt
Mat1_raw_reads_1404181135.txt
Mat1_raw_reads_1404181136.txt
Mat1_raw_reads_1404181137.txt
Mat1_raw_reads_1404181138.txt
Mat1_raw_reads_1404181139.txt
Mat1_raw_reads_1404181140.txt
Mat1_raw_reads_1404181141.txt
Mat1_raw_reads_1404181142.txt
Mat1_raw_reads_1404181143.txt
Mat1_raw_reads_1404181144.txt

Open in new window


Then you only process the new files, not every file again.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bob Schneider
ID: 39987638
Interesting idea.  I like the idea of creating successive text files...but I can't change the fact that the original text file will have all the reads.  It is created by the third party rfid timing system.  Is there a way to use your idea and keep the original text file intact?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39987733
So the system appends to the raw_reads.txt file from beginning to end?

In that case try this idea. Instead of reading the whole file as one thing do a line input row by row.

select LineNum = CurrLine from LineNumTbl

        Open sRFIDFile For Input shared  As iFileNum
       if I < LineNum Then
           Do While I < LineNum 
             Line Input #iFileNum, sFileData
             I=I+1
           Loop
       End If
Do Until EOF( &iFileNum) = True
     Line Input #iFileNum, sFileData
     ' rest of processing
      I=I+1
Loop

Close #iFileNum

SQL = "UPDATE LineNumTbl " & _
       "SET CurrLine = " & I 

RunSQL SQL

Open in new window


Then the next time you run it will chunk through the first 250 rows in seconds and then from there it will just import and process the new rows.
0
 

Author Closing Comment

by:Bob Schneider
ID: 39998617
Very helpful.  Thank you!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question