Studio 3T’s Import Wizard lets you import JSON, CSV, BSON/mongodump, SQL, and another collection to MongoDB.
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 to MongoDB (Microsoft SQL Server, MySQL, and PostgreSQL)
- Import a MongoDB collection
After successfully connecting to a MongoDB database, there are two ways to open the Import Wizard.
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:
For all imports, you can trigger one of three actions 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
The Operations window on the bottom-left side tracks the progress of your import.
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, Studio 3T will automatically show the 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. Simply adjust the 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 fields view, and double-check that all looks good in the JSON output preview.
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:
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.
Import JSON to MongoDB
Open the Import Wizard. Then, choose JSON as the import format.
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.
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.
Click on the folder icon to locate the 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 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.
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.
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.
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.
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.
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).