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

Question

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?

Just FYI - A CSV file is NOT an Excel file. All because Excel can (sometimes) open it, doesn't mean you should use Excel. — Reversed Engineer, Dec 17 '15 at 10:54
I had the same issue and thought my experiment results were gone. It proved that the file was actually intact; just opened it with a different program. — George, Jul 14 '16 at 12:43
This will help in case of the Query result to CSV https://stackoverflow.com/a/54879655/1365663 — Jignesh Variya, Feb 26 '19 at 06:34
Try using [Open Refine](http://openrefine.org/). It has been able to handle datasets that otherwise crashed Excel for me. — David Lemayian, Jan 23 '14 at 11:02

score 29 · Answer 1 · answered Jun 30 '13 at 15:23

29

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 Jun 30 '13 at 15:23

skyliner28

315
2
2

6

Bit of a pain that you have to email them to get your trial key. – fantabolous Jul 08 '14 at 09:02
3

PC only. No OSX version. – Adam_G Aug 28 '17 at 16:06
1

Thankfully the trial key can be obtained by emailing them, and then you get an automated response with the key. If you're just trying to get a one off read it's useful. If you want to try it out and realise it does the job long term then $49 not bad. I spent way too long trying to find something that'll open an 8GB csv. I have to do this on a monthly basis! – John Nov 11 '17 at 10:51
Sadly, does not work well with high DPI screens - text is almost unreadably small. – Bart Read Jan 05 '18 at 11:34
2

Delimit do it's work, but it's a trial version and won't allow you to do calculation or save it in other formats – Gabriel Marius Popescu Dec 14 '18 at 09:35

score 19 · Answer 2 · answered Apr 30 '14 at 09:19

19

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 Apr 30 '14 at 09:19

Martijn van der Jagt

524
3
13

Access definitely the quickest DB Import interim solution if you have it. – tinonetic Nov 18 '14 at 07:05
It's worth noting that Access won't import more than 255 columns. If you're dealing with Excel's too many columns error instead of rows, Access won't be able to help. – Sam Weaver May 07 '18 at 15:04

score 7 · Answer 3 · answered Jun 05 '13 at 16:49

7

Excel 2007+ is limited to somewhat over 1 million rows ( 2^20 to be precise), so it will never load your 2M line file. I think that the technique you refer to as splitting is the built-in thing Excel has, but afaik that only works for width problems, not for length problems.

The really easiest way I see right away is to use some file splitting tool - there's tons of 'em and use that to load the resulting partial csv files into multiple worksheets.

ps: "excel csv files" don't exist, there are only files produced by Excel that use one of the formats commonly referred to as csv files...

answered Jun 05 '13 at 16:49

fvu

32,488
6
61
79

1

If Excel could handle 2^32 rows all would be fine for the OP ... I guess you rather mean 2^20 rows ;) – Pragmateek Jun 05 '13 at 16:53
1

@Serious arghh, time for some more coffee! Thanks for the heads up – fvu Jun 05 '13 at 16:55

score 7 · Answer 4 · answered Jun 05 '13 at 21:35

7

You can use PowerPivot to work with files of up to 2GB, which will be enough for your needs.

answered Jun 05 '13 at 21:35

Jack

2,750
2
27
36

score 7 · Answer 5 · answered Sep 11 '13 at 15:39

7

First you want to change the file format from csv to txt. That is simple to do, just edit the file name and change csv to txt. (Windows will give you warning about possibly corrupting the data, but it is fine, just click ok). Then make a copy of the txt file so that now you have two files both with 2 millions rows of data. Then open up the first txt file and delete the second million rows and save the file. Then open the second txt file and delete the first million rows and save the file. Now change the two files back to csv the same way you changed them to txt originally.

answered Sep 11 '13 at 15:39

user2769406

111
1
1

37

This first step (editing the file extension) is absolutely pointless, a CSV is just a text file so you can open it and edit it directly with any text editor. – ydaetskcoR Sep 03 '14 at 12:15
1

@Zenadix . open with notepad. or in cmd.. notepad.exe test.csv – Luigi Aug 04 '15 at 18:37
1

@ydaetskcoR I found opening a file with millions of lines in Excel that was `.csv` gave me the error of File not opened completely, whereas if it was `.txt` it opened no issues. – codaamok Apr 07 '16 at 09:56
1

It is not solution.We can not open with notepad! – Mohsen Tavoosi محسن طاوسی Nov 02 '16 at 13:59
how to delete the second million rows fast? – compski Oct 06 '19 at 06:38
1

if you use terminal, use the following commands to get first 100 lines : cat file.csv | head -100 – Rishi Barve Feb 17 '21 at 09:49

click here · Answer 6 · 2018-08-13T15:42:10.683

6

I'm surprised no one mentioned Microsoft Query. You can simply request data from the large CSV file as you need it by querying only that which you need. (Querying is setup like how you filter a table in Excel)

Better yet, if one is open to installing the Power Query add-in, it's super simple and quick. Note: Power Query is an add-in for 2010 and 2013 but comes with 2016.

edited Aug 13 '18 at 15:42

answered May 08 '17 at 13:54

click here

814
2
10
24

score 5 · Answer 7 · answered Apr 05 '16 at 13:15

If you have Matlab, you can open large CSV (or TXT) files via its import facility. The tool gives you various import format options including tables, column vectors, numeric matrix, etc. However, with Matlab being an interpreter package, it does take its own time to import such a large file and I was able to import one with more than 2 million rows in about 10 minutes.

The tool is accessible via Matlab's Home tab by clicking on the "Import Data" button. An example image of a large file upload is shown below: Once imported, the data appears on the right-hand-side Workspace, which can then be double-clicked in an Excel-like format and even be plotted in different formats.

score 4 · Answer 8 · answered May 18 '18 at 20:20

I was able to edit a large 17GB csv file in Sublime Text without issue (line numbering makes it a lot easier to keep track of manual splitting), and then dump it into Excel in chunks smaller than 1,048,576 lines. Simple and quite quick - less faffy than researching into, installing and learning bespoke solutions. Quick and dirty, but it works.

score 3 · Answer 9 · answered Jun 05 '20 at 09:52

3

Try PowerPivot from Microsoft. Here you can find a step by step tutorial. It worked for my 4M+ rows!

answered Jun 05 '20 at 09:52

ironzionlion

832
1
7
28

score 1 · Answer 10 · answered Sep 25 '13 at 13:16

1

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

Use a database, is the best option for this problem.

Excel 2010 specifications .

answered Sep 25 '13 at 13:16

alap

646
1
11
24

score 1 · Answer 11 · answered Nov 22 '16 at 06:42

Use MS Access. I have a file of 2,673,404 records. It will not open in notepad++ and excel will not load more than 1,048,576 records. It is tab delimited since I exported the data from a mysql database and I need it in csv format. So I imported it into Access. Change the file extension to .txt so MS Access will take you through the import wizard.

MS Access will link to your file so for the database to stay intact keep the csv file

score 1 · Answer 12 · answered Jun 02 '20 at 21:29

The best way to handle this (with ease and no additional software) is with Excel - but using Powerpivot (which has MSFT Power Query embedded). Simply create a new Power Pivot data model that attaches to your large csv or text file. You will then be able to import multi-million rows into memory using the embedded X-Velocity (in-memory compression) engine. The Excel sheet limit is not applicable - as the X-Velocity engine puts everything up in RAM in compressed form. I have loaded 15 million rows and filtered at will using this technique. Hope this helps someone... - Jaycee

score 0 · Answer 13 · answered Sep 04 '19 at 05:55

I found this subject researching. There is a way to copy all this data to an Excel Datasheet. (I have this problem before with a 50 million line CSV file) If there is any format, additional code could be included. Try this.

Sub ReadCSVFiles()

Dim i, j As Double
Dim UserFileName As String
Dim strTextLine As String
Dim iFile As Integer: iFile = FreeFile

UserFileName = Application.GetOpenFilename
Open UserFileName For Input As #iFile
i = 1
j = 1
Check = False

Do Until EOF(1)
    Line Input #1, strTextLine
    If i >= 1048576 Then
        i = 1
        j = j + 1
    Else
        Sheets(1).Cells(i, j) = strTextLine
        i = i + 1
    End If
Loop
Close #iFile
End Sub

score 0 · Answer 14 · answered Nov 11 '20 at 08:44

0

You can try to download and install TheGun Text Editor. Which can help you to open large csv file easily.

You can check detailed article here https://developingdaily.com/article/how-to/what-is-csv-file-and-how-to-open-a-large-csv-file/82

answered Nov 11 '20 at 08:44

Vikas Lalwani

1,041
18
29

score -1 · Answer 15 · edited May 05 '15 at 16:35

-1

Split the CSV into two files in Notepad. It's a pain, but you can just edit each of them individually in Excel after that.

edited May 05 '15 at 16:35

Carrie Kendall

11,124
5
61
81

answered May 05 '15 at 15:21

Dredge

15
1

3

Notepad does struggle with large files, though, so command-line text tools like `head` or `tail` from Cygwin or GnuWin32 might be easier. – Rup May 05 '15 at 16:07

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

15 Answers15

Linked