Import Wizard

Studio 3T’s Import Wizard lets you import JSON, CSV, BSON/mongodump, SQL, and another collection to MongoDB.

To follow along with this video, you’ll want to download the customers.json file mentioned in the introduction.

Download Studio 3T here to access Import Wizard and skip ahead to your format:

• Import CSV to MongoDB
• Import JSON to MongoDB
• Import BSON/mongodump to MongoDB
• Import SQL & Migration to MongoDB (Microsoft SQL Server, MySQL, and PostgreSQL)
• Import a MongoDB collection

Basics

After successfully connecting to a MongoDB database, open the Import Wizard by clicking on Import in the Global Toolbar.

Alternatively, right-click on any server, database, or collection in the Connection Tree and choose Import (Collections, Data).

Or right-click anywhere on a Result tab (Collection Tab, SQL Query, IntelliShell), or any input or output panel in Aggregation Editor and choose Import.

From here, choose one of the supported import formats:

• JSON – mongo shell / Studio 3T / mongoexport
• CSV
• SQL Database
• BSON – mongodump folder
• BSON – mongodump archive
• Another Collection

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

Automate and schedule imports as tasks

Import Wizard integrates with Tasks and Task Scheduler, which let you save recurring imports as tasks. You can, of course, also run all imports immediately by clicking on Execute.

To save an import as a task, click on Save task (as). Name the task, then click OK.

This saves the task in the Task Viewer, from which you can choose to schedule, unschedule, edit, clone, delete, add, and execute tasks.

Import CSV to MongoDB (e.g. Excel)

Open the Import Wizard. Then, choose CSV as the import format.

This will open up the two sub-tabs, Source options and Target options.

Import from clipboard

If you already have copied data, click on Import from Clipboard and Studio 3T will automatically show a preview of the detected rows.

Update the settings (e.g. delimiter, text qualifier) in the top-right to configure your 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.

Same as above, a preview will appear. Configure the other settings as needed.

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

• 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.

• 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 options view, and double-check that all looks good in the JSON output preview.

Once done, you can save the export as a task, which you can run on-demand or schedule for later, or execute the task immediately.

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 overridden.

Import JSON to MongoDB

Open the Import Wizard. Then, choose JSON as the import format and click OK.

Click on + to add JSON source documents, – to remove them, or the clipboard icon to paste JSON data from the clipboard.

Here we will add the JSON source document, Rainfall-Data.

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

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

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

Import BSON/mongodump to MongoDB

Open the Import Wizard. Then, select BSON – mongodump folder or BSON – mongodump archive as the import format.

For both types, you will need to configure a ‘mongorestore’ executable under Preferences > MongoDB tools.

Click on the folder icon and choose the root mongodump 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.

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

Import SQL & Migration to MongoDB

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

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

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.

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.

Choose an insertion mode from the dropdown menu.

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

You can double-click on any cell under Field Name or Mongo Type columns to update field names and field types. 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.

Import another collection

Open the Import Wizard and select Another collection as the import format. This will open the Collection Import tab.

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.

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

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

---