• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1655
  • Last Modified:

Deleting Rows in CSV from VBScript

I'm using this VBS in automation to open an Excel file, save it as a CSV, and then open the CSV and delete rows that don't have today's date.  I can't get the row deletion part to work, cause I suck at VBS.  See the bottom of the code where I tagged it.
option explicit
 
dim a, arg, oArgs, ArgNum
a = 0
 
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
 
if ArgNum <> 1 then
   WSCript.echo "Syntax: cscript <Script> filename_root"
   WScript.quit(1)
end if
 
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
 
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
 oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
 
pos = instr(oldfilename, " ")
 
if pos > 0 then
 
 newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
 newname = replace(newname," ","_")
 filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
 
 dim oShell
 set oShell = Wscript.CreateObject("WScript.Shell")
 Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
 oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
 
else
 filename_root = oldfilename
end if
 
dim app
set app = createobject("Excel.Application")
   
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
 
const xlXMLSpreadsheet = 46
const xlCSV = 6
 
app.DisplayAlerts = false
dim sht
Dim sDate
Dim sMonth
Dim sDay
 
If Month(Now()) < 10 Then
sMonth = "0" & Month(Now())
Else
sMonth = Month(Now())
End If
 
If Day(Now()) < 10 Then
sDay = "0" & Day(Now())
Else
sDay = Day(Now())
End If
 
sDate = Year(Now()) & sMonth & sDay
 
for each sht in wb.worksheets
 sht.activate
 dim output_filename
 output_filename = filename_root & "_" & sDate & ".csv"
 wb.saveAs output_filename, xlCSV
next
app.DisplayAlerts = true
 
wb.close false
 
'### problematic code
dim csv
set csv = app.workbooks.open( filename_root & "_" & sDate & ".csv" )
 
Rng = csv.Count.Rows
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value < sDate Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next
Application.ScreenUpdating = True
 
dim csv_output_filename
csv_output_filename = filename_root & "_" & sDate & ".csv"
csv.saveAs output_filename, xlCSV   
csv.close false
'### somebody help
 
WScript.quit

Open in new window

0
judas2158
Asked:
judas2158
  • 4
  • 2
1 Solution
 
Mark PavlakCommented:
can you post a sample csv so I can get it to work
0
 
judas2158Author Commented:
Here is the Excel file.  It will get as far as converting it to a CSV (so you'll have the generated CSV file when you run it).  After that is the problem.
SNdata.xls
0
 
Mark PavlakCommented:
And you want all rows that do not have the current date(tdoays date) in the coulmn DATEENT to be removed.  Correct.  
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark PavlakCommented:
Add these lines and call the sub Usinng the FileName (full Path) to call it.  ie Clean_UP_CSV ("c:\temp\sample.csv")  it will save the file in the same dir only as sample_revised.csv
Sub Clean_UP_CSV (sFilename)
Dim oFso : Set oFso =CreateObject ("Scripting.FileSystemObject")
Const ForReading = 1
Dim oInfile : Set oInfile = oFso.OpenTextFile (sFilename,ForReading)
Dim oOutfile : oOutfile = oFso.CreateTextFile (Replace(sFilename,".csv","_Revised.csv"))
Dim sTemp
Do Until oInfile.AtEndOfStream
	sTemp = oInfile.ReadLine
If InStr (sTemp,BuildWritableDate) <> 0 Then
	oOutfile.WriteLine sTemp
Loop
End Sub
Function BuildWritableDate ()
Dim NewDate
NewDate = DatePart ("yyyy",Date)
If Len (DatePart ("M",Date)) < 2 Then
	NewDate = NewDate&"0"&DatePart ("M",Date)
Else
	NewDate = NewDate&DatePart ("M",Date)
End If 
If Len(DatePart ("d",Date)) < 2 Then
	NewDate = NewDate &"0"&DatePart ("d",Date)
Else
	NewDate = NewDate&DatePart ("d",Date)
End If
BuildWritableDate = NewDate
End Function

Open in new window

0
 
judas2158Author Commented:
Got a Loop without Do, put an End If before Loop; got a property not supported on oOutfile, had to put Set infront of it.  Made minor adjustments.  Works perfectly.  Thank you...  :)
'sDate defined elsewhere
Clean_UP_CSV (filename_root & ".csv")
 
Sub Clean_UP_CSV (sFilename)
Dim oFso : Set oFso =CreateObject ("Scripting.FileSystemObject")
Const ForReading = 1
Dim oInfile : Set oInfile = oFso.OpenTextFile (sFilename,ForReading)
Dim oOutfile : Set oOutfile = oFso.CreateTextFile (Replace(sFilename,".csv","_" & sDate & ".csv"))
Dim sTemp
Do Until oInfile.AtEndOfStream
	sTemp = oInfile.ReadLine
If InStr (sTemp,BuildWritableDate) <> 0 Then
	oOutfile.WriteLine sTemp
End If
Loop
End Sub
Function BuildWritableDate ()
Dim NewDate
NewDate = DatePart ("yyyy",Date)
If Len (DatePart ("M",Date)) < 2 Then
	NewDate = NewDate&"0"&DatePart ("M",Date)
Else
	NewDate = NewDate&DatePart ("M",Date)
End If 
If Len(DatePart ("d",Date)) < 2 Then
	NewDate = NewDate &"0"&DatePart ("d",Date)
Else
	NewDate = NewDate&DatePart ("d",Date)
End If
BuildWritableDate = NewDate
End Function

Open in new window

0
 
Mark PavlakCommented:
Your welcome.  Glad it worked pretty much first time I posted it quickly as I was going home for the day
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now