CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Hybrid View

  1. #1
    Join Date
    May 2011
    Posts
    2

    vbscript to delete columns from csv

    Hi, I'm new to the forum and have a question about looping through rows to check for values at specific columns.

    I have a csv file with 20 some columns and hundreds of rows. Basically, what I want to do is delete all the rows where column 12 is 1 or column 15 is a 1. I've seen many examples of how to delete some arbitrary row or search for some arbitrary string but nothing about checking for a string at a specific spot (like "1" in column 12) and then deleting that row. What I'd really like to know is how to reference a specific column - something like "elementAt(12)" - so I can create conditionals.

    Any help would be appreciated.

    Also, I'd like this to be done in a new CSV file. Thanks!

  2. #2
    Join Date
    May 2002
    Posts
    10,943

    Re: vbscript to delete columns from csv

    Why don't you just use a spreadsheet editor?
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    Jun 2009
    Posts
    113

    Re: vbscript to delete columns from csv

    You've got various methods to do this. Parse the text file reading a line at a time, use the Split method to make an array of each comma separated element, then do your comparison. Like this:

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Const strFilePath = "C:\Scripts\input.csv"
    Const ForWriting = 2
    Dim strNew 
    Set objTS = objFSO.OpenTextFile(strFilePath)
    Do Until objTS.AtEndOfStream
    	strLine = objTS.ReadLine
    	bolWrite = True
    	If InStr(strLine,",")>0 Then
    		arrLine = Split(strLine,",")
    		If Ubound(arrLine)>=15 Then
    			If arrLine(11)="1" Or arrLine(14)=1 Then
    				bolWrite=False
    			End If
    		End If
    	End If
    	If bolWrite Then
    		strNew = strNew & strLine & VBCrLf
    	End If
    Loop
    objTS.Close
    Set objTS = objFSO.OpenTextFile(strFilePath, ForWriting,True)
    objTS.Write strNew
    objTS.Close
    Or if you have Excel installed you can programmatically call it to do the same comparison, but that can be quite slow. Another method you might want to investigate is using ADO to filter the CSV, however I haven't had any luck using the Delete method to write the recordset back to file and ended up using GetString instead and writing it back using the last 3 lines of the script above. ADO is fast though; there's more information about it here:
    http://msdn.microsoft.com/en-us/library/ms974559.aspx

    For what it's worth, here's my script that I was playing with. It assumes your CSV file has a header row and the ones you're interested in are called "Col12" and "Col15":

    Code:
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdText = &H0001
    Const ForWriting = 2 
    Const strFilePath = "C:\Scripts\"
    Const strFileName = "input.csv"
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & strFilePath & ";Extended Properties=""text;HDR=YES;FMT=Delimited"""
    objRecordSet.Filter = "Col12<>'1' AND Col15<>'1'"
    objRecordSet.Open "SELECT * FROM " & strFileName, objConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
    strNew=objRecordSet.GetString(2,,",",VBCrLf)
    objRecordSet.Close
    
    Set objTS = objFSO.OpenTextFile(strFilePath & "\" & strFileName, ForWriting,True)
    objTS.Write strNew
    objTS.Close
    Last edited by PeejAvery; May 31st, 2011 at 10:28 AM. Reason: Added code tags

  4. #4
    Join Date
    May 2011
    Posts
    2

    Re: vbscript to delete columns from csv

    Thanks, the_cat! That worked like a charm and it makes perfect sense now that I look at it and think about it.

    I can't use a spreadsheet editor as one member suggested because hundreds of these csv files are generated automatically every morning. So, I've written a script to loop through a daily-created directory and extract from a bunch of zip files the particular CSV files that are of interest. Then I've modified the_cat's code to keep only the parts I wanted and eventually it will write out to one CSV file (or maybe Excel workbook) with the specific fields compiled from all the CSVs.

  5. #5
    Join Date
    Jun 2009
    Posts
    113

    Re: vbscript to delete columns from csv

    You're welcome - glad to help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured