csv file too big for excel

[pro356]

We have a file in csv format with about 1200 columns and over 2 million rows. It is 3.8 gb and too large to open in Excel. We need to be able to filter on several columns at once to conduct specific searches and are stymied trying to find something that will work. Any suggestions?

 

[mintjulep]

https://spark.apache.org/

 

[Andrzejek]

1200 columns, that is insane

I would set a table (or few tables) in Access (?) and with a little (VBA?) coding transfer this data into a data base.

After all, csv format is just a simple text file

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Mike Lewis]

This sounds like a good example of trying to stretch Excel into doing a job that it was never designed for.

(See Matt Parker's book, Humble Pi, for more examples.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[strongm]

Whilst Excel can be used as a database, it is NOT a database ...

 

[combo]

This sounds like a good example of trying to stretch Excel into doing a job that it was never designed for.

True for pure excel. Power Query, an excel ETL add-in tool can process up to 16k columns, as long as the output table size meets worksheet limits, it is worth to try. Microsoft has experimented with Power Query since excel 2013 (or 2010), starting from excel 2016 it is one of built in data tools.

combo

 

[SkipVought]

Here's another issue to be aware of, that is a reason that I rarely "open" a text file, like a csv file, with Excel although I will use Excel if I can. Rather, I IMPORT the data via Data > Text files... where I can specify each column as General, Date or Text, since Excel will make data conversion choices that you might not like.

There's a way to specify a text file structure using a .INF file.

https://docs.microsoft.com/en-us/windows-hardware/drivers/install/overview-of-inf-files

With a .INF file you can QUERY a pre-defined text file in Excel.

Using such a technique in Excel, it is possible to return manageable portions of a too-large text file to a worksheet.

BUT, you must know, understand and define the file column structure explicitly and correctly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[strongm]

Mike, Humble Pi is a great book! Love Matt Parker

 

[macropod]

You could use a directory/catalog mailmerge from Word to generate a report with the applicable filtering...

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[Mike Lewis]

Strongm, and have you seen Matt Parker's videos on YouTube - "Stand up maths"? Recommended.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[strongm]

Yep!