Submitted By: Austin Ledbetter

Removes commas that appear within double quotation marks in a CSV file.

Visual Basic
Edit|Remove
'Removing a character embedded in another set of a certain character.

'I wrote this because we did not have a good command line solution
'to remove commas that appear within double quotes in a CSV file.
'If you run it without command line parameters, it will show a help dialogue
'This program demonstrates error handling (in places), function use, 
'use of the Split command, use of command line arguments, and a 
'crude handling of the double quote character on the command line.

'Declare constants

Const ForWriting = 2
Const ForReading = 1
Const ForAppending = 8

Dim objFSO

Dim objInFile
Dim objOutFile
Dim objDeleteSched
Dim iSuccess
Dim objArgs

Dim Splitter
Dim Encapsulator
Dim sInfile
Dim sOutfile
Dim sDeletePath

sInfile = ""
sOutfile = ""
sDeletePath = ""
Splitter = 0
Encapsulator = 0


Set objArgs = WScript.Arguments

If ArgumentsWerePassedCorrectly(objArgs) Then
    sInfile = objArgs(0)
    WScript.Echo "Successfully read "&sInfile
    sOutfile = objArgs(1)
    WScript.Echo "Successfully read "&sOutfile
    Splitter = GetCharacter(objArgs(2))
    If Splitter <> -1 Then WScript.Echo "Successfully read "&chr(Splitter)
    Encapsulator = GetCharacter(objArgs(3))
    If Encapsulator <> -1 Then WScript.Echo "Successflly read "&chr(Encapsulator)
    sDeletePath = objArgs(4)
    WScript.Echo "Successfully read "&sDeletePath

    If Splitter <> -1 and Encapsulator <> -1 Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        WScript.Echo "Opening "&sInFile
        Set objInFile = objFSO.OpenTextFile(sInfile, ForReading)
        WScript.Echo "Creating "&sOutfile
        Set objOutFile = objFSO.CreateTextFile(sOutfile, ForWriting)
        WScript.Echo "Creating "&sDeletePath
        Set objDeleteSched = objFSO.CreateTextFile(sDeletepath, ForWriting)

        WScript.Echo "Processing "&sInFile
        iSuccess = ProcessFile(objInFile, objOutFile, chr(Splitter), chr(Encapsulator))
        
        If iSuccess <> 1 Then 
            WScript.Echo "There was an issue that has caused the script to error out."
        Else
            Set objDeleteSched = objFSO.GetFile(sDeletePath)
            objDeleteSched.Delete
        End If
        objInFile.Close
        objOutFile.Close
    End If
Else
    Wscript.Echo "In order to use this script, the user must provide " _
       &"4 parameters in this order: "&VbCrLf _
       &"1.  <inFilePath> - The input file " &VbCrLf _
       &"2.  <outFilePath> - The output file "&VbCrLf _
       &"3.  <sBadCharacter> - The character to remove"&VbCrLf _
       &"4.  <sEncapsulator> - The character surrounding"&VbCrlf _
       &vbTab&"the character to remove"&VbCrlf _
       &"5.  <sDeletePath> - Usually C:\Sched\Running.On"&VbCrLf _
       &"If the file is in another directory, please specify "&VbCrlf _
       &"the full file path."&_
        VbCrLf&VbCrlf&"If a character is a Double Quote ("&_
        Chr(34)&"), "&VbCrlf&_
        "Please enter the word -> doublequote."
End If

Function GetCharacter(sTemp)
On Error Resume Next
    If LCase(sTemp) = "doublequote" Then
        GetCharacter = 34
    Else
        GetCharacter = Asc(sTemp)
        If Err.Number <> 0 Then
            WScript.Echo _
            "Could not read the character. If a character is a Double Quote ("&_
        Chr(34)&"), "&VbCrlf&_
        "Please enter the word -> doublequote"
        GetCharacter = -1
        End If
    End If
    
End Function

Function ArgumentsWerePassedCorrectly(objArgs)
    Dim bGood
    bGood = True
    If (objArgs.Count <> 5) Then
        bGood = False        
    End If
    ArgumentsWerePassedCorrectly = bGood
End Function

Function CountOccurrences(sCurString, Encapsulator)
    CountOccurrences=UBound(Split(sCurString, Encapsulator)) 
End Function

Function IsClean(sCurString, Encapsulator)
    Dim bClean
    'Wscript.Echo "Checking to see if "&sCurString&" is clean"
    bClean = (inStr(sCurString, Encapsulator) = 0) Or _
        ((Left(Trim(sCurString),1) = Encapsulator) _
        And (Right(Trim(sCurString),1) = Encapsulator) _
        And (CountOccurrences(sCurString, Encapsulator) = 2))
    IsClean = bClean
End Function

Function ProcessFile(objInFile, objOutFile, Splitter, Encapsulator)
    Dim intLineNumber
    Dim intQuote
    Dim sBuildString
    Dim sNewLine
    Dim sCurString
    Dim sInLine
    Dim arrStrings
    intLineNumber = 0
    Do While objInFile.AtEndOfStream <> True
        intLineNumber = 1 + intLineNumber
        sBuildString = ""
        sNewLine = ""
        sInLine = objInFile.ReadLine
        'wscript.Echo "Processing Line"&sInLine&VbCrLf
        If inStr(sInLine, Splitter) Then
            intQuote = 0
            arrStrings = split(sInLine, Splitter)
            For i = LBound(arrStrings) To UBound(arrStrings)
                sCurString = arrStrings(i)
                'Wscript.Echo "Processing String "&sCurString&VbCrLf
                If intQuote = 0 Then
                    If IsClean(sCurString, Encapsulator) Then
                        If sNewLine <> "" Then sNewLine = sNewLine&Splitter
                        sNewLine = sNewLine&sCurString
                    Else
                        intQuote = intQuote + CountOccurrences(sCurString, Encapsulator)
                        sBuildString = sBuildString&sCurString
                    End If
                ElseIf intQuote > 0  Then
                    intQuote = intQuote - CountOccurrences(sCurString, Encapsulator)
                    sBuildString = sBuildString&sCurString
                    If intQuote = 0 Then
                        If sNewLine <> "" Then sNewLine = sNewLine&Splitter
                        sNewLine = sNewLine&sBuildString
                    End If
                End If
                If intQuote < 0 Then
                    Wscript.Echo "Error processing Line "&intLineNumber
                    Wscript.Echo VbCrlf&InLine
                    Wscript.Echo VbCrlf&"Please see if there is an unmatched encapsulator in the line"
                    ProcessFile = 0
                    Exit Function
                End If
            Next
            'Wscript.Echo "Writing Line "&sNewLine&VbCrLf
            objOutFile.WriteLine sNewLine
        End If
    Loop
    ProcessFile = 1
End Function