-
May 27th, 2011, 03:09 PM
#1
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!
-
May 28th, 2011, 12:28 PM
#2
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.
-
May 31st, 2011, 08:28 AM
#3
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
-
June 2nd, 2011, 08:02 AM
#4
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.
-
June 2nd, 2011, 08:55 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|