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!
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:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const strFilePath = "C:\Scripts\input.csv"
Const ForWriting = 2
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
If bolWrite Then
strNew = strNew & strLine & VBCrLf
Set objTS = objFSO.OpenTextFile(strFilePath, ForWriting,True)
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":
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.