Open a flat file with more than 65536 rows in Excel

[barnard89]

Hi

I usally open a csv into Excel and start working on it.
The usually I get abut 20000 - 30000 rows in the CSV file and this works fine
But I get error when I have flat CSV file over 65536 rows and I get an error that the file is not loaded completely

Is there a way , I can open up the flat csv completely in Excel ? Can I do that in 2 -3 different worksheets ?

Please suggest


Thanks

 

[fredericofonseca]

you need to use some VBA code for that.

search these forums as this problem has been solved before, and you will be able to find the code required for you to do this.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

Hi,

Use Data/Get External Data/New Database Query -- text files.

You ought to be able to query this file in manageable "chunks"

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[macropod]

Hi barnard89,

The other thing you need to consider is whether you really need to have the data in Excel, or just your analysis of it. You could, for example, load the whole file into an array, analyse the data in the array and write out the results to a worksheet.

I recently did such an excercise with 8 files encompassing 11.4 million records. The only sheets I needed were one to hold the file names and a series to display the results of the analysis. The whole process took less time than in would have for Excel to read then parse and write the records to a series of worksheets.

Cheers