Using VBScript to delete a column from CSV file

Question

I need to use VBScript to delete various columns from CSV file.

The columns to be eliminated are from number 101 to number 106.

My code below it does not delete any columns:

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, strLine, dataArray, clippedArray()

InputFile="C:\input.csv"
OutputFile="C:\input_n_1.csv"

Set fso = CreateObject("Scripting.FileSystemObject")

Set InFile = fso.OpenTextFile(InputFile, ForReading)
Set OutFile = fso.OpenTextFile(OutputFile, ForWriting, True)

Do While InFile.AtEndOfStream <> True

    strLine = InFile.ReadLine
    ReDim Preserve clippedArray(x)
    clippedArray(x) =  Split(strLine,";")

    intCount = 0
    newLine = ""

    For Each Element In clippedArray(x)    
        If intCount <> (101 OR 102 OR 103 OR 104 OR 105 OR 106) Then
           EndChar = "|"
           newLine = newLine & Element & EndChar
        End If
        intCount = intCount + 1 
    Next

    OutFile.WriteLine newLine

Loop

InFile.Close
OutFile.Close

WScript.Echo "Done"

Étienne Laneville · Accepted Answer · 2021-02-17 22:41:28Z

The code inside your loop has a few issues. For example, no value is specified for x when trying to ReDim your clippedArray array. There would be no need to Preserve what's in the array either since you're placing new data in it.

The interior of the loop can be simplified and put into a function like this:

Function GetUpdatedLine(p_sLine)
    Dim arrColumns
    Dim sNewLine
    Dim sEndChar
    Dim iCounter
    
    ' Split line into columns
    arrColumns = Split(p_sLine, ";")

    ' Initialize variables
    sNewLine = ""
    sEndChar = "|"

    For iCounter = 1 To UBound(arrColumns) + 1
    
        Select Case iCounter
            
            Case 101, 102, 103, 104, 105, 106
                ' Skip these columns
                
            Case Else
                ' Add to new line
                If sNewLine <> "" Then sNewLine = sNewLine & sEndChar ' Add separator
                sNewLine = sNewLine & arrColumns(iCounter - 1) ' arrColumns is a zero-based array
        End Select
    
    Next

    GetUpdatedLine = sNewLine

End Function

You loop can now be updated to this:

Do While InFile.AtEndOfStream <> True
    OutFile.WriteLine GetUpdatedLine(InFile.ReadLine)
Loop

Collectives™ on Stack Overflow

Using VBScript to delete a column from CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related