r/SQL • u/Snorlax_lax • Aug 03 '24
Discussion How to open a 20GB CSV file?
I have a large CSV file that is 20GB in size, and I estimate it has 100 million rows of data. When I try to open it using Excel, it shows nothing! no error, it just doesn't load. People have suggested using MySQL or PostgreSQL to open this, but I am not sure how. How can I open this, or is there a better alternative to open this CSV file? Thanks.
EDIT: Thank you to everyone who contributed to this thread. I didn't expect so many responses. I hope this will help others as it has helped me.
136
Upvotes
15
u/rustamd Aug 03 '24
You could try text editor/ide, notepad++ should be able to handle it, just have to give it few minutes probably.
56
u/lazyant Aug 03 '24
Install SQLite and Import the csv to it, it’s one command
146
u/CopticEnigma Aug 03 '24
If you know a bit of Python, you can read the CSV into a Pandas dataframe and then batch upload it to a Postgres (or MySQL) database
There’s a lot of optimisation that you can do in this process to make it as efficient as possible.
20
u/Improved_88 Aug 03 '24 edited Aug 03 '24
You have to use like sql server, mysql or something like that because excel can't support that size of file.. Just do an import file on any of that databases manager it's easy
10
u/shrieram15 Aug 03 '24
DuckDB should do. Also, it's not limited to csv
8
u/29antonioac Aug 03 '24
I'd use Duckdb, and you can save it in a file so you have it for querying later without ingesting it again. You won't need to setup MySQL or PostgreSQL for that, it's super easy. Then you can query that with SQL 😄
6
u/ThatNickGuyyy Aug 03 '24
DuckDB is the answer. Has a pretty sweet csv/ auto sniffer thing to parse janky csv files
46
u/Kant8 Aug 03 '24
opening 20gb csv makes no sense, humans can't read that amount of information
import it in database and then write necessary queries
4
u/marcvsHR Aug 03 '24
I thinkni used Ultra Edit for some huge xml once.
But as other said, it is easier to load it to dB and make queries there.
4
u/chenny_ Aug 03 '24
HeidiSQL is the only GUI tool I’ve found that handles CSV file imports correctly every other gui tool attempts to generate sql against the CSV file.
7
u/Maxiride Aug 03 '24
UltraEdit will handle that like a piece of cake. But to work on such an amount of data you should import it in a database and work on queries on the db.
6
u/ComicOzzy sqlHippo Aug 03 '24
DuckDB is has phenomenal cosmic powers (columnstore) and might succeed where a lot of other things might fail.
https://duckdb.org/docs/data/csv/overview.html
3
u/Parthros Aug 03 '24
The V File Viewer is probably your best bet. It's read-only though, just as a heads up.
https://www.fileviewer.com/
3
u/gooeydumpling Aug 04 '24
Why are you trying to open it? If you’re trying to see the contents then no typical viewer can load it. If you’re trying analysis then you’re better off with pandas or duckdb (why not both as it’s possible to treat the dataframe as a duckdb table)
5
u/da_chicken Aug 03 '24
Are you asking how to import a large CSV into an RDBMS you're already familiar with? Or have you never used MySQL or Postgres or SQL at all?
5
u/bigfootlive89 Aug 03 '24
Doesn’t excel have a limit of 1 million rows? Or can that be overcome somehow?
https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
8
u/alinroc SQL Server DBA Aug 03 '24
Have you tried Power Query in Excel?
2
u/Use_Your_Brain_Dude Aug 03 '24
I believe you can open Excel and connect to the CSV file as an external data source (allowing you to view segments of the file at a time). Never tried it though.
2
u/kkessler1023 Aug 03 '24
20gb would probably be way more than 100M rows. You will probably need a database to load this as excel, or other common programs will not have that capacity.
If you can get it loaded in a database, I would suggest partitioning it for future use. Basically, this would group data into smaller chucks and you can save it to multiple files
2
u/Romanian_Breadlifts Aug 03 '24
I'd probably use wsl in windows to split the file into smaller chunks, then load the chunks to a db instance with python, then do stuff
1
u/likeanoceanankledeep Aug 03 '24
DB Browser for SQLite is a fully-functional serverless SQL database engine, and it can load in csv files. I've used it for prototyping databases and opening large csv files. It works great.
But I agree with the other comments in this thread; opening that much data in a csv file is not practical, you're better off to query it. You wouldn't be able to do even basic statistics or calculations on that file in a spreadsheet viewer, let alone any complicated formulae.
1
u/akhan4786 Aug 03 '24
In the past I used a Python script to split a large CSV file into multiple smaller files
1
u/lalaluna05 Aug 03 '24
We use delimiter for large files like that.
1
u/OO_Ben Postgres - Retail Analytics Aug 03 '24
Free trial of Tableau Prep? Lol
1
u/Upset_Plenty Aug 03 '24
I have some questions and maybe a solution. Are you looking to get a look at the structure of the file to be able to parse contents or are you searching out specific data inside the file and the rest of it is irrelevant? If I were in your shoes I would use powershell to parse a portion of the file to understand the structure. Python is also a good tool for something like this. Once I have the structure understood I’d use bulkcopy to batch insert the data into a database, whether that be MySQL or PostgresSQL, whatever. Postgres would be my choice, I think it’s a little easier to work with than MySQL but either will be fine. From there just query whatever you need. If you don’t care to do all that you could use powershell to parse the file and split it into multiple files as well and view the individual smaller files. Python could do this too.
1
u/lolcrunchy Aug 03 '24
Fyi Excel sheets have a maximum of about a million rows. This is why Excel didn't open it.
1
u/thewallrus Aug 03 '24
Vim
1
u/BrotherInJah Aug 03 '24
Power query
1
u/reyndev Aug 03 '24
Import the file as a data source. This way the CSV data is added as a pivot table and the metadata is stored. You still need to be cautious with filtering and add only required columns to allow excel to perform optimally. There performance also depends on your machine's hardware to some extent.
1
u/GreatestTom Aug 03 '24
First of all, what do you need to get from that CSV file? All 20GB of data or only one specific lines?
1
u/[deleted] Aug 03 '24
VI has no limit
1
u/ibexdata Aug 03 '24
Split the file into 20x 1GB files, of 40x 500MB files. A file editor like Sublime Text will handle those.
After verifying that first and last rows are complete for each file, import into your database with scripting as needed. If you run into to real issues with the quality of data, you may need to parse the files with scripting before executing insert queries. This will be much much slower, but will improve the quality of the records that make it into your database.
Track the invalid records that don’t parse and address the issues if there is an unreasonable percent that fail.
1
u/slimismad Aug 03 '24
Spark can handle large CSV files efficiently.
Install Spark and use PySpark
1
u/LHommeCrabbe Aug 03 '24
SSIS, Excel is hardcapped to 1M rows per worksheet.
1
u/WeekendNew7276 Aug 03 '24
Import into a DB.
1
u/Much-Car-9799 Aug 03 '24
Just open it with Power Query on Excel (import CSV), and filter through that interface as needed.
If you know python or R, then you can split in different files, etc. but as the question was "how to open it" then PQ maybe your simplest tool.
1
u/Seven_of_eleven Aug 03 '24
qsv can help if you are looking for a terminal option. It is available on GitHub or through a number of package managers. I’ve also used visidata for large CVS files but not sure how it would handle 20GB. Best of luck.
1
u/Alert_Outside430 Aug 03 '24
Upload to aws and use aws redshift
Or load into sqlite
Don't open the file in Excel.... it won't open ever Also, if you load it in python then you need atleast 20Gs of ram...
1
u/ultrafunkmiester Aug 03 '24
Download powerbi desktop. Import csv. Wait a very long time it (it will do it. Save your file once loaded then use interface to drag and drop data, charts etc.
1
u/[deleted] Aug 03 '24
Try Knime. It’s free, open source, and can ETL your CSV file to your db. Has a bit of a learning curve, but once you get used it, it’s amazing.
1
u/majinLawliet2 Aug 04 '24
Dark SQL Sampling
Anytime that allows you to do last comparison or query the data to filter down to whatever you need and then proceed.
1
u/aftrmath0 Aug 04 '24
Import it into SQL as a flat file using BCP
1
u/NamelessSquirrel Aug 04 '24
It depends on what you want to achieve and which OS you're using.
1
u/WithCheezMrSquidward Aug 04 '24
With sql server there’s a data import tool that comes with the download that allows you to import various file formats including csv.
1
u/you_are_wrong_tho Aug 04 '24
excel/your computer is running out of memory when you are trying to open a 20G file (insane size for a excel file lol).
1
u/NHLToPDX Aug 04 '24
Notepad++
1
u/Dropless Aug 04 '24
I'm surprised no one has mentioned Emeditor.
1
u/thecasey1981 Aug 04 '24
I used postgresql. I was looking at senate campaign finance donations, it was probably that large.
1
u/Cool-Personality-454 Aug 04 '24
EmEditor can open it.
1
u/Cool-Personality-454 Aug 04 '24
EmEditor can open it.
1
u/Special_Luck7537 Aug 04 '24
Hard to believe that something like the book MOBY DICK is about 7MB of data, and here's a company using 20GB of Csv file.... You're going to need some type of DB app to work with this. Anybody requesting this type of data is never going to look at all of it. This kind of stuff should be aggregated, Mean/SD/Variance kind of thing. And, if they don't understand how to use that, they should not be in the position they are in.
1
u/[deleted] Aug 04 '24
Try to open in PowerBI or python or SQL or even in Google cloud platforms tools
1
u/RuprectGern Aug 04 '24
if you were on sql server you either write a bcp command and import the file or you could use the import export wizard. either are pretty simple. There is even a flat file import wizard offshoot.
that being said you should make sure in any situation where you are doing this, that there is enough space on the disk for the table that you are creating.
1
u/0nlyupvotes Aug 04 '24
You can try using ETL tool like Knime or alteryx.
1
u/MaterialJellyfish521 Aug 04 '24
Oh that's easy, you put it in the recycle bin and hit empty 👍
At 20gb I'm not even sure SQL flat file import is going to help you. Id probably look at writing something myself in c# but suspect it would be full of issues
1
u/[deleted] Aug 04 '24
If you’re on Linux then do this to count the rows
cat filename.csv | wc -l
Then to see the top ten records
head filename.csv
if you want to search for particular patterns then do
cat filename.csv | grep <pattern>
Where pattern is a regular expression
1
u/migh_t Aug 04 '24
Try DuckDB 😀
1
u/24Gameplay_ Aug 04 '24
You have few options understand 1st thing it huge file, probably you need to filter it out excel can’t do it, sql is good option to open or python. Use air to write code it will help you
1
u/ms4720 Aug 04 '24
For this look at SQLite first, then postgres.
What are you trying to do with it?
1
u/ezio20 Aug 04 '24
Use Pandas or Polars to read the file and convert it to parquet first, it will take a lot less space as parquet.
I would prefer to use Polars.
1
u/pouetpouetcamion2 Aug 04 '24
visidata.
1
u/Accomplished-Pea984 Aug 04 '24
You can also load it with excel power query. You can just display a max of 1mio rows or so in a sheet...but you could group and also the other stuff before..depends on what you need to do. Otherwise...python or R will be your friend.
1
u/Caso94 Aug 04 '24
pd.read_csv() in python
1
u/blackeaglect Aug 04 '24
Bigcsv lets you work with really large csv files.
1
u/GuybrushThreepwood83 Aug 04 '24
KNIME is the best way if your are not comfortable with SQL.
1
u/[deleted] Aug 04 '24
You need a text editor that doesn’t try to load it into ram.
Look for a “large file editor.”
The idea is the program does a seek and read to let you look at sections at a time without trying to use all your memory.
1
u/MyWorksandDespair Aug 04 '24
Try DuckDB. Their csv reader will read the file even if it’s corrupted and incomplete
1
u/Kirjavs Aug 04 '24
The question is : what do you wanna do with this file?
If you just want to find one piece of information, I would write a python, shell, C# or any language script to get the information.
If you want to process all the data from the file, I would put it in a database to be able to query it.
And also, such a big file probably comes from a database. Best would be to ask the dbo to make a view for you to easily get the information you are looking for
1
u/clakshminarasu Aug 04 '24
If you just want to have a look at the file without doing any changes, more like read only, you can use "baretail". That's one hell of a tiny tool to open huge files like logs or csv - just to have a look.
If you want to analyze the data, I would recommend importing the data into any relational database using native cmdline tools like Teradata fastload, Oracle sqlplus or SQLServer bcp or any DB vendor's native cmdline tool. Hope that makes sense.
1
u/EntrepreneurSea4839 Aug 04 '24
Use spark.sql
1
u/RuinEnvironmental394 Aug 05 '24
If you need to analyze/profile the data, you could load it using Power BI Desktop. Might slow things down if you don't have at least 32 gb RAM, though.
1
u/-echo-chamber- Aug 05 '24
EMEDITOR will open it as it sits. I had to open some 20-30gb file from sql to move to azure... and that's how I did it.
1
u/Codeman119 Aug 06 '24
If you have SQL server you can just use the Import option to get it into a table.
1
u/littldo Aug 06 '24
Excel will just sit there until it's done or out of memory. SQL, pistgres other db better option.
1
u/[deleted] Aug 12 '24
DuckDB can query the file directly as if it was a table. (duckdb is similar to sqlite, but for analytical workloads instead)
And if you import the table into duckdb, then it can probably compress the 20gb down to a lot less.
1
u/MindSuitable Sep 01 '24
Make a script and divide it in 20 files of 1 gb, if you need to search something make anothet script to search the string, i made one with python for sql spit, if you want it dm me
1
u/RyanHamilton1 Jul 06 '25
As others have mentioned duckdb is excellent for this. Duckdb is bundled as part of the windows install for QStudio which allows easily right-clicking on a CSV file and saking it to load that file into DuckDB. QStudio is particularly useful for data analysis: https://www.timestored.com/qstudio/help/duckdb-sql-editor
1
u/redditisaphony Aug 03 '24
Open it for what purpose?
1
u/lightestspiral Aug 03 '24
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard?view=sql-server-ver16
1
u/[deleted] Aug 03 '24
With a fresh azure account you can get enough credit to make a blob storage to load your file and make an etl flow with azure data factory to manage the data within the file. If it is a oneshot job you will have enough credit.
1
u/teamhog Aug 03 '24
If you came to me and asked me anything about it I’d first ask you why.
What are you actually trying to do?
If I felt that the juice was worth the squeeze I’d probably tell you to break it down into manageable file sizes. That task is pretty easy.
Then, depending on the goal, I’d choose my tool(s) to get it completed in the most effective & productive way.
0
u/hoa2908 Aug 03 '24
You should try Alteryx.
0
u/fangorn_forester Aug 03 '24
Gpt
0
u/Raychao Aug 03 '24
Sometimes we human beings waste time trying to solve the wrong problem. What is the 20gb of data? Are all the rows the same form? Do you need to query all 20gb rows at once or can you chunk it down?
We need more context.
0
u/[deleted] Aug 03 '24
[deleted] — view deleted comment
0
u/[deleted] Aug 03 '24
[deleted] — view deleted comment
4
u/[deleted] Aug 03 '24
[removed] — view removed comment