excel file too large to load? any way around this??

[Marvinuser]

Ok heres my dilemma, I am running reports from a inhouse software that exports ONLY to excel. this leads to many problems as the reports are VERY large, 100,000 rows or more, they take about 20 mins to run and then they give the error of "file not loaded completely" they are exported as a .txt file and are displayed in excel. Is the file there but just can't load because of excel being overloaded past the 65,000 row capacity? I tried to save it as a .txt and import it into access but it still gave me the 65,000 rows, Any ideas?? Anyone??? this would REALLY help me out tremendously.

 

[MisterC]

You say that the exports are "only to Excel", yet you then say that they are exported as .txt files.

If they are indeed text files, MS Access can import them directly (without Excel) and does not have the 65,000 record/row limitation that the current versions of Excel have.

Why not import the .txt file directly into Access?

 

[SkipVought]

Hi,

Do not OPEN, or IMPORT.

QUERY instead, using Data/Get External data...

return the subset of data that you NEED in order to do what you need. You RARELY need 100,000 rows returned.

You might need to do a CrossSum query (something like a PivotTable)

Or use the PivotTable wizard geting EXTERNAL DATA.

Or import in MS Access and query Access ffrom Excel.

Skip,

_[red][/red]

 

[Marvinuser]

The inhouse software that i am using only has a button built in to export to excel, when the file is done processing (approx 20 mins) it then opens in excel as a .txt file. i don't open it it just opens by itself as part of the export process. then i just noticed that it is saved in my temp files as a .txt. so i'll try to import those into access. thanks for the help!

 

[Griffyn]

If it's inhouse software, it shouldn't be an issue to have this program changed so that it doesn't open Excel with the .txt file.

 

[macropod]

Hi Marvinuser ,

FWIW, Excel 2007 can handle 100,000 rows of data easily.

Cheers

[MS MVP - Word]