Large CSV and Parquet Files: Work With Them in a Browser
A big history file does not open in Excel and crashes many online tools. Here is how to work with one in the browser.
Quant developers and market data engineers often work with price and tick histories of many gigabytes. Excel stops at about one million rows, and a tool that loads a whole file into memory fails long before a very large file is finished. The usual answers are a database, a script or a cloud service. Those are fine, but heavy for a quick look or a one-off cut.
Data Utility has a large file mode for files from about 50 MB up to 1 GB or more. The file is read from your disk in pieces instead of being loaded whole, and nothing is uploaded. The modes are convert, SQL, profile and split. Use SQL to filter a date range or one symbol and save only the rows you need, profile to see blanks and duplicates, split to cut the file into smaller parts, and convert to change the format.
A good routine for a large history is to profile it first, filter to the period or instruments you need with SQL, and then convert the smaller result to Parquet for repeated work. Parquet reads faster and takes less space, and a smaller file can then go through the normal tools.
Know the limits. Speed depends on your computer and browser, and a very large file takes time. Keep the original file untouched and give outputs clear names. If the work is a daily production job, a scripted pipeline is a better home for it than a browser tool.
Related tool pages
- Merge or Split Files: CSV, JSON, Excel, Parquet: Combine many CSV, JSON, Excel or Parquet files into one, or split one big file into many parts. Free, and your files stay in your browser.
- Combine Multiple CSV Files into One: Combine many CSV files into one file in your browser. Add a source_file column, mix in Excel or JSON files, and download CSV, Excel or Parquet. Free.
- Merge Parquet Files into One: Merge several Parquet files into one Parquet, CSV or Excel file in your browser. No code and no upload. Free, with a source_file option.
- Split a CSV File into Parts: Split a CSV into smaller files by number of rows, number of parts or the value of a column. Download them as one ZIP. Free, in your browser.
- Split a Parquet File into Parts: Split a Parquet file into smaller Parquet, CSV or Excel files by rows, equal parts or a column value. Free, in your browser, nothing uploaded.
- Split a Large JSON or NDJSON File: Split a big JSON, NDJSON, CSV or Parquet file into smaller parts and download them as a ZIP. In your browser, nothing is uploaded.
How to use the Split a Large CSV: step by step
- Open a tool with large file mode, such as Data Transformation, and choose Use large file mode.
- Pick SQL, profile, split or convert.
- Run it on your file and save the result.
- Continue with the smaller file in the normal tools.
When to use the Split a Large CSV
- Cut a few months out of a huge CSV
- Check a large file for blanks and duplicates
- Split a file into parts that Excel can open
- Convert a large CSV to Parquet
Example
Input: big.csv (1,000,000 rows)
Output: files of 200,000 rows
Frequently asked questions
Why split a large CSV?
Excel opens about one million rows per sheet, and many import tools limit file size. Splitting makes each part easy to open and upload.
How large a file can I split?
It depends on your computer, because the file is read in your browser. Files up to about 100 MB usually work.
Can I merge CSV and Parquet files together?
Yes. You can mix CSV, JSON, Excel and Parquet files. They are stacked in the order you add them and saved in the format you choose. Columns missing in some files are left empty.
How do I split a big CSV file?
Load the file, choose rows per file, a number of equal parts or one file per value of a column, and download the ZIP. The header is kept in every part. Up to 500 parts at a time.
Tip: Filter first, convert second. Doing it in that order keeps the Parquet file small.