Opening large text files
 

I have some text files which I want to open in Excel. I use the Text
Import Wizard and it works with no problems. As I am going to be doing
this a lot, I want to write a macro so I can do it at the press of a
button.

However, when I try to record the macro and use the Text Import Wizard,
I get the error message "Too many lines". After OK'ing the error
message, the file is opened properly, but the macro recorder has turned
itself off and when I look at what has been recorded it's all in red -
obviously an error.

Is there a way I can get around this? It's not that there are too many
columns, it's just that there seems to be a limit to the number of
lines that can be recorded in a command in a macro.

Thanks for any help.

free


--
freekrill
------------------------------------------------------------------------
freekrill's Profile: http://www.excelforum.com/member.php...fo&userid=7561
View this thread: http://www.excelforum.com/showthread...hreadid=399371

I'm betting that you have lots of fields in each record. You may want to look
at this kb article to see a workaround.

"Out of Memory" Message Using the OpenText Method
http://support.microsoft.com/default...;EN-US;q134826

freekrill wrote:

I have some text files which I want to open in Excel. I use the Text
Import Wizard and it works with no problems. As I am going to be doing
this a lot, I want to write a macro so I can do it at the press of a
button.

However, when I try to record the macro and use the Text Import Wizard,
I get the error message "Too many lines". After OK'ing the error
message, the file is opened properly, but the macro recorder has turned
itself off and when I look at what has been recorded it's all in red -
obviously an error.

Is there a way I can get around this? It's not that there are too many
columns, it's just that there seems to be a limit to the number of
lines that can be recorded in a command in a macro.

Thanks for any help.

free

--
freekrill
------------------------------------------------------------------------
freekrill's Profile: http://www.excelforum.com/member.php...fo&userid=7561
View this thread: http://www.excelforum.com/showthread...hreadid=399371

--

Dave Peterson

More likely, the text file being imported contains more than 65,536
lines, which is Excel's row capacity. If that's the case, you might
try recording / debugging / customizing your macro on a text file that
is known to be less than 65k lines.

For the files larger than 65k lines, I'm not sure the text import
wizard will work for you: it may be written to error out and stop when
it reaches 65,536. If that's the case, you may need to write code that
reads (and counts) each import line separately, parses it into fields,
and writes it to cells. Then when it reaches 65k lines, insert a new
sheet or jump to a new sheet and continue parsing and writing.

Excel is more user-friendly than Access and has many database-like
functions and attributes, but when files get this large it may be time
to consider database software. When a single import file starts to
span more than one tab in a workbook, you may find that you'll have to
implement a number off workarounds to accommodate checking data here,
checking data there, adding it together, etc. Then your question
becomes "Are the workarounds so chunky and inelegant that time spent on
workarounds is less egregious and inconvenient than time spent learning
and working with Access".

Oops! Didn't see Dave P's post. The "more likely" line in my post is
not meant to gainsay Dave P's response; rather, it was intended as a
response to freekrill's conjecture about recording too many lines in a
macro.

I read "too many lines" as a VBA error ("too many line continuations").

I thought the "too many lines" error you get when the text file has too many
lines was something like "File not loaded completely".

But who knows???????



Dave O wrote:

Oops! Didn't see Dave P's post. The "more likely" line in my post is
not meant to gainsay Dave P's response; rather, it was intended as a
response to freekrill's conjecture about recording too many lines in a
macro.

--

Dave Peterson

I've never encountered either error, but my irony gland kicks into
overdrive when I learn that the macro recorder allows line
continuations and then the debugger causes an error when too many
continuations exist.

So we need to hear from freekrill: is it too many lines, or too many
columns?

Here's a macro that will tell how many lines are in your text file:

Sub Count_Lines()
Dim K As Long
Dim Lyne As String

Open ("c:\exact_file_path\Exact_File_Name.txt") For Input As #1
'You'll need to substitute the exact file path and exact file name in
this line.

Do Until EOF(1)
Line Input #1, Lyne
K = K + 1
Loop

MsgBox ("The file contains " & K & " rows.")
Close #1
End Sub

Thanks guys for all your ideas.

The text files had only 40 rows and were about 200 characters wide, so
there shouldn't have been a problem in fitting all the data on a
worksheet, even at one character per cell.

It appears that my problem was that VBA will only hold so many lines in
any one command. Using the macro recorder got me a text wizard command
that went like..............

Array(1,1), Array(2,1), ....up to Array(97,1)

and then failed.

I got around the problem by deleting some line continuations and
manually filling in the rest. I was able to go up to ...Array(200,1)
without any problems


Once again, thanks for your time

free


--
freekrill
------------------------------------------------------------------------
freekrill's Profile: http://www.excelforum.com/member.php...fo&userid=7561
View this thread: http://www.excelforum.com/showthread...hreadid=399371