469,610 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Skipping lines in a text file

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,",")
Aug 7 '07 #1
3 1776
jhardman
3,406 Expert 2GB
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
Aug 8 '07 #2
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?
Aug 17 '07 #3
jhardman
3,406 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Set objFile = CreateObject("Scripting.FileSystemObject")
  2.  
  3. Dim MyFile, MyFileNew, data, nextData
  4. MyFile = Session("strFileName")
  5.  
  6. Set objText=objFile.OpenTextFile(MyFile)
  7.  
  8. RLog.Open "Select * From tblCoreImportTest",Conn,3,3
  9.  
  10. strTextLine=objText.Readline
  11. data=Split(strTextLine,",")
  12. if NOT objText.EOS then
  13.    Do while not objText.AtEndOfStream
  14.       strTextLine = objText.Readline
  15.       nextData = Split(strTextLine, ",")
  16.  
  17.       if nextData(1) = data(1) then 'the next line has the same value for this field
  18.          'discard the old value without saving
  19.          data = nextData
  20.       else 'the next line has a different value
  21.          'save the old data, before updating
  22.          RLog.addNew
  23.          RLog("field1") = data(0)
  24.          RLog("field2") = data(1)
  25.          'etc
  26.          RLog.update
  27.          data = nextData
  28.       end if
  29.    loop
  30. end if
  31.  
  32. 'save the data one more time
  33. RLog.addNew
  34. RLog("field1") = data(0)
  35. RLog("field2") = data(1)
  36. 'etc
  37. RLOG.update
  38.  
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
Aug 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

22 posts views Thread by Ling Lee | last post: by
3 posts views Thread by Ivan Liu | last post: by
6 posts views Thread by lisa.engblom | last post: by
6 posts views Thread by ivan.perak | last post: by
7 posts views Thread by Gustaf | last post: by
reply views Thread by Jerry Coffin | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.