MDB to CSV

What to expect

Drop an .mdb file and you get one ZIP back, with a CSV file for each table, named after the table. CSV is the plainest way to move data into a database, a script, a CRM or any spreadsheet, and it's the right choice when the database is too big for one Excel workbook.

It's the usual route when an Access database is being retired: the tables go into PostgreSQL, MySQL or SQLite, a Python or R script picks them up, or a mailing tool imports the contact list. CSV also suits an archive: plain text that no single program is needed to read.

Each file is UTF-8 with a byte order mark, so Excel shows accented letters correctly, and has Windows line endings. Choose a comma, semicolon or tab between fields above; a semicolon suits Excel in countries that write decimals with a comma.

How to export the tables

1. Pick the delimiter above: Comma, Semicolon or Tab.
2. Drop the .mdb file on the box, or choose it from your device.
3. When "Your file is ready" appears, choose Download. You get a ZIP named after the database, even if it has a single table.
4. Unzip it. Each CSV is named after its table; characters a file name can't hold, such as / or :, become an underscore.
5. Open "What changed" under the file for anything that was approximated or skipped.

Choosing a delimiter

• Comma is the default and what most import tools expect. Start here unless you have a reason not to.
• Semicolon is for Excel set to a language that writes 3,5 for three and a half. That Excel expects semicolons when you double-click a CSV, and a comma file opens with everything in one column.
• Tab suits text full of commas and semicolons, like addresses and notes, and tools that ask for tab-separated values.

Whichever you choose, a value that contains the delimiter, a double quote or a line break is wrapped in double quotes, as the CSV standard (RFC 4180) describes. The file stays valid either way; the choice only decides what reads most easily on the other side.

What's inside each file

• The first line holds the column names, in the table's order.
• Long Text is kept in full; CSV has no cell limit.
• Decimals always use a dot, like 444.555, whichever delimiter you pick.
• Dates are written as 2003-07-15, or 2003-07-15 09:30:00 when there's a time, without a time zone. A time stored without a date comes out on the day Access counts from, as 1899-12-30 09:30:00.
• Yes/No becomes TRUE or FALSE.
• An empty value and a missing (Null) value both come out as an empty field.
• Attachment fields, which only .accdb files have, list their file names, separated by semicolons.

In a CSV everything is text, so "approximated" here mostly means a value written differently from how Access showed it: a long decimal with every digit spelled out, a time with the date Access counts it from, or [binary] where an OLE object was. If you import into a database, give long decimals a text or exact numeric type rather than a floating-point one, and the digits survive. Keys and indexes don't travel in a CSV; recreate them in the new database.

Large databases

Each table is turned into CSV as soon as it's read, so its rows don't pile up in memory. In our measurements, a 200 MB test database with 3.3 million rows converted in 63 to 96 seconds on our test machine, and the tab needed up to about 2.4 times the file's size in memory, mostly for the database itself and the finished ZIP. We haven't measured larger files, and speed depends on your device. Files over 25 MB bring up a short note before they start; you can always continue. The one hard stop is 1 GB per file, a limit we set so the tab doesn't run out of memory.

Versions and passwords

This page is built for files from Access 97 to 2003, and an .accdb from 2007 or later converts here too. An encrypted .accdb asks for its password in the file list; ACCDB to Excel walks through it. What we've checked about .mdb database passwords is on the MDB page.

If something goes wrong

• Accented letters look wrong in Excel: open the CSV with Data › From Text/CSV and choose UTF-8, or use MDB to Excel for a database of normal size.
• Everything lands in one column: pick the semicolon above and convert again.
• Excel shows 444.555 as text or as another number: Excel set to decimal commas can read a dot that way. Import through Data › From Text/CSV and set those columns' type using the English (United States) locale, or use MDB to Excel, where numbers arrive as numbers.
• Notes break across rows in another tool: values with line breaks sit inside double quotes. Turn on that tool's option for quoted or multi-line fields.
• "This file looks damaged, or it isn't the format its name suggests": if Access itself has asked to repair this database, run Compact and Repair there first and convert the repaired copy.
• "Table … could not be read and was left out" in "What changed": the other tables still converted. A table that stops partway keeps the rows read up to that point, and the note gives the count.

Try it with a sample file

sample-access97-types.mdb (116 KB) comes from the tests of the open-source mdb-reader project. Converted here, it gives a ZIP with four CSV files. Table1.csv has a header and two rows, with dates such as 1981-12-12 and the Yes/No column as TRUE or FALSE. The other three files hold only a header line, and one of them has 89 columns. Source and license: sample notes.