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!

Why don't you just use a spreadsheet editor?

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

---

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.

You're welcome - glad to help.