deleting cetain lines on a file

dtaylor · Mar 29th, 2002, 09:53 AM

Hello ALL,
I have a need for a macro that will open up all text files (with extension .csv) in a directory, find lines that have a cell entry of **** Header Line ****
and then delete those lines, close and save the files. There is only one column containing the string, col "J". The number of lines with a cell containing this string will vary as will the number of files.
There are 52 diff columns on each file and each are in the same format.

Any assistance would be gratefully recieved!

Thanks and have a great day....

Dan

daleyman · Mar 29th, 2002, 06:03 PM

hope this does it for you... will search all files as prescribed and strip any whole lines that contain the search text anywhere in that line...

Sub strip_files()

search_dir = "c:"
file_type = ".csv"
find_string = "*** Header Line ***"

filesfound = 0

Dim filefound As String
filefound = Dir(search_dir & "*" & file_type)
If filefound = "" Then GoTo end_files
nextfile:
filesfound = filesfound + 1
file1 = search_dir & filefound
file2 = Left(file1, Len(file1) - (Len(file_type) - 1)) & "tmpx"
If Dir(file2) <> "" Then Kill file2
Open file1 For Input As #1
Open file2 For Output As #2
Do Until EOF(1)
Line Input #1, strg
If InStr(1, strg, find_string) = 0 Then Print #2, strg
Loop
Close #1
Close #2
Kill file1

filefound = Dir(search_dir & "*" & file_type)
If filefound <> "" Then GoTo nextfile

end_files:
filefound = Dir(search_dir & "*.tmpx")
Do Until filefound = ""
file1 = search_dir & filefound
file2 = Left(file1, Len(file1) - 5) & file_type
Name file1 As file2
filefound = Dir(search_dir & "*.tmpx")
Loop

MsgBox filesfound & " files processed."
End Sub

daleyman · Mar 29th, 2002, 06:05 PM

ps: configure the first few lines, the first of which should read

search_dir = "c:"

daleyman · Mar 29th, 2002, 06:06 PM

darn, it did it again, what is wrong with this thing! ha ha

should only be on forward slash in the directory name specified.

daleyman · Mar 29th, 2002, 06:09 PM

there is definately something wrong with this site, mail me if you want the code as i submitted it (if it doesn't work from copy & pasting) daley_123@hotmail.com

Ivan F Moala · Mar 29th, 2002, 06:21 PM

Quote:

On 2002-03-29 17:09, daleyman wrote:
there is definately something wrong with this site, mail me if you want the code as i submitted it (if it doesn't work from copy & pasting) daley_123@hotmail.com

Yes there is the [] double up...known prob
This is getting looked into.....please bere
with us until it gets a fix....

Regards

Ivan

daleyman · Mar 29th, 2002, 06:33 PM

no problem, thanks.

dtaylor · Apr 1st, 2002, 09:33 AM

wow thanks for all the interest. i am sorry it took so long for my reply but i was out of town. i will, now that i am back at work uugh!, give this a go. do appreciate the input. you don't know how much work you have saved more than one person. have a good day.

dan

dtaylor · Apr 1st, 2002, 10:15 AM

success my friend...

Dan

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 09:53 AM	#1
dtaylor Board Regular Join Date: Mar 2002 Location: So Cal Posts: 380	Hello ALL, I have a need for a macro that will open up all text files (with extension .csv) in a directory, find lines that have a cell entry of ** Header Line ** and then delete those lines, close and save the files. There is only one column containing the string, col "J". The number of lines with a cell containing this string will vary as will the number of files. There are 52 diff columns on each file and each are in the same format. Any assistance would be gratefully recieved! Thanks and have a great day.... Dan

Mar 29th, 2002, 06:03 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	hope this does it for you... will search all files as prescribed and strip any whole lines that contain the search text anywhere in that line... Sub strip_files() search_dir = "c:" file_type = ".csv" find_string = "* Header Line " filesfound = 0 Dim filefound As String filefound = Dir(search_dir & "" & file_type) If filefound = "" Then GoTo end_files nextfile: filesfound = filesfound + 1 file1 = search_dir & filefound file2 = Left(file1, Len(file1) - (Len(file_type) - 1)) & "tmpx" If Dir(file2) <> "" Then Kill file2 Open file1 For Input As #1 Open file2 For Output As #2 Do Until EOF(1) Line Input #1, strg If InStr(1, strg, find_string) = 0 Then Print #2, strg Loop Close #1 Close #2 Kill file1 filefound = Dir(search_dir & "" & file_type) If filefound <> "" Then GoTo nextfile end_files: filefound = Dir(search_dir & ".tmpx") Do Until filefound = "" file1 = search_dir & filefound file2 = Left(file1, Len(file1) - 5) & file_type Name file1 As file2 filefound = Dir(search_dir & "*.tmpx") Loop MsgBox filesfound & " files processed." End Sub

Mar 29th, 2002, 06:05 PM	#3
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	ps: configure the first few lines, the first of which should read search_dir = "c:"

Mar 29th, 2002, 06:06 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	darn, it did it again, what is wrong with this thing! ha ha should only be on forward slash in the directory name specified.

Mar 29th, 2002, 06:09 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	there is definately something wrong with this site, mail me if you want the code as i submitted it (if it doesn't work from copy & pasting) daley_123@hotmail.com

Mar 29th, 2002, 06:33 PM	#7
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	no problem, thanks.

Apr 1st, 2002, 09:33 AM	#8
dtaylor Board Regular Join Date: Mar 2002 Location: So Cal Posts: 380	wow thanks for all the interest. i am sorry it took so long for my reply but i was out of town. i will, now that i am back at work uugh!, give this a go. do appreciate the input. you don't know how much work you have saved more than one person. have a good day. dan

Apr 1st, 2002, 10:15 AM	#9
dtaylor Board Regular Join Date: Mar 2002 Location: So Cal Posts: 380	success my friend... Dan