Skipping lines in a text file

[Anthony1312002]

Hello. I have a scipt the reads and imports a text file into a database table. Below is an example of the text file I'm importing and the script I'm using to accomplish the import. You'll notice at the beginning of 3 of the lines in the file you see the number 84. What I need to be able to do is skip the first two lines where 84 appears and import the last line containing 84 and then continue to load the rest of the file. How can this be done?

Text File Example
"18",77,"Brinks Home","",08/01/2007,2146,2146,477,477,67,123,46,46,2,
"18",81,"United/Reliable Ins","",08/01/2007,762,762,241,241,136,238,0,0,0
"18",84,"Raleigh/Wake","",08/01/2007,1611,1611,68,68,17,93,0
"18",84,"Raleigh/Wake","",08/01/2007,694,694,75,75,11,10,0
"18",84,"Raleigh/Wake","",08/01/2007,2305,2305,143,143,28,
"18",88,"World Omni 88","",08/01/2007,1389,1389,525,525,
"18",89,"World Omni 89","",08/01/2007,142,142,20,20,3,7,47,47,
"18",99,"PSCU","",08/01/2007,1767,1767,2378,
"18",248,"Virginia Natural Gas","",08/01/2007,0,0,0,0,0,0,2,2,0,0,
"18",304,"Henrico County Real Estate","",08/01/2007,1,1,2,2,0,0,0,0,0,0,0,


Importing Script

Set objFile = CreateObject("Scripting.FileSystemObject")

Dim MyFile, MyFileNew
MyFile = Session("strFileName")

Set objText=objFile.OpenTextFile(MyFile)

RLog.Open "Select * From tblCoreImportTest",Conn,3,3

Do while not objText.AtEndOfStream

strTextLine=objText.Readline
data=Split(strTextLine,",")

[jhardman]

the easiest way to do this is to make one more variable called something like currentRecord. Set currentRecord equal to strTextLine but don't save it to the db. Then go to the next line. If the part in question is still the same (data(1) = 84 on both strTextLine and currentRecord) then set currentRecord equal to the new strTextLine and move to the next record. If it is different, save the currentRecord to the db, then set currentRecord equal to strTextLine and move on. Does this make sense? You don't save any record until you verify that the next record has a different handle.

Jared

[Anthony1312002]

jhardman, thanks for responding. This really helps. I'm really just getting my mind around codeing concepts. I see you mentioned creating a variable and using this to ignore or skip certain lines in the file. I hope it's not asking to much but could you give me a visual of what this would look like using my current code?

[jhardman]

jhardman, thanks for responding. This really helps. I'm really just getting my mind around coding concepts. I see you mentioned creating a variable and using this to ignore or skip certain lines in the file. I hope it's not asking to much but could you give me a visual of what this would look like using my current code?

OK, I assume you already know how to save a record to the db, and you are just asking about how to skip, right? so try this:

Set objFile = CreateObject("Scripting.FileSystemObject")
 
Dim MyFile, MyFileNew, data, nextData
MyFile = Session("strFileName")
 
Set objText=objFile.OpenTextFile(MyFile)
 
RLog.Open "Select * From tblCoreImportTest",Conn,3,3
 
strTextLine=objText.Readline
data=Split(strTextLine,",")
if NOT objText.EOS then
   Do while not objText.AtEndOfStream
      strTextLine = objText.Readline
      nextData = Split(strTextLine, ",")
 
      if nextData(1) = data(1) then 'the next line has the same value for this field
         'discard the old value without saving
         data = nextData
      else 'the next line has a different value
         'save the old data, before updating
         RLog.addNew
         RLog("field1") = data(0)
         RLog("field2") = data(1)
         'etc
         RLog.update
         data = nextData
      end if
   loop
end if
 
'save the data one more time
RLog.addNew
RLog("field1") = data(0)
RLog("field2") = data(1)
'etc
RLOG.update
 

I always make new records with addnew and update, but I assume you have your own way, go ahead and save the data any way you want. The important thing is that I get the first line of the text file into the "data" variable before I start the loop, then each time through the loop I first check to see if the field in question from the next line is the same as the last one. If it is the same I just set "data" equal to the next line without saving the old "data". If it is different, I save the old one, then set data equal to the next line. this will end up with the last line in variable "data" after you finish the loop, so you have to save one more time after you finish the loop. does this make sense?

Jared