How to delete rows in a csv file?

Discussion:

(too old to reply)

D.P. Roberts

2006-09-26 16:42:16 UTC

I have a vbscript that appends data to a csv file on a daily basis. When new
data gets appended to the bottom rows of the file, I'd like the oldest data
in the top rows to be deleted. Does anyone know how I can delete the top 3
rows every time the file gets appended? Here's what I've got so far:

Set WshNetwork = WScript.CreateObject("WScript.Network")
Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("MyFile.csv", ForAppending, True)

<<<Here's where I want to delete rows 1-3 in the csv file>>>

outFile.writeline (Now & "," & DataValue)
outFile.close

Thanks for any help on this...

Nigel

2006-09-26 16:50:12 UTC

Permalink

If it is the top three rows in the Excel worksheet after you have appended
new data then use.....

Rows("1:3").Entirerow.delete

But I suspect that may not be a full answer?
--
Cheers
Nigel

Post by D.P. Roberts
I have a vbscript that appends data to a csv file on a daily basis. When
new data gets appended to the bottom rows of the file, I'd like the oldest
data in the top rows to be deleted. Does anyone know how I can delete the
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("MyFile.csv", ForAppending, True)
<<<Here's where I want to delete rows 1-3 in the csv file>>>
outFile.writeline (Now & "," & DataValue)
outFile.close
Thanks for any help on this...

Joe Reynolds

2006-09-26 16:55:42 UTC

Permalink

you cant "delete" rows from a text file. youll have to open a new, temp
file, read the original file, write the lines to the temp file (skipping the
ones you dont want) then delete the old, rename the new

"Crash" Dummy

2006-09-26 17:31:03 UTC

Permalink

Unfortunately, you can not read/write with random access to a file from
VBScript. You can, however, edit the contents of one file while copying it to a
second file, or while the complete file is in memory. As usual, there are
several ways to do it.How exactly you do it depends on how big the file is and
on personal preference. Here is how I might do it, using the example code you
posted:

'your code
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("MyFile.csv", ForAppending, True)
outFile.writeline (Now & "," & DataValue)
outFile.close

'added code
Set inFile= fsoOut.OpenTextFile("MyFile.csv", ForReading, True)
Set tempFile=fso.CreateTextFile("MyFile.tmp")

for n=0 to 2:inFile.skipLine:next

do until inFile.AtEndOfStream
line=inFile.readLine
tempFile.writeLine line
loop

inFile.close
tempFile.close

fsoOut.deleteFile "MyFile.csv"
fsoOut.moveFile "MyFile.tmp", "MyFile.csv"

--
Crash

D.P. Roberts

2006-09-26 18:30:54 UTC

Permalink

I got it working using Crash's approach. Thanks!

Post by "Crash" Dummy

Unfortunately, you can not read/write with random access to a file from
VBScript. You can, however, edit the contents of one file while copying it to a
second file, or while the complete file is in memory. As usual, there are
several ways to do it.How exactly you do it depends on how big the file is and
on personal preference. Here is how I might do it, using the example code you
'your code
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("MyFile.csv", ForAppending, True)
outFile.writeline (Now & "," & DataValue)
outFile.close
'added code
Set inFile= fsoOut.OpenTextFile("MyFile.csv", ForReading, True)
Set tempFile=fso.CreateTextFile("MyFile.tmp")
for n=0 to 2:inFile.skipLine:next
do until inFile.AtEndOfStream
line=inFile.readLine
tempFile.writeLine line
loop
inFile.close
tempFile.close
fsoOut.deleteFile "MyFile.csv"
fsoOut.moveFile "MyFile.tmp", "MyFile.csv"
--
Crash

D.P. Roberts

2006-09-26 17:47:49 UTC

Permalink

Why can't I simply open the csv files as an Excel application, delete the
rows, close the file, then open it again as a text file for appending?

I tried this but I think the syntax is wrong:

Dim XL, XLBook
Set XL = CreateObject("Excel.application")
Set XLBook = XL.Workbooks.Open("MyFile.csv")
Rows("1:3").Entirerow.delete <<<Type mismatch error for "Rows" happens
on this line

Joe Reynolds

2006-09-26 17:56:50 UTC

Permalink

thats a different story.

did you try XLBook.Rows?

i dont think "Rows" means anything to anyone

Post by D.P. Roberts
Why can't I simply open the csv files as an Excel application, delete the
rows, close the file, then open it again as a text file for appending?
Dim XL, XLBook
Set XL = CreateObject("Excel.application")
Set XLBook = XL.Workbooks.Open("MyFile.csv")
Rows("1:3").Entirerow.delete <<<Type mismatch error for "Rows" happens
on this line

D.P. Roberts

2006-09-26 18:30:08 UTC

Permalink

Yes, I treid XLBook.Rows with no luck.

Post by Joe Reynolds
thats a different story.
did you try XLBook.Rows?
i dont think "Rows" means anything to anyone

Post by D.P. Roberts
I have a vbscript that appends data to a csv file on a daily basis. When
new data gets appended to the bottom rows of the file, I'd like the
oldest data in the top rows to be deleted. Does anyone know how I can
delete the top 3 rows every time the file gets appended? Here's what I've
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("MyFile.csv", ForAppending, True)
<<<Here's where I want to delete rows 1-3 in the csv file>>>
outFile.writeline (Now & "," & DataValue)
outFile.close
Thanks for any help on this...

Joe Reynolds

2006-09-26 17:57:50 UTC

Permalink

and while youre at it, why bother appending as a text file? just use the
excel object to append to it while its open