Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
Solved
Problem with VB Script to convert comma to Pipe delimited
Posted on 2011-03-08
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.
SL.Account.Export.txt
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
SL.Account.Export.txt
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2 Comments
Active 4 days ago
Hi, try this.
Regards,
Rob.
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script. Many of these have come from the web or adaptations from snippets I find on the Web. Periodically I add to them when I come…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month5 days, 22 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.