Link to home
Start Free TrialLog in
Avatar of moore315
moore315

asked on

Problem with VB Script to convert comma to Pipe delimited

Previously I received the attached code to convert a file from comma to pipe delimited.  However I recently realized that the script is also converting commas embedded within fields such that a street address of "100 Main Street, Suite 1A" gets changed to "100 Main Street| Suite 1A".

I need the code to change only those commas that are delimiters between fields, not the ones that are part of the data.

Sample input and out files are attached.
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  sInfile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  sOutfile = WScript.Arguments(1)
Else
  sOutfile = sInfile
End If

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Read entire input file into a variable and close it
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)
sData = oInfile.ReadAll
oInfile.Close
Set oInfile = Nothing

' Replace commas (,) with pipes (|)
sData = Replace(sData, ",", "|")

' Write file with any changes made
Set oOutfile = oFSO.OpenTextFile(sOutfile, ForWriting, True)
oOutfile.Write(sData)
oOutfile.Close
Set oOutfile = Nothing

' Cleanup and end
Set oFSO = Nothing
Wscript.Quit

Open in new window

SL.Account.Export.csv
SL.Account.Export.txt
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of moore315
moore315

ASKER

Fantastic!  Thanks.