# Importing large text files into Excel 2007



## Oldskiboat (Jan 29, 2013)

I have imported upto and including row 1048576 in sheet one. After I hit finish to start this, I received a message: The text file contains more data than will fit on a single work sheet. To continue and import as much of the data as will fit, click ok. You can then import the rest of the data by repeating the import operation on another work sheet and using the text import wizard exclude data already imported. Okay, I read that, but cannot find any way to exclude the data already imported and start placing the new data not previously coppied in a new tab (sheet 2). HELP!!!


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome

I still use Chip Pearson's code for importing large text files - see here

Importing Big Text Files Into Excel


----------



## scalh001 (Dec 17, 2011)

...And this is why many have grown to love the wonderful work of Chip.

I think I might hold on to this handy piece of code.


----------



## macropod (Apr 11, 2008)

If you have more than 1,000,000 records to process, that suggests you're using the wrong tool (Excel) or the wrong approach (loading onto a worksheet).

FWIW, I've used Excel to analyse a dataset with 20,000,000 records, without loading any of them onto a worksheet. Instead, I used vba to read the data into arrays and do the processing there, outputting only the results of the data analysis onto a worksheet. This approach was also much faster than trying to work with worksheet-based data.


----------



## scalh001 (Dec 17, 2011)

And VBA read the data arrays straight from the source — nicely done.

That is a little on the advanced side for me, so it would likely be behooving for me to do some...uhh...light reading on the subject.

I know with my previous employer, when our data analysis processes became too large for Excel's capacity, we shifted to Access, then to SQL to house our data records and analysis.


----------



## Glaswegian (Sep 16, 2005)

Paul - I've read about doing it that way, but never could get my head around arrays...

Typical corporation - everyone has the standard Office build but you have to justify the benefits of adding an Access licence. Since I only do this once a month, and can do it just fine in Excel, I won't get Access. Anyway, it's too long since I did any serious Access.


----------

