Free CSV Editor for Large Files

A three gigabyte export lands on your disk, you double-click it, and the spreadsheet gives up. DbSchema has a CSV Editor for that file. Choose Query Tools → CSV Editor, point it at the path, and you get a grid you can filter, sort and correct, on files up to 5 GB. The CSV Editor is part of the free Community Edition and of Pro. It works on the file on disk, with no database in the picture.

Why large CSV files break ordinary editors

A spreadsheet works inside a fixed grid. Microsoft documents an Excel worksheet as 1,048,576 rows by 16,384 columns, so a sign-up log with two million lines has no single worksheet that can hold it in one piece. The DbSchema CSV Editor loads the rows into an in-memory grid instead, so what decides whether a given file opens is the memory free on the machine, up to the documented ceiling of 5 GB. The editor itself has no row count to run into.

Open a CSV File in the DbSchema CSV Editor

Open the DbSchema CSV Editor from Query Tools → CSV Editor in the application menu.

DbSchema then asks for the file and for the format it should be read with. Four settings describe the format:

• File encoding
• Field delimiter
• Record delimiter
• Quote character

DbSchema tries to detect all four from the content of the file, so your job at this dialog is to check the preview rather than to fill it in. The preview shows the rows already split into columns by the delimiter DbSchema picked. The values in them are rendered with the encoding it picked. Look at both before you open, because the whole file is read with the settings left in the dialog. Press Cancel at this step and DbSchema starts a blank CSV instead of opening a file.

Filter and Sort the Data

Right-click any column header to reach the filter and sort options for that column. On a two gigabyte log of sign-ups, a filter on the date column is how you get down to last month's rows without cutting the file up first.

Sorting is where the format of a CSV file gets in the way, because every value in it is text. DbSchema detects the data type of each column, text, numeric or date, and sorts by that type, so a revenue column comes back in order of value rather than in the order the digits happen to fall.

Edit the data, and add or delete rows and columns

Double-click any cell to edit its value in place, then press Enter or click another cell to confirm the change. A missing zip code in a customer export gets fixed in the cell where you found it.

Right-click a column header to insert or delete a column, and a row header to insert or delete a row. A vendor price list that needs a discounted price column, or fifty more products at the bottom, is handled from those two menus.

Every one of those changes lands in the grid DbSchema loaded into memory. The file on disk is untouched until you save it.

Encoding, delimiters and save format

Use File → Save to overwrite the file you opened. Use File → Save As to write a new path, and to change the shape of the output while you are there. The Save As dialog takes a new delimiter, a new encoding and a new quote character, and DbSchema writes the file with those instead of the ones it read. A comma-separated file that a legacy loader wants pipe-delimited, or a UTF-8 file that has to arrive as ISO-8859-1, is one Save As away.

Import a CSV into a database table

Editing the file is one job; querying its contents with SQL is another, and for that the rows have to reach a table. DbSchema loads them with the Data Importer, opened from Data Tools → Import Data From File or by right-clicking a table header and choosing Import Data From File. This is the step that writes to the live database, and it is a Pro feature rather than part of the free Community Edition.

In the import dialog you map each file column to a database column and leave out the columns you do not want. When the target table does not exist yet, DbSchema derives the column names and types from the file header and a preview of the content, and the column editor lets you correct both before the import runs. The separator, quote character, escape character, date format and locale are set in the same dialog. Rows that fail arrive in the Error pane with their line numbers, so you fix the source file or the mapping and run it again. The steps for one engine are written up in import a CSV into MySQL; PostgreSQL and the other supported databases use the same dialog.

Download DbSchema at https://dbschema.com/download.html, open Query Tools → CSV Editor, and give it the file your spreadsheet turned down. The CSV Editor is part of the free Community Edition, so nothing in it needs a licence; loading the same file into a database table uses the Data Importer, which is Pro.