Opening a csv with more than 65536 rows

[anjanesh]

Hi

I have a 10MB .csv file having some 200,000+ rows and 3 columns.
I obviously can open the file in any other plain text editor.
But when I open this in MS Excel 2003, I get the msg *File Not Loade
Completely*.
The details are

This error is usually encountered when an attempt to open a file wit
more than 65,536 rows or 256 columns is made. Excel is limited t
65,536 rows of data and 256 columns per worksheet. You can have man
worksheets with this number of rows and columns...

So how would I go abt saving this entire .csv file as .xls ? The latte
part (after 65536) is getting truncated.
Is there any solution to this ?

Thank

 

[Dave Peterson]

I'd use a text editor to split the giant file into smaller pieces (40k lines
each??) and then import each of those separately.

 

[Paul B]

anjanesh, have a look here for some code that will do what you want

http://redirx.com/?334z

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[anjanesh]

I find it strange to believe that Microsoft Excel cannot handle a sheet
having more than 65,536 rows ! I think I'll switch over to OpenOffice.

 

[Dave Peterson]

Wait until you find out that excel only supports 256 columns per worksheet!

 

[Bill Sharpe]

I find it strange to believe that Microsoft Excel cannot handle a sheet
having more than 65,536 rows ! I think I'll switch over to OpenOffice.

Current version of OpenOffice only supports 32k rows. Version 2.0, now
in beta, supports 64k rows, the same as Excel.

If you insist on using a spreadsheet, Quattro Pro can handle 1,000,000
rows. But when you have so much data you should consider using a
database program instead.

Bill

 

[aaron.kempf]

use a friggin database, excel doesn't scale. excel just isn't ready
for real-world use

 

[Timothy L]

Not much help at the moment, but when Excel 12 is eventually released, then according to information released
by Microsoft today,

"Specifically, the Excel 12 grid will be 1,048,576 rows by 16,384 columns. That's 1,500% more rows and 6,300%
more columns than in Excel 2003, and for those of you that are curious, columns now end at XFD instead of IV."



In the mean time, here is a workaround:

Start another Worksheet and then import the remaining portion of your CSV file by using the Text Import
Wizard.

Please note: The instructions below are for Microsoft Excel 2003, but should work in other versions of
Microsoft Excel.


Insert a new worksheet
http://office.microsoft.com/en-us/assistance/HP052008891033.aspx


Import a text file
http://office.microsoft.com/en-us/assistance/HP052573861033.aspx

When you get to step 5, you should have the "Text Import Wizard - Step 1 of 3" dialog up. In Step 1 of the
Text Import Wizard, change the value next to "Start import at row" to 65537.



In addition, you might want to take a look at this program called CSVed. It is an easy and powerful CSV file
editor.
http://home.hccnet.nl/s.j.francke/t2t/text2table.htm

 

[aaron.kempf]

excel is still a waste of time.

use microsoft access if you want to have a real reporting solution..
and i see 90% of all excel spreadsheets should be automated and
replaced with access reports.. or crystal reports or something