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
moore315Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
RobSampsonConnect With a Mentor Commented:
Hi, try this.

Regards,

Rob.
' 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)
' Write file with any changes made
Set oOutfile = oFSO.OpenTextFile(sOutfile, ForWriting, True)
While Not oInfile.AtEndOfStream
	sLine = oInfile.ReadLine
	If InStr(sLine, """,""") > 0 Then
		If Left(sLine, 1) = """" Then sLine = Mid(sLine, 2)
		If Right(sLine, 1) = """" Then sLine = Left(sLine, Len(sLine) - 1)
		aFields = Split(sLine, """,""")
		For intField = 0 To UBound(aFields)
			If intField = 0 Then
				oOutfile.Write aFields(intField)
			ElseIf intField < UBound(aFields) Then
				oOutfile.Write "|" & aFields(intField)
			Else
				oOutfile.WriteLine "|" & aFields(intField)
			End If
		Next
	End If
Wend
oInfile.Close
Set oInfile = Nothing
oOutfile.Close
Set oOutfile = Nothing

' Cleanup and end
Set oFSO = Nothing
Wscript.Quit

Open in new window

0
 
moore315Author Commented:
Fantastic!  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.