Try Studio 3T for Free

Import Wizard

With the Import Wizard in Studio 3T, run MongoDB import tasks without having to type a single command line in the following formats: JSON, CSV, BSON/mongodump, SQL, and another collection. Download it here.

Looking to export, not import? View the Export Wizard documentation which covers how to export data from MongoDB to CSV, JSON, BSON/mongodump, SQL, and to another collection.

Supported import formats

Studio 3T supports the following import file types:

  • JSON
  • CSV
  • BSON/mongodump

Additionally, data can be imported from:

  • A SQL database
  • Another collection

Open the Import Wizard

After successfully connecting to a MongoDB database, there are two ways to open the Import Wizard:

  • Button – Click on Import in the Global Toolbar or
  • Right-click on any server, database, or collection in the Connection Tree and choose Import

From here, choose one of the supported import formats:

Supported import formats

For all imports, you can trigger one of three actions from the Import Wizard toolbar:

Choose Save task, Save task as, or Execute from the Import Wizard toolbar

  • Save task – Saves the task and provides the option to view it in Task Viewer
  • Save task as – Gives you the option to rename the task, if already saved
  • Execute – Runs the import on the spot

View the full documentation on Tasks and Task Scheduler.

The Operations window on the bottom-left side tracks the progress of your import.

Track the progress of your import in the Operations Window

Import JSON to MongoDB

Choose JSON as the import format.

Click on to upload documents, – to remove them, and the clipboard icon to paste data from the clipboard.

Under the Target Database and Target Collection, double-click on a cell to type in the target database or collection.

Click on a cell under Target CollectionUnder the Insertion Mode column, double-click on a cell to choose one of the insertion modes from the drop-down menu.

Click on a cell under Insertion Mode and choose one from the dropdown menu

Click on Execute to run the import. Alternatively, choose Save task or Save task as… to automate the task.

Import CSV to MongoDB (e.g. Excel)

Choose CSV as the import format. This will open up the two sub-tabs, Source options and Target options.

Source options

There are two source options: Import from Clipboard and Import from CSV file.

Import from clipboard

If you already have copied data, Studio 3T will automatically show the preview of the detected rows.

Import from Clipboard automatically detects rows and gives you a preview

Update the settings (e.g. delimiter, text qualifier) in the top-right to configure your import.

Adjusting the delimiter during CSV import

  • Delimiter – Character that separates values in a row (e.g. comma, tab, semicolon).
  • Text qualifier – All symbols between the text qualifiers will be parsed as one field.
  • Skip first <n> lines – Skips the first <n> lines from the beginning of the file. Usually this is set if the file contains a preamble or leading comments.
  • File contains header with field names – Check/uncheck as needed.

Import from CSV file

Click on the folder icon and locate the CSV file to be imported.

Choose the CSV file to be imported

Same as above, a preview will appear. Simply adjust the settings as needed.

Target options

Click on the Target options tab to further configure the CSV import.

Target Options tab in the Import Wizard

  • Target database – Type to filter and choose the target database from the auto-detected list, or drag and drop it from the Connection Tree.
  • Target collection – Type to filter and choose the target collection where the CSV rows will be imported. Alternatively, use drag and drop.

    If the target collection already exists, the imported rows will be added to that collection. If the target collection doesn’t exist, Studio 3T will automatically create one with the same name.

  • Insertion mode – Choose an insertion mode from the dropdown.
  • Empty fields – Choose how to treat empty fields (Import as Null, Import as Empty String, or Exclude).
  • Trim spaces – Choose whether to trim no, leading, trailing, or all spaces.
  • Unescape control characters in strings (with \) – Control characters \r, \n, delimiter (,), escape (\) will be prepended with \
  • Don’t infer document structure – By default dots (.) in header names are interpreted as embedded documents. Choose this option to replace all dots with underscore (_) and make all documents flat.
  • Don’t detect arrays – Header names such as arr.0, arr.1, arr.2, etc. are detected as part of an array. Choose this option to disable array detection.

Choose which fields to import by checking/unchecking the boxes in the Import fields view, and double-check that all looks good in the JSON output preview.

Choose which fields to import and get a preview of the document

Click on Execute to run the import. Alternatively, choose Save task or Save task as… to schedule the task.

Supported date formats

CSV import supports eight date formats:

  • ISO
  • Locale
  • MDY
  • DMY
  • YMD
  • MYD
  • DYM
  • YDM

The date format XXX supports all order variations of the year, month, and day fields, as well as three separators: “-“, “/” and “.”

Both ISO and Locale formats accept their respective variations like date only, date with short or long time, etc.

Upon CSV import, Studio 3T will try to detect a suitable date format for all the values of a column and will automatically use one based on the following criteria:

  • The above date formats are tested in the given order with all the values of a column/field
  • The first format to match all values is selected as suggested date type for the field or column

But of course, as with all data types, the selection can always be overriden.

Download Studio 3T to start your CSV import to MongoDB.

Import BSON/mongodump to MongoDB

Select BSON – mongodump folder or BSON – mongodump archive as the import format.

Regardless of the option chosen, a ‘mongorestore’ executable will have to be accessible for this type of import to work.

Click on the folder icon to locate the mongodump folder to be imported.

Choose the BSON folder to be imported

If the folder is valid, the available databases and collections will auto-populate the Import sources section. Check or uncheck the collections to include or exclude them in the import.

Under the BSON options tab, check or uncheck other configuration options as you see fit.

Check or uncheck BSON configuration options as needed

When ready, click on Execute to run the import. Alternatively, choose Save task or Save task as to schedule the task.

Import SQL to MongoDB

Import Wizard imports a single SQL table to a single MongoDB collection at a time.

If you need to import entire SQL databases or multiple SQL tables to a single MongoDB collection, then SQL to MongoDB Migration is the right feature for you.

Choose SQL Database as the import format which will open the SQL Import tab.

Select the source SQL connection

To define the source SQL connection, click Click here to connect to a server which will open the SQL Connection Manager.

SQL Connection Manager in the Import Wizard

Most users will need to create a new SQL connection. 

With your SQL connection details ready, click on New Connection in the toolbar and fill out the Server and SSH Tunnel tabs with the details needed.

Create a new SQL connection or edit an existing oneClick Save. Then click OK. For future imports, you can simply choose the saved connection from the list.

Next, choose the source SQL table by clicking on Click here to select a table. This will display a list of all SQL tables in your database.

Select the source SQL database from the list

Type to filter or simply choose the source table from the list.

Next, define the target MongoDB database and target MongoDB collection by typing their names, or dragging and dropping them from the Connection Tree.

Define the target MongoDB database and collection

If the target collection already exists, the imported rows will be added to that collection. If the target collection doesn’t exist, Studio 3T will automatically create one with the same name.

Choose an insertion mode from the dropdown menu.

Check (or uncheck) fields in the the Import options section to include (or exclude) them in the import.

You can also double-click on any cell under Field Name or Mongo Type columns to update field names and field types.

Check or uncheck fileds to include them in the SQL import

The resulting JSON output preview is shown on the right.

Additional settings you can configure:

  • SQL NULL values – Choose how to handle SQL NULL values.
  • On error – Choose how to handle errors on data import.
  • Array creation – Choose how to handle mongo field names in dot notation for sub-fields that have numeric names.
  • Enable embedded document creation – Check/uncheck as necessary.

Once done, click on Execute to run the import or choose Save task or Save task as to automate the import.

SQL Import to MongoDB is a Pro and Enterprise feature. Learn more about the different Studio 3T editions.

Studio 3T supports all the major SQL databases: Oracle (Enterprise version only), Microsoft SQL Server, MySQL, and PostgreSQL.

Import another collection

Looking to copy MongoDB collections to another database? Here’s a quicker way.

Select Another collection as the import format. This will open the Collection Import tab.

Import a collection to another collection

Define the Target database by typing its name and choosing it from the auto-detected list. Alternatively, drag and drop it from the Connection Tree.

Do the same for Target collection. Then, choose an insertion method from the dropdown menu.

If the target collection already exists, the imported rows will be added to that collection. If the target collection doesn’t exist, Studio 3T will automatically create one with the same name.

Now that we’ve configured the target database, let’s define the source database.

Click on Connect to source and choose the source MongoDB connection from the Connection Manager. Click Connect.

Connection Manager via Import Wizard

Next, choose the source collection from the tree. This is the collection that will be copied and pasted into your target collection.

Choose the source SQL connection

Click Execute to run the import, or save the import as a task by clicking Save task (as).

Want other time-saving features? Make sure to check these out:

  • Export Wizard – Export entire MongoDB collections and views, query results, or specific documents to CSV, JSON, BSON/mongodump, SQL or another collection
  • Aggregation Editor – Build MongoDB aggregation queries stage by stage
  • Query Code – Generate driver code in Java, JavaScript (Node.js), PHP, C#, and Python from MongoDB and SQL queries
  • IntelliShell – Studio 3T’s built-in mongo shell with robust autocompletion

Updated on October 2, 2019

Was this article helpful?

Related Articles