Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with VB Script to convert comma to Pipe delimited

Posted on 2011-03-08
2
Medium Priority
?
610 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:moore315
2 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 35073505
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
 

Author Closing Comment

by:moore315
ID: 35131401
Fantastic!  Thanks.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question