MS Excel - How To Import a Large TXT File?

[txdave35]

Hey guys,

I'm sure this gets asked a lot, but I can't find a good workaround.

I have a text file with 200,000 records which is past the 65K limit that Excel allows. How can I import the full text on a single worksheet? This limitation is one of the dumbest restrictions MS designed.

 

[SkipVought]

You could purchase Excel 2007.

However, what I would do is set up a .ini file with a file description for this file structure, and then use MS Query with the ODBC text driver, to query the subset of data that you REALLY need in your workbook.

http://msdn.microsoft.com/en-us/library/ms709353.aspx

Alternatively, import the entire file into an MS Access table and query the table in Excel via MS Query.

In any case, I seriously doubt that you need all 200,000 rows in your workbook in order to accomplish your task.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[txdave35]

Thanks mate. I know how to do the MS query in Excel so I'll give that a shot.

 

[txdave35]

Looks like the query method won't work. It still only returns 65K rows of data.

 

[kjv1611]

Did you attempt to filter out records via the query? That's what Skip was implying - that you can reduce the total number of records, and then it would fit within the record limit of Excel.

If you're halfway comfortable with using Access, that would probably be the easiest method of taking care of this.

If you did need all your data, you could also do either of these 2 methods:

[OL][LI]Split the text file into a few text files, so you can import into multiple worksheets.[/LI]
[LI]Import into Access, query into separate tables, and then export those to multiple worksheets.[/LI]
[/OL]

Or perhaps you can shed a little light on what you're doing with the data, and then one of the folks 'round here could offer a suggestion on filtering the data more efficiently (if possible).

--

"If to err is human, then I must be some kind of human!" -Me

 

[txdave35]

Are you saying that it is not possible to get all the data to display past the 65K row?

The data is accounting data sorted by agency code. I could filter by agency code, but there are 100 different agency codes so that would give me 100 different sheets.

 

[SkipVought]

YES. THe PHYSICAL limitation of Excel 2003 and earlier is 54,536 rows.

WHY do you need 200K rows in your workbook?

I regularly query tables that contain MILLIONS of rows. What would I do with a million rows? Burp and keel over!

You Query in order to return a focused set of data that answers an immediate question. If you get another question, you query for THAT question.

Much more manageble than having ALL the data inyour workbook!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip meant 65,536 rows

Gavin

 

[macropod]

You could also read all the data into an array, process the data in the array in much the same way as you might with a worksheet, then write the results out to a worksheet.

Cheers

[MS MVP - Word]

 

[SkipVought]

Gavin,

Thanks for reading my mind. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}