• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Stripping the double quotes and removing the last field of each record - VBS

Source file looks like:

"Doodles Caboodles ","877 EXECUTIVE CENTER DR W STE 300 ","STPETERSBURG ","FL","33702 ","7275766630","$9.95"
"Doodles Caboodles ","877 EXECUTIVE CENTER DR W STE 300 ","STPETERSBURG ","FL","33702 ","7275766630","$9.95"
"Doodles Caboodles ","1111 S 21ST ST ","Anyplace","FL","55555 ","4053084196","$9.95"

Need to remove double-quotes and the last field which is the dollar amount.  Maybe by putting the data into an array and printing the fields I need, not sure how to accomplish that either.  Started with the code:

'Declare Variable
Dim objRegExpr
Dim fname

'Create instance of regexp object
Set objRegExpr = New regexp
 
fname = ("C:\projects\test.txt")
objRegExpr.Pattern = "[^a-zA-Z0-9\.\-\'\,@"+chr(43)+"]"
objRegExpr.Global = true
objRegExpr.IgnoreCase = true
0
Webcc
Asked:
Webcc
  • 3
  • 2
1 Solution
 
fostejoCommented:
Webcc,

The following example reads in a text file, in this case "C:\test.txt", of the format specified and splits each line up into an array (arrR) which can then be manipulated as you wish..


Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\test.txt", ForReading)

Do While objTextFile.AtEndOfStream <> True
    strL=objtextFile.Readline
    arrR=split(strL,",")
    for k=0 to 6
        arrR(k)=replace(arrR(k),chr(34),"")
    next

    Wscript.Echo arrR(0)
    Wscript.Echo arrR(1)
    Wscript.Echo arrR(2)
    Wscript.Echo arrR(3)
    Wscript.Echo arrR(4)
    Wscript.Echo arrR(5)
    Wscript.Echo "The $ amount to ignore is .. "&arrR(6)
Loop

objTextFile.close
SET objFSO=nothing

hope that helps
0
 
fostejoCommented:
and ps...

I suspect you want to write out to a new file also? this should work, creating a file called "C:\testoutput.txt" which is stripped of the double quotes and $ amount at the end; in this case giving:

Doodles Caboodles ,877 EXECUTIVE CENTER DR W STE 300 ,STPETERSBURG ,FL,33702 ,7275766630
Doodles Caboodles ,877 EXECUTIVE CENTER DR W STE 300 ,STPETERSBURG ,FL,33702 ,7275766630
Doodles Caboodles ,1111 S 21ST ST ,Anyplace,FL,55555 ,4053084196


Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\test.txt", ForReading)
Set objOutputFile = objFSO.CreateTextFile("C:\testoutput.txt")

Do While objTextFile.AtEndOfStream <> True
    strL=objtextFile.Readline
    arrR=split(strL,",")
    for k=0 to 6
        arrR(k)=replace(arrR(k),chr(34),"")
    next

    arrR(6)=empty   ' the $ amount, set to empty so it'll be ignored by the Join

    strOut=Join(arrR,",")

    objOutputFile.Write left(strOut,len(strOut)-1) & vbCRLF  ' take off the extraneous comma added by the Join and add a CR + LF
Loop

objTextFile.close
objOutputFile.Close
SET objFSO=nothing

cheers,
0
 
WebccAuthor Commented:
Would you be able to add code to write the array to a comma-delimited file with a name such as UCS-DATE.TXT?  Which would plug in the current day's date into the name of the file.  Then it would be complete!

Thanks
0
 
fostejoCommented:
No problem.. just change:

Set objOutputFile = objFSO.CreateTextFile("C:\testoutput.txt")

to

strOutputFile="C:\UCS-"+Replace(FormatDateTime(Date()),"/","-")+".txt"    ' current date, replacing forward slashes with dashes..
Set objOutputFile = objFSO.CreateTextFile(strOutputFile)

cheers!
0
 
WebccAuthor Commented:
THANKS!!!!!!!!!!!!!!!!!!!!!!!!!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now