How to find a "word/string" in a text file if I don't use Cells of Excel

[alin09]

I try to use VBA to read and write a text file without using "Cells". How can I find a "word" in a text file and change this word and output another text file? For example, I want to find all "structural" in the following text file and change it to "nonstructural". Suppose that size of the file is too big to be imported into excel "Cells". Thanks a lot!

1000 temp abc 234 789 789
1000 temp rabc 234 789 673
1000 temp rabc 234 789 5653
1000 temp abc 234 789 343
......................
......................
2000 structural mechanical
3000 static analysis attach
200 mechanical dynamic
........................
structural..............
........................

 

[davidbeach]

Read, process, write one line at a time.

Read a line. If it doesn't contain "structural", write it. If it does contain "structural", write the first part, write "non", and write the remainder.

 

[nick262b]

Hi Alin 09,

I presume you mean that the file is too large to be read as a single string into a single cell in excel?

Assuming the text file is in distinct lines I would just write some code along the lines of:

Open file

Make a loop that runs until EOF and has a counter i that increments:

Read each line of the file and assign it to a string named "line" & i using the Line Input Statement

Then use the Replace function to change any occurances of "structural" to "non structural" in the string you have just written

Then write the string as line i in a new file.

Loop will end when all lines have been read and changed (if the string was present) and re written to a new file.

save new file.

This would be easier to do in C or C++ if you are familiar with this language as you could search the whole file as a single object. Or indeed, if this is just a one of file cant you just open the file in a text editior and use the find replace function Ctrl H for example?

Cheers Nick

 

[MintJulep]

Why don't you just open the file in a text editor and use FIND/REPLACE?

 

[alin09]

Everyone, thanks for the reply!
I gave a simplified example. What I really want is to find a word in a line and change another word in the same line.
This example will be more clear:
TEMP 1 100001 56
TEMP 1 100002 56
TEMP 1 100003 34
TEMP 1 100004 67
...................
...................
TEMP 2 100001 56
TEMP 2 100002 26
TEMP 2 100003 34
TEMP 2 100004 67
...................
...................
TEMP 3 100001 56
TEMP 3 100002 26
TEMP 3 100003 34
TEMP 3 100004 67
...................
...................

I want to find the line containing "100001" and change TEMP to "$". I can do this in Text Editor, but it is time consuming since there are a few hundreds to change in one file. Also, it need to be separated to a few files to be read into Excel since there are too many lines. I also need to change the format from Excel output.

I have done the code for test, somewhat like this:

Sub Macro2()
'Dim Data
'Dim Char As String * 1
Open "C:...\Desktop\VBA_read_input.txt" For Input As #1
r = 0
Do Until EOF(1)
Line Input #1, Data
If Mid(Data,9,6) = "#100001" Then
ActiveCell.Offset(r, 0) = "$"
Else
ActiveCell.Offset(r, 0) = Data
End If
r = r + 1
Loop
Close #1
End Sub

Here are my questions:
If the file format is not uniform and I don't know the location of the "word/string" in a line. How do I find it? If there any VB function that can search for a "string" in a line?
For example, two lines contains "static"
1000 456 static 00990
1222 static 44556 565676 990

Is it the only way that I need to read by "line" to operate in VBA? Some analysis result files are around 400M and the format is not uniform. How can I read such file by lines? Can I find a word and then read only some contents after the word?

nick262b, thanks for the reply. I don't know C++. I can not use other programming languages at work either since such software are not available (I heard that Matlab can do that too). So, I try to explore VBA.

 

[IDS]

The download file here should do what you want:

http://newtonexcelbach.wordpress.com/2011/07/29/importing-selected-rows-from-a-text-file/

Any problems, or if it doesn't do what you want, please ask.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[alin09]

IDS, thank you!

 

[loki3000]

search online for "split string vba"