Import data

The Import wizard loads data into a MongoDB collection. It reads files, the clipboard, a table of a SQL database or another collection, and writes in batches. Large files therefore never need to fit in memory.

Open the Import wizard

Use any of these:

• Import on the global toolbar.
• In the connection tree, right-click a connection (Import…), a database or its collections folder (Import Collections…), or a collection (Import Data…).
• Database › Import Collections… or Collection › Import… in the menu bar.

When you open it from a collection, that collection becomes the target. Then pick a format in the Import format list and click Configure. A tab opens for that format.

Supported formats

The BSON formats are read and written by the app itself, so you don’t need mongodump or mongorestore installed. Excel files are an export format only: to import a spreadsheet, save it as CSV first.

Import a JSON file

Example: load customers.json into acme_store.customers.

1. Open the wizard on the acme_store database and choose JSON - mongo shell, mongoexport.
2. Click Add source and pick one or more files, or use Paste from clipboard to import JSON you copied.
3. Each source has its own row, with a target database, a target collection and an Insertion mode. By default, the database is the file’s folder name and the collection is the file name without its extension. Double-click a cell to change it.
4. Optional: tick Validate JSON before import. The file is then read once without writing. If any document is malformed, nothing is imported.
5. Check the first documents in JSON output preview.
6. Click Run and confirm.

Import a CSV file

1. Choose CSV, then select the File or the Clipboard as the source.
2. On Source options, set the Delimiter, the Text qualifier, Skip first <n> lines and File contains header with field names. Changing these options detects the columns again. Without a header, the fields are named field_0, field_1, and so on.
3. On Target options, click Select target to pick the collection, then choose the insertion mode and the CSV handling options (table below).
4. In Import options, each column has a row: untick a column to leave it out, set its Type, or give it another name in Overwrite Name. Date columns keep the detected date format.
5. Check Import source preview (first 100 rows) and the JSON output preview, then click Run.

Column names with dots become embedded documents (address.city becomes { address: { city } }), and names such as tags.0, tags.1 become arrays. Tick Don’t infer document structure or Don’t detect arrays to turn these off.

Import a table of a SQL database

1. Choose SQL database.
2. Click Click here to connect to a server and pick a saved SQL connection. The session is read-only.
3. Click Click here to select a table and choose the table in Select the table to import.
4. In Import options, untick the columns you don’t want, then rename fields and change each Mongo type as needed.
5. Choose a target collection, set the SQL options (table below), then click Run.

To join several tables, embed rows as arrays or run a custom query, use Migrate from SQL to MongoDB instead.

Restore a mongodump folder or archive

1. Choose BSON - mongodump folder (then the dump folder, a database folder or one .bson file) or BSON - mongodump archive (then the archive file).
2. A folder lists its collection files in Import sources, each with a target database and collection. system.* collections start unticked. An archive lists its collections, and Import data only for database and Import data only for collection limit the restore.
3. Set the BSON options (table below) and the insertion mode, then click Run.

A folder import restores documents only: the indexes and collection options in the dump’s .metadata.json files are not restored yet. An archive import creates each collection with its options.

Copy from another collection

Choose Another collection. Use Select source and Select target to pick the source and target collections on any open connection, set the insertion mode, then click Run. You can also use Copy and paste collections, which also copies indexes and validators.

Insertion modes

The insertion mode decides what happens when a document’s _id already exists in the target.

Overwrite, Drop and Clear can destroy data, so the confirmation names them. Drop and Clear run just before the first batch is written: if the first record cannot be read, the target is left untouched.

Error handling

The On error option decides what happens to a record that fails: a CSV row that cannot be read, or a document the server refuses (validation, duplicate key).

Some errors always stop the import, whatever the option. A malformed JSON document or a damaged BSON frame means the next record cannot be found. In that case, the documents read before the error are written first, and the message gives the record number and how many documents were imported.

Abort if _id already exists and Abort import never roll back. What was written before the stop stays in the target. To undo an import, import into a new collection first, check the result, then rename it.

Options reference

CSV

SQL database

BSON (folder and archive)

To use an external mongorestore, set it in Edit › Preferences › External tools › MongoDB tools: the bundled tools, the executable found on the PATH, or a custom path. Connections through an SSH tunnel always use the bundled tools.

Field masking

The JSON, CSV, SQL and collection imports can mask fields as they are written: open the field masking editor from the tab’s Data Masking section. The techniques are the same as in Mask data.

Save the import as a task

Save task keeps this configuration so you can run it again, and Schedule runs it on a schedule. Load task fills the tab from a saved import task. See Automation.

Tips

• Run a first import into a scratch collection (acme_store.customers_import) with Skip and continue, check the listed errors, then run it for real.
• For very large JSON files, leave Validate JSON before import off: it reads the file twice.
• The clipboard is handy for a few documents copied from a web page or a ticket.

Troubleshooting