Importing large text files into Excel.

[keithmlewis]

I am trying to import a text file that is over 300,000 lines long. However, the Excel rows stop at 65,536 and import wizard will not let you select which line of the import file you would like to start with. Please post all help on importing text files into Excel.

Thank you

[Damon Ostrander]

Hi Keith,

As always, there are a number of options.

If this is a one-time thing, then the best way to deal with it is to divide your input file into bite-size chunks, or extract just the chunk you want, using an editor. For example, you could use WordPad to just delete out all the records not of interest, and save the remaining records to a new file to be imported.

On the other hand, if you plan on importing this file or similar files many times, it would be worth your while to write a macro to do the import. A macro could provide several ways to handle your problem:

1. It could read all your data and wrap the data to additional worksheets;

2. If you are only interested in reading certain records rather than all records from the input file, it could filter the records while reading them, thereby reducing the total number of records to fit on one sheet;

3. If you want to skip a certain number of records, or perhaps just read a block of records from the file, it could do this based either on record number, or some value in the record fields that indicates when to start and stop writing records to Excel.

A VBA macro can gain this flexibility via use of standard file I/O rather than using the Excel import or open capability. If you are interested in investigating this, see the VBA helps for file I/O statements like Open, Line Input #, Input #, Close #, and EOF. It is not hard to do, but if you need a simple example to get started, follow up with your email address and I will send you one.

[Mark W.]

See http://support.microsoft.com/default...;en-us;Q120596.

[Parra]

If you want to do data analysis on the information while keeping it together you can importe it to access and run pivot tables from excel to the data in access. I have done this many times and works great for me.