{"id":105804,"date":"2025-03-05T00:28:59","date_gmt":"2025-03-05T00:28:59","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105804"},"modified":"2025-02-25T00:57:29","modified_gmt":"2025-02-25T00:57:29","slug":"importing-and-exporting-data-in-mongodb-compass","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/nosql\/mongodb\/importing-and-exporting-data-in-mongodb-compass\/","title":{"rendered":"Importing and exporting data in MongoDB Compass"},"content":{"rendered":"\n

This article is part of Robert Sheldon's continuing series on Mongo DB. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n\n\n\n

When working with MongoDB, you\u2019ll likely need to import or export document data from time-to-time. You can use MongoDB Compass for both types of operations, working with either the JavaScript Object Notation (JSON) format or the comma-separated values (CSV) format.<\/p>\n\n\n\n

Both formats are commonly used to exchange data across different types of systems and applications. In Compass, you can import documents from JSON or CSV files, or you can export them in either format. You can also export an entire collection, a subset of data from a collection, or the data returned by an aggregation.<\/p>\n\n\n\n

Earlier in this series, I briefly demonstrated how to use MongoDB Compass to import data from a JSON file into a target collection. In this article, I go deeper into the process of importing documents and then explain how to export documents to an external file. Compass provides an intuitive interface for importing and exporting data, making the process both quick and easy. This article provides multiple examples for better understanding how these operations work.<\/p>\n\n\n\n

Note: For the examples in this article, I used the same MongoDB Atlas environment I used for the previous articles in this series. Refer to the first article for details about setting up these environments. The examples are based on the hr<\/code> database and candidates<\/code> collection. If you plan to try out these examples, the database and collection should already exist. In addition, the collection should contain no schema validation rules or documents. You\u2019ll also need to download the candidates.csv<\/code> file and the candidates.json<\/code> file from here<\/a>.<\/p>\n\n\n\n

Import data from a CSV file into a MongoDB collection<\/h2>\n\n\n\n

Comma-separated values (CSV) files are commonly used to transfer data between systems, applications, and individuals. It does not matter how the data is generated or used, as long as it shares a common format.<\/p>\n\n\n\n

Strictly speaking, a CSV file is a text file that uses a comma as its delimiter. However, MongoDB Compass uses the term CSV file<\/em> in a broader sense. To this end, the interface supports four delimiter types: comma, tab, semicolon, and space. In addition, the file does not necessarily need to take a .csv<\/code> extension. For example, you might use a file that has a.txt<\/code> extension.<\/p>\n\n\n\n

Regardless of the delimiter used, the file must follow a specific format. To begin with, the file\u2019s header row must list the document fields, separated by commas or by one of the other supported delimiters. The header row should contain all possible fields, even if some documents don\u2019t include those fields.<\/p>\n\n\n\n

Each subsequent row in the file should list a document, or more specifically, the document\u2019s values, specified in the same order as the fields in the header row. The values should also be separated by the same delimiter used for the header row.<\/p>\n\n\n\n

The following figure shows the contents of the candidates.csv<\/code> file, which you\u2019ll be importing into the candidates<\/code> collection. The first row lists the field names, and the remaining rows list the field values, with each document on a separate line. A comma is used as the delimiter.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

When specifying the field names, you must take into account fields that are objects (embedded documents) and fields that are arrays. In this case, all the documents include the position<\/code> field, which is an embedded document that contains the title<\/code> and dept<\/code> subfields. Many documents also include the skills<\/code> field, which is an array that can contain a varying number of values.<\/p>\n\n\n\n

To help you better understand the type of documents in the candidates.csv<\/code> file, the following code shows the first document, formatted as JSON:<\/p>\n\n\n\n

{\n  \"_id\": 101,\n  \"name\": \"Drew\",\n  \"position\": {\n    \"title\": \"Senior Developer\",\n    \"dept\": \"R&D\"\n  },\n  \"yrs_exp\": 18,\n  \"skills\": [\n    \"Java\",\n    \"SQL\",\n    \"Python\",\n    \"PHP\"\n  ]\n}<\/pre>\n\n\n\n
The document includes the position<\/code> field, which contains the two subfields. In the CSV file, the embedded field names are listed as position.title<\/code> and position.dept<\/code>, indicating that they\u2019re both subfields in the position<\/code> field.<\/p>\n\n\n\n
This document also includes the skills<\/code> array, which contains four values. In the CSV file, the skills<\/code> field is listed multiple times in the header row, with each value specified with an index number, as in skills[0]<\/code> or skills[1]<\/code>. The documents in the CSV file that include the skills<\/code> array contain up to six values within the array, so the index values range from 0<\/code> to 5<\/code>. (An array\u2019s index is 0-based.)<\/p>\n\n\n\n
When a CSV file includes documents that don\u2019t contain a field listed in the header row, the delimiter must still be included so the number of values associated with each document matches the number of fields listed in the header row, in the order the field names are specified. If one of the fields is an array, a document must include a delimiter for each possible array value listed in the header line.<\/p>\n\n\n\n
For example, the following code shows the first three documents included in the candidates.csv<\/code> file:<\/p>\n\n\n\n
101,Drew,Senior Developer,R&D,18,Java,SQL,Python,PHP,,\n102,Parker,Data Scientist,R&D,14,Python,SQL,R,Scala,Java,Julia\n103,Harper,Marketing Manager,Marketing,22,,,,,,<\/pre>\n\n\n\n
The first document includes the skills<\/code> array, but the array contains only four values, so the document is listed with two trailing commas. The commas serve as placeholders for the remaining two array values. However, the skills<\/code> array in the second document includes six values, so no extra commas are needed. The third document does not include the skills<\/code> array, so six commas are required to correspond to the array\u2019s number of potential values.<\/p>\n\n\n\n
You might have noticed that the documents in the CSV file include an _id<\/code> field. If you import documents that don\u2019t include this field, MongoDB will automatically add the field to each document and generate a unique value for that document.<\/p>\n\n\n\n
Once your CSV file is in the proper format, you can import it into your MongoDB collection. To import the data from the candidates.csv<\/code> file into the candidates<\/code> collection, you should first open the collection in the main window of the Compass interface. From there, click the Add<\/strong> Data<\/strong> drop-down arrow near the top of the window and then click Import JSON or CSV file<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
When your system\u2019s file manager window appears, navigate to the folder that contains the candidates.csv<\/code> file, select the file, and click Select<\/strong>. Compass will launch the Import<\/strong> dialog box, as shown in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The default delimiter is Comma<\/strong>, but you can instead select Tab<\/strong>, Semicolon<\/strong>, or Space<\/strong>. The source file will determine which delimiter you should use. The dialog box also includes two options that you can clear or select:<\/p>\n\n\n
\n