Excel CSV. file with more than 1,048,576 rows of data

51

I have been given a CSV file with more than the MAX Excel can handle, and I really need to be able to see all the data. I understand and have tried the method of "splitting" it, but it doesnt work.

Some background: The CSV file is an Excel CSV file, and the person who gave the file has said there are about 2m rows of data.

When I import it into Excel, I get data up to row 1,048,576, then re-import it in a new tab starting at row 1,048,577 in the data, but it only gives me one row, and I know for a fact that there should be more (not only because of the fact that "the person" said there are more than 2 million, but because of the information in the last few sets of rows)

I thought that maybe the reason for this happening is because I have been provided the CSV file as an Excel CSV file, and so all the information past 1,048,576 is lost (?).

DO I need to ask for a file in an SQL database format?

This question is tagged with excel csv split max rows

~ Asked on 2013-06-05 16:37:49

The Best Answer is


26

You should try delimit it can open up to 2 billion rows and 2 million columns very quickly has a free 15 day trial too. Does the job for me!

~ Answered on 2013-06-30 15:23:08


15

I would suggest to load the .CSV file in MS-Access.

With MS-Excel you can then create a data connection to this source (without actual loading the records in a worksheet) and create a connected pivot table. You then can have virtually unlimited number of lines in your table (depending on processor and memory: I have now 15 mln lines with 3 Gb Memory).

Additional advantage is that you can now create an aggregate view in MS-Access. In this way you can create overviews from hundreds of millions of lines and then view them in MS-Excel (beware of the 2Gb limitation of NTFS files in 32 bits OS).

~ Answered on 2014-04-30 09:19:24


Most Viewed Questions: