No announcement yet.

Vbscript to delete columns

  • Filter
  • Time
  • Show
Clear All
new posts

  • Vbscript to delete columns

    Hi does anyone know how to delete columns in a csv file using vbscript?

    Thanks in advance.

  • #2
    Re: Vbscript to delete columns

    with or without using excel?

    like a script to take whatever.csv and delete say, the 3rd column and all the cells contents?

    what are the contents of the .csv? is it money or names or what? does it import into excel correctly, i.e. all values are correctly delimited?

    its easier to beg forgiveness than ask permission.
    Give karma where karma is due...


    • #3
      Re: Vbscript to delete columns

      Const FOR_READING   = 1
      Const FOR_WRITING   = 2
      Const FOR_APPENDING = 8
      dt = date() : dt = Year(dt)*1e4 + Month(dt)*1e2 + Day(dt)
      strInputFile  = "c:\test.csv"     'path
      strOutputFile = "c:\" & dt & " test.csv"
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      If objFSO.FileExists(strInputFile) Then
        Set objInputFile = objFSO.GetFile(strInputFile)
      ' Read input-file into one string [ -> strInput]
        If objInputFile.Size > 0 Then
          Set objInputFile = objFSO.OpenTextFile(strInputFile, FOR_READING)
          strInput = objInputFile.ReadAll
        End If
      End If
      If Not strInput = "" Then
       ' If outputfile exist, Erase its present content
        If objFSO.FileExists(strOutputFile) Then
          Set objInputFile = objFSO.OpenTextFile(strOutputFile, FOR_WRITING, False)
        End If
       ' Modify each row and write back to the csv-file
        Set objInputFile = objFSO.OpenTextFile(strOutputFile, FOR_APPENDING, True)
        ' Breaking Input data (strInput) into array at line breaks
         arrRows = Split(strInput, VbCrLf)
        ' Now breaking each Row (in arrRows) into array at commas
         For Each strRow In arrRows 
            arrParams = Split(strRow, ",")
       ' Select the fields (columns) from the output csv-file. 
       ' With this method  you select columns and add them to an array per line. 
       ' you can change the order of apearance of the new columns. 
           NewColumn1 = arrParams((2)-1)   '<= csv column 2
           NewColumn2 = arrParams((4)-1)   '<= csv column 4
           ' Add the selected values per row to a string
           strData = NewColumn1 &  "," & NewColumn2
           ' Write row to csv-file
      End If
      wscript.echo "done"
      Note, this script expect all commas in the inputfile are seperators.
      So the csv-file must not contain quoted strings that have a comma within, as being a part of the value.


      This posting is provided "AS IS" with no warranties, and confers no rights.


      ** Remember to give credit where credit's due **
      and leave Reputation Points for meaningful posts