Results 1 to 6 of 6

Thread: VBS removing entire column in .csv file

  1. #1
    Join Date
    Feb 2009
    Posts
    20

    VBS removing entire column in .csv file

    Hi Guys,

    I was wonder does anyone know.vbs scripting that can remove the entire column from .csv file? Example: entire column A is to remove

  2. #2
    Join Date
    Jan 2008
    Posts
    1,521

    Re: VBS removing entire column in .csv file

    The following VBScript will delete FIELDS (example 10 and 3) within the CSV file C:\TEST.csv


    'On Error Resume Next
    Dim objFSO, dataArray, clippedArray()
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Create an array out of the CSV
    'open the data file
    Set oTextStream = objFSO.OpenTextFile("C:\test.csv")
    Set newFile = objFSO.CreateTextFile("C:\newCSV.csv")
    'make an array from the data file
    dataArray = Split(oTextStream.ReadAll, vbNewLine)
    'close the data file
    oTextStream.Close

    x = 0
    For Each strLine In dataArray
    'Now make an array from each line
    ReDim Preserve clippedArray(x)
    clippedArray(x) = Split(strLine,",")

    ' Delete Field 10 and 3 (highest to lowest)
    CutColumn = 10
    CutColumn = 3

    intCount = 0
    newLine = ""
    For Each Element In clippedArray(x)
    If intCount = UBound(clippedArray(x)) Then
    EndChar = vbCrLf
    Else
    EndChar = ","
    End If

    If intCount <> CutColumn -1 Then
    newLine = newLine & Element & EndChar
    End If
    intCount = intCount + 1
    If intCount = UBound(clippedArray(x))+1 Then
    newFile.Write newLine
    End If
    Next

    Next

  3. #3
    Join Date
    May 2008
    Posts
    2,680

    Re: VBS removing entire column in .csv file

    You can try this as well:-

    ' NAME: RemoveColumnFromCSV.vbs
    '
    ' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
    ' URL: http://www.thespidersparlor.com
    ' DATE :
    ' COPYRIGHT (c) 2007 All Rights Reserved
    '
    ' COMMENT:
    '
    ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
    ' ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
    ' THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    ' PARTICULAR PURPOSE.
    '
    ' IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS
    ' BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
    ' DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
    ' WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
    ' ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
    ' OF THIS CODE OR INFORMATION.
    '
    '==========================================================================
    'On Error Resume Next
    Dim objFSO, dataArray, clippedArray()
    Set objFSO = CreateObject("Scripting.FileSystemObject")


    'Create an array out of the CSV

    'open the data file
    Set oTextStream = objFSO.OpenTextFile("C:\Testcsv.csv")
    Set newFile = objFSO.CreateTextFile("C:\newCSV.txt")
    'make an array from the data file
    dataArray = Split(oTextStream.ReadAll, vbNewLine)
    'close the data file
    oTextStream.Close

    x = 0
    For Each strLine In dataArray
    'Now make an array from each line
    ReDim Preserve clippedArray(x)
    clippedArray(x) = Split(strLine,",")
    CutColumn = 3
    intCount = 0
    newLine = ""
    For Each Element In clippedArray(x)
    If intCount = UBound(clippedArray(x)) Then
    EndChar = vbCrLf
    Else
    EndChar = ","
    End If

    If intCount <> CutColumn Then
    newLine = newLine & Element & EndChar
    End If
    intCount = intCount + 1
    If intCount = UBound(clippedArray(x))+1 Then
    newFile.Write newLine
    End If
    Next

    Next
    WScript.Echo "Done"

  4. #4
    Join Date
    Feb 2009
    Posts
    20

    Re: VBS removing entire column in .csv file

    The code look like detecting with a comma "," then it will know is the next column. But what i want is to removing entire column A without using the comma to check the next column because in column A the data contain many comma and each row of column A contain different comma (some 5 comma some is 7 comma)

  5. #5
    Join Date
    Apr 2012
    Location
    Europe
    Posts
    7

    Re: VBS removing entire column in .csv file

    Hi guys. Thanks for scripts above.

    But is there any shorter way to delete for example column 3, 5,6 via VSB script ?

    Open the file, modify and save ?

    CZAS;DEPART;CLIENT;CLASSE;LOCATION;ERREURS;CODE ZNANY;MESSAGE;VALUE;TYP;CZAS;TAILLE(Kb);LALA;STREAMS;STORAGE;RETENTION
    2012-04-13;02:00:59;vbsscript;vbsscript_aix_rgsedi01;rgsedi01-bck.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00:08:48;2111360;759;6;suyesokdiane_dmx4_054;2 Sem
    2012-04-13;02:01:02;vbsscript;vbsscript_aix_rgsedi01_data;rgsedi01-bck.tlt;0;0;OK;vbsscript_cumu_2sem;yayative;00:06:35;128512;500;12;suyesokdiane_dmx4_054;2 Sem
    2012-04-13;04:00:13;vbsscript;vbsscript_aix_rgsedi01_gtf;rgsedi01-bck.tlt;0;0;OK;vbsscript_cumu_2sem;yayative;01:11:53;5832800;21054;1;suyesokdiane_dmx4_054;2 Sem
    2012-04-13;03:00:00;vbsscript;vbsscript_rhe_liabv8;liabv8.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00 :00:36;85920;148;1;suyesokdiane_dmx4_054;2 Sem
    2012-04-13;03:00:00;vbsscript;vbsscript_rhe_liabv9;liabv9.tlt;0;0;OK;vbsscriptlolek2sem;scriptérentielle;00 :01:06;477088;209;1;suyesokdiane_dmx4_054;2 Sem

  6. #6
    Join Date
    Apr 2012
    Location
    Europe
    Posts
    7

    Re: VBS removing entire column in .csv file

    I've modify this script for my purposes

    Code:
    Function OK()
    
    Const ForReading = 1
    Const ForWriting = 2
    
    'On Error Resume Next
    Dim objFSO, dataArray, clippedArray()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Create an array out of the CSV
    'open the data file
    Set oTextStream = objFSO.OpenTextFile("c:\CSV\2012-04-12.csv", ForReading)
    Set newFile = objFSO.CreateTextFile("c:\Text.csv", ForWriting)
    'make an array from the data file
    dataArray = Split(oTextStream.ReadAll, vbNewLine)
    'close the data file
    oTextStream.Close
    
    x = 0
    For Each strLine In dataArray
    'Now make an array from each line
    ReDim Preserve clippedArray(x)
    clippedArray(x) = Split(strLine,";")
    
    ' Delete Field 10 and 3 (highest to lowest)
    
    CutColumn = 3
    
    intCount = 0
    newLine = ""
    For Each Element In clippedArray(x)
    If intCount = UBound(clippedArray(x)) Then
    EndChar = vbCrLf
    Else
    EndChar = ";"
    End If
    
    If intCount <> CutColumn -1 Then
    newLine = newLine & Element & EndChar
    End If
    intCount = intCount + 1
    If intCount = UBound(clippedArray(x))+1 Then
    newFile.Write newLine
    End If
    Next
    
    Next 
    
    End Function
    But it's deleting only column 3
    How to add option to add also column 7 and 14 and 15 ?

Similar Threads

  1. Replies: 5
    Last Post: 21-02-2012, 07:13 PM
  2. Tor cache file removing
    By RangerGordx in forum Technology & Internet
    Replies: 1
    Last Post: 16-12-2011, 11:47 PM
  3. Removing Combofix.exe File!
    By Erie in forum Networking & Security
    Replies: 6
    Last Post: 13-07-2010, 09:28 AM
  4. Replies: 3
    Last Post: 09-12-2009, 01:11 PM
  5. mysql update column with another column
    By Gunter in forum Software Development
    Replies: 3
    Last Post: 23-05-2009, 09:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,704,200.96999 seconds with 17 queries