Importing JSON files and Access databases into Base by Zaweba

Most databases don't start empty. There is usually an old file somewhere that holds the records you care about, and the first job is getting them across. Base by Zaweba already imports CSV and spreadsheet files, and it also takes two other common sources: JSON files and Microsoft Access databases, both the newer .accdb and the older .mdb kind.

The Access import is the one that saves the most time, because it can bring a whole database across rather than one table at a time.

Two ways to bring in an Access file

The first way treats a single Access table like a spreadsheet. You choose the table and its records come in, which is handy when you only want one list out of a larger file, such as the contacts from an old system you are otherwise retiring.

The second way imports the whole database. Base brings across the tables with their field types and their records. AutoNumber keys become record ids in Base, and relationships become lookup fields, with their cascade deletes kept. That last part matters: the links between your tables survive the move, so orders still belong to their customers and order lines to their orders, and deleting a parent still removes its children where the old database said it should.

Base reads the Access file without changing it. The original stays exactly as it was, so you can import, look around, and import again if you want to start over.

What you need on the PC

Reading an Access file needs the Access Database Engine installed on the PC. If yours doesn't, Base can't open the .accdb or .mdb file until it is installed. Nothing else is needed, and there is no account or online step.

The Mac and Linux version of Base doesn't have the .accdb import, so do this part on a Windows PC. Once the records are in a .obase file, that file opens on a Mac or Linux machine like any other.

Importing JSON

JSON import brings in records from a JSON file. It suits data exported from a web service, another app, or a script someone wrote years ago. Each record in the file becomes a record in a table. If the file's layout is complicated, with records nested inside records, it can help to tidy it into a simple list first so each entry maps cleanly to a row.

After the import

Open the relationships diagram straight away. It shows every link the import created, as a line between boxes, so you can check that the structure came across the way you expect. Then look at a few records in the datasheet, especially dates, money and yes/no fields, to be sure they read correctly.

This is also a good moment to add the things an old database may have lacked. Validation rules with clear messages, input masks on postcodes and phone numbers, and value lists on status fields will tighten up data that was entered loosely for years. Base refuses a new rule that existing records break and tells you how many, which makes it a useful way to find the untidy records the import brought with it.

Finally, build fresh forms and reports in Base rather than trying to reproduce every old screen. An auto form is a quick start, and the result works in the exported website as well, which the old Access forms never could.