Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Blog
    • Community
    • Testimonials
    • Whitepapers
    • Reports
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Careers
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • Download
  • My 3T
search

Studio 3T® Knowledge Base

  • Documentation
  • Tutorials
  • Workshops
Take the fastest route to learning MongoDB. Cover the basics in two hours with MongoDB 101, no registration required.
Start the free course

Import Wizard

Posted on: 16/09/2020 (last updated: 15/03/2023) by Dakota Karlsson

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

Looking to export, not import? Use the Export Wizard instead to export MongoDB to CSV, JSON, BSON/mongodump, SQL, and to another collection.

Basics

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

Click on Import in the global toolbar

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

Right click on the connection tree to import MongoDB collections and 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.

Right-click on a Result tab and trigger the import function

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.

Keep track of MongoDB import operations, always

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.

Save a CSV import as a task

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

Learn more about Tasks and Tasks Scheduler.

Import CSV to MongoDB (e.g. Excel)

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

Choose Import CSV to MongoDB

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.

Import data from clipboard

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.

Import a CSV file to MongoDB

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.

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

Select the fields to include in the MongoDB import

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.

Ready to import? Download the latest Studio 3T version here.

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.

Import JSON to MongoDB

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.

Download Studio 3T to start your JSON import.

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.

BSON import to MongoDB

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

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.

Check out these articles for more detail:

  • SQL to MongoDB Migration
  • How To Merge Multiple SQL Tables Into One MongoDB Collection
  • Import a SQL Database to MongoDB in 5 Steps

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

Configure the SQL connections

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 Studio 3T

Most users will need to create a new SQL connection.

Click on New Connection in the toolbar and fill out the Server and SSH Tunnel tabs with the details needed. Click 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 SQL source table

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.

Drag and drop connection to Import Wizard

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.

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

Choose and edit the SQL fields to 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.

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

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

Import another collection

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

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


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

How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

Dakota Karlsson

New to the world of databases, Dakota hails from a rural town in Texas and is learning just how exciting databases can be. After brief stops in the Big Sky state and a few years in Scandinavia, he now calls Berlin home and spends his spare time involved in the Berlin music scene and experiencing the art of German cuisine.

Article navigation

Related articles

  • Export Wizard
  • What’s New in Studio 3T 2020.4 | Introducing License Manager, IBM DB2 Migration Support, Export Wizard Improvements
  • MongoDB Atlas Tutorial: Set Up a Free Tier Cluster & Import Your First Collection
  • What’s New in Studio 3T 2021.10 | Value Search, Better Task Scheduling and Import Data Masking
  • What’s New in Studio 3T 2022.1 – SQL Connection Import/Export, MongoDB-AWS authentication

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2023 3T Software Labs Ltd. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum

We value your privacy

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data.

By clicking “Accept all”, you consent to the storage of cookies and the processing of personal data for these purposes, including any transfers to third countries. By clicking on “Decline all”, you do not give your consent and we will only store cookies that are necessary for our website. You can customize the cookies we store on your device or change your selection at any time - thus also revoking your consent with effect for the future - under “Manage Cookies”, or “Cookie Settings” at the bottom of the page. You can find further information in our Privacy Policy.
Accept all
Decline all
Manage cookies
✕

Privacy Preference Center

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data. Please choose for which purposes you wish to give us your consent and store your preferences by clicking on “Accept selected”. You can find further information in our Privacy Policy.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Google Analytics

Google Ads

Bing Ads

Facebook

LinkedIn

Quora

Hotjar

Reddit

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

HubSpot

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected