VBScript to remove certain characters in CSV file

kangiser
kangiser used Ask the Experts™
on
I have a CSV file that has 2 columns (13 and 15) with dollar amounts in them. Columns 13 and 15 may or may not be put in double quotes depending on if the amount is over $999.99. I need a VB script to standardize these two columns so that the amount does not include the dollar (comma) separator, any spaces (leading or trailing) and no quotes.

Here is what the file would look like before and after running the script.


Head1,Head2,Head3,Head4,Head5,Head6,Head7,Head8,Head9,Head10,Head11,Head12,Head13,Head14,Head15
actions,whatever,1/4/2010,1/26/2010,Cat food,145749,2,0, $-   ,0, $-   ,4," $2,046.19 ",4," $2,046.19 "
actions,whatever,1/4/2010,1/26/2010,Dog food,147324,1,0, $-   ,0, $-   ,1, $500.00 ,1, $500.00
actions,whatever,1/4/2010,1/26/2010,Fish food,52313,1,0, $-   ,0, $-   ,1," $2,325.00 ",1," $2,325.00 "

*** AFTER *****
Head1,Head2,Head3,Head4,Head5,Head6,Head7,Head8,Head9,Head10,Head11,Head12,Head13,Head14,Head15
actions,whatever,1/4/2010,1/26/2010,Cat food,145749,2,0, $-   ,0, $-   ,4,2046.19,4,2046.19
actions,whatever,1/4/2010,1/26/2010,Dog food,147324,1,0, $-   ,0, $-   ,1,500.00,1,500.00
actions,whatever,1/4/2010,1/26/2010,Fish food,52313,1,0, $-   ,0, $-   ,1,2325.00,1,2325.00

Thanks in advance,

Marty
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello kangiser,

The following VBScript appears to be doing what you want.





Dim fso, tsIn, tsOut, TheLine, RegX

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\Before.csv")
Set tsOut = fso.CreateTextFile("c:\After.csv", True)
Set RegX = New RegExp

With RegX
      .Global = True
      .IgnoreCase = True
      .Pattern = "(""? *\$)(\d{1,3})?(,)?(\d{1,3})?(,)?(\d{1,3})?(,)?(\d{1,3})(\.\d+)?( *""?)"
End With

Do Until tsIn.AtEndOfStream
      TheLine = tsIn.ReadLine
      TheLine = RegX.Replace(TheLine, "$2$4$6$8$9")
      tsOut.WriteLine TheLine
Loop

tsIn.Close
tsOut.Close
Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing
Set RegX = Nothing






Regards,

Patrick
Top Expert 2010

Commented:
kangiser,

That will take care of amounts in the billions of dollars :)

Patrick
Top Expert 2010
Commented:
Actually, this might be safer:



Dim fso, tsIn, tsOut, TheLine, RegX

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\Before.csv")
Set tsOut = fso.CreateTextFile("c:\After.csv", True)
Set RegX = New RegExp

With RegX
      .Global = True
      .IgnoreCase = True
      .Pattern = ",(""? *\$)(\d{1,3})?(,)?(\d{1,3})?(,)?(\d{1,3})?(,)?(\d{1,3})(\.\d+)?( *""?)(?=,|$)"
End With

Do Until tsIn.AtEndOfStream
      TheLine = tsIn.ReadLine
      TheLine = RegX.Replace(TheLine, ",$2$4$6$8$9")
      tsOut.WriteLine TheLine
Loop

tsIn.Close
tsOut.Close
Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing
Set RegX = Nothing
HainKurtSr. System Analyst

Commented:
do you have any code?

download this manual to see al functions that you may need:

http://www.microsoft.com/downloads/details.aspx?FamilyId=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en
open input text file, loop line by line, process it
open output text file, write processed lines
while not eof (file)
  l = readline(file)
  l = replace(l, """","")
  cur1s = instr(l,"$")
  cur1e = instr(cur1s+1,".")
  cur2s = instr(cur1e+3, l, "$")
  cur2e = instr(cur2s+1, l, ".")
  nl = left(l,cur1s) & replace(mid(l, cur1s+1, cur1e-cur1s-1),",","")
  nl = nl & mid(l, cur1e +1, cur2s -cur1e -1) & replace(mid(l, cur2s+1, cur2e-cur2s-1),",","")
  nl = nl & mid(l, cur2e, len(l)) ' the rest
  newfile.writteln(nl)
wend  

Open in new window

Author

Commented:
Thanks matthewspatrick, The second script worked fine and I will have to take your word on the second script being safer.

Thanks HainKurt but I’m really new to scripting and yours was a little harder for me to follow.

Marty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial