MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

txt file

  Asked By: Karina    Date: Aug 31    Category: MS Office    Views: 1654

Anyway I have a .txt file that is about 77000 pages long (not an
exaggeration). I need titles of each page in an excel spreadsheet. So
what I want to do is to have excel read through the file and if the line
starts with B/M to copy it into the next blank cell in the column. If
not delete the line from the .txt file if possible otherwise just jump
to the next line. Think anyone can help? I know excel only has 65536
lines in it so my file is to big but I can just do the procedure on half
at a time.



7 Answers Found

Answer #1    Answered By: Muntasir Bashara     Answered On: Aug 31

Though I'm certain someone here could work out a VBA program to open and
then read the text file, I think your situation could be more easily
rectified via Access. You can link or import the text file  into Access,
and then build a query using the criteria you just stated. It's fast and
simple. Then you can export the query to Excel.

Answer #2    Answered By: Cadencia Bernard     Answered On: Aug 31
Answer #3    Answered By: Patty Freeman     Answered On: Aug 31

If you do not want to use Access or VBA, try break the text file  into
halves, ie 30,000 and 47,000 lines,
import into Excel one at a time,
sort it,
delete irrelevant rows,
combine the two sheets back.

Answer #4    Answered By: Johnathan Nelson     Answered On: Aug 31

Excel supports slightly larger spreadsheets (about 1024 times the size of
the current spreadsheet) for a maximum of 1,048,576 rows.

Does anyone with the beta for Excel want to do a load and sort for him?

Answer #5    Answered By: Horia Ahmed     Answered On: Aug 31

Sub ExtractTitles()
.. rr = 0 ' row counter
.. cc = 1 ' eg, Col A
.. MyFile = "c:\MyTextFile.txt"
.. Close #1
.. Open MyFile For Input As #1
.. For ii = 1 to 1000000 ' yes, 1,000,000!
.... If EOF(1) Then
...... Exit For
.... Endif
.... Line Input #1, NextLine
.... xtr = NextLine
.... If Left(xtr, 3) = "B/M" Then
....... rr = rr + 1
....... Cells(rr, cc) = Mid(xtr, 4)
.... End If
.... If rr = 65000 Then
....... rr = 1
....... cc = cc + 1
.... Endif
.... DoEvents
.. Next ii
End Sub

OK, what have we got here?

1. We initialize 2 vars, rr and cc, to serve as pointers.
Modify them as you see fit.

2. Then we open the text file  (change name as required)

3. Then we enter a loop that reads the text file line by
line. The EOF statement gets you out at the end of file.
You might need more that 1,000,000 as your loop counter.

4. Then we check the left 3 characters of the extracted
text string. If it is "B/M", then we enter the branch.

5. Then, we place the rest of that line in the next row
of your active spreadsheet. rr is incremented by 1 to
assure next "hit" goes in the next row.

6. The trap at rr = 65,000 is optional. But, it would
enable you to get it all in 1 sheet if you don't mind
the rest spilling into the next column.

7. The DoEvents statement is also optional -- but while you
are debugging, its a good idea, especially in a massive
loop. This statement will give you the chance to end the
app without locking up your computer.

Natch, feel free to modify as required, but this should
give you the gist of what should work. Hope I understood
your needs correctly. Let me know if I went wrong anywhere.

Answer #6    Answered By: Sophie Williamson     Answered On: Aug 31

Thanks guys I used access. I had 77,000 pages which turned out to be
around 3 million or so lines of text so access worked really really

Answer #7    Answered By: Hattie Howard     Answered On: Aug 31

Are you open to non-VBA solution?

How about an old fashioned command line solution?

The easiest way to work with text files of this size is to use the DOS
Find command. The drill here is to go to a command prompt and then
'CD' into the folder that holds your big text file.

Then type: find "B/M" filename.txt >fname2.txt {enter}

This will filter the contents of the big file  and look for any line
that contains the substring 'B/M'. It will then copy any lines that
contain this text and output them to the screen. The '>fname2.txt'
redirects the screen output into the file named fname2.txt.

Once the new file is created that just contains the 'B/M' lines...
This should be a significant subset of the big file and you should be
able to open this in Excel without having to jump through a bunch of

Didn't find what you were looking for? Find more on txt file Or get search suggestion and latest updates.