Solved

Problem with VB Script to convert comma to Pipe delimited

Posted on 2011-03-08
2
594 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 500 total points
Comment Utility
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
Comment Utility
Fantastic!  Thanks.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now