PDA

View Full Version : Remove string in text files



swaggerbox
05-25-2016, 12:03 AM
In drive D:\MyReport I have several text files formatted this way (see below). I would like to remove the date sent information (see example below 20160504, 20160523) located on the left-most side of the text file.

INVENTORY LOG FOR 25 May 2016
=============================================

20160504\XXX\BM201716161A\IMAGES\BM201716161A.PDF
20160504\XXX\BM201716161A\IMAGES\BM201716161A.TIF
20160523\XXX\BM201723252A\IMAGES\BM201723252A.PDF
20160523\XXX\BM201723252A\IMAGES\BM201723252A.TIF

Total Files : 4
=============================================

How do I programmatically remove the date sent data in each of these text files? See revised LOG below:

INVENTORY LOG FOR 25 May 2016
=============================================

XXX\BM201716161A\IMAGES\BM201716161A.PDF
XXX\BM201716161A\IMAGES\BM201716161A.TIF
XXX\BM201723252A\IMAGES\BM201723252A.PDF
XXX\BM201723252A\IMAGES\BM201723252A.TIF

Total Files : 4
=============================================

Tom Jones
05-25-2016, 01:27 AM
Hi,

Try in B1:


=RIGHT(A1,LEN(A1)-FIND("\",A1))

Then drag down

swaggerbox
05-25-2016, 01:35 AM
Hi Tom,
Thanks for your help but the content is stored in the text files in D:\MyReport folder.

swaggerbox
05-25-2016, 03:47 AM
anyone?

p45cal
05-25-2016, 04:00 AM
One of many ways, try:
Sub TextFile_FindReplace()
Dim TextFile As Integer, FilePath As String, FileContent As String, x As String, m, j, p
FilePath = "D:\MyReport" '<<<adjust
x = Dir(FilePath & "\*.txt")
Do
TextFile = FreeFile
Open FilePath & "\" & x For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
Close TextFile
m = Split(FileContent, vbLf)
For j = 0 To UBound(m)
If InStr(m(j), "\") > 0 Then
p = Split(m(j), "\")
m(j) = Join(Application.Index(p, Application.Transpose(Application.Evaluate("row(2:" & UBound(p) + 1 & ")"))), "\")
End If
Next j
TextFile = FreeFile
Open FilePath & "\" & x For Output As TextFile
Print #TextFile, Join(m, vbLf)
Close TextFile
x = Dir
Loop Until x = ""
End Sub
Run it only once, repeating it will progressively remove the first part of each line up to and including the first backslash. It overwrites existing files.
There are 2 instances of vbLf which might need a tweak depending on the actual content of your .txt files.
snb here would probably produce a one- or two-liner

swaggerbox
05-25-2016, 04:42 AM
This is just great p45cal. You're a genius!!! Thanks a lot