Export MongoDB to CSV

In this MongoDB tutorial, We are going to learn “Export MongoDB to CSV”. We will also cover this with different operations and examples. These are the following topics that we are going to cover in this tutorial:

  • MongoDB export to CSV with query(command)
  • Export MongoDB documents to CSV with example
  • MongoDB export to CSV without header
  • MongoDB export to CSV using compass
  • Export MongoDB documents to CSV using atlas
  • Export MongoDB collection to CSV using python

MongoDB export to CSV with query (command)

We can also export data from MongoDB into other forms, such as JSON or CSV, so that it can be used by other applications.

So, to export the data from the MongoDB database, MongoDB provides a command-line tool known as mongoexport. With the help of this tool, you can exports data of a collection in JSON or CSV format.

Note that, The mongoexport command does not run in the mongo shell so to run this command you have to exist from the mongo shell.

Syntax:

For export the data in CSV format

mongoexport –db database_name –collection collection_name –type=csv –fields fields_names –out path_or_name_of_the_file
  • Here, the out command specifies the file in which exported data is to be stored and if we don’t use out then file will create at the path where we are at the time of running cmd.
  • If mongoexport tool is not present in yuor system then install it from here.

Read: How to download MongoDB database Tools?

There are some of the commonly used options in mongoexport:

OptionDescription
-helpReturn the information about option
-versionReturn the version of mongoexport
-dbSpecify the name of the database in which mongoexport will run
-collectionSpecify the collection to export
-fieldsSpecify the fields that include in the export.
-typeSpecify the file type, JSON or CSV
-skipControl from where mongoexport start exporting files.
-sortUse to sort the exporting the data.
-limitLimit the number of documents to export.

So these are some of the options that you can use with the mongoexport tool and export the data into CSV file format or you can also export in JSON format as well.

Export MongoDB documents to CSV with example

In MongoDB, we use the mongoexport tool to “export MongoDB documents to CSV”. With the help of this tool, we can export also in JSON format as well. You will more understand with the help of an example so let’s get into it.

Example 1:

In this example, we export all the documents present in the EmpInfo collection to a CSV file.

Export MongoDB to CSV
Documents that we export into CSV
mongoexport –-db Company -–collection EmpInfo -–type=csv -–fields name,age -–out E:\students.csv

Use this command to export the documents into CSV.

Export MongoDB to CSV with example

Here, you can see we successfully export all the documents into the students.csv file. You can also check it

Example Export MongoDB to CSV file
Example Export MongoDB to CSV file

Note that, there is no _id field in the CSV file that is because we don’t specify it in the command. If we specify this with –fields then this is also exported into the CSV file.

Example 2:

In this example, we export only 2 records of the EmpInfo collection using the limit into a CSV file.

mongoexport --db Company --collection EmpInfo --limit 2 --type=csv --fields name,age --out E:\employee.csv

Here, we use the same database and collect and export only 2 records into the employee.csv file by applying –limit 2.

Export documents of MongoDB to CSV file
Export documents of MongoDB to CSV file

Here, see in the output we successfully exported 2 records into the CSV file so this is the simplest way of exporting the documents into the CSV file.

Read: MongoDB join two collections

MongoDB export to CSV without header

In MongoDB, Sometimes the question demands that we have to export all documents into CSV without a header so to store the data in CSV without a header then you have to use the –noHeaderLine option. This will remove the header and store all the data into a CSV file.

Example:

The below command is used to store documents into CSV without a header.

mongoexport --db Company --collection EmpInfo --type csv --noHeaderLine --fields name,age --out E:\header.csv

Here, we only need to add one extra field –noHeaderLine before the field name, and this will remove the header and store all the documents into CSV.

These documents we are going to export into CSV

MongoDB export to CSV without header
MongoDB documents export to CSV without header

Now, we apply the above command to export into CSV without a header

MongoDB export to CSV without header example
MongoDB export to CSV without header

Here, we successfully exported the documents into the header.csv file without a header. You can also check the output

Export MongoDB to CSV without header
Export MongoDB to CSV without header

In the output, we successfully imported the documents into CSV without a header.

Read: Import CSV into MongoDB

MongoDB export to CSV using compass

MongoDB compass can export the data from a collection on either JSON or CSV. In compass, we can also apply the filter on the documents while exporting the data and compass only export those data that match the given condition.

Here we export the data into CSV two ways :

  1. Export entire collection
  2. Export filtered subset of a collection

Now, I’ll show you both the ways one by one so that you can easily export the documents to CSV.

To export the entire collection to a file follow the below steps:

  • Open MongoDB compass
  • Connect to the deployment containing the collection that you wish to export the data from
  • Navigate to the collection
  • Now click on the Collection in the top-level menu and select Export Collection.
MongoDB export to CSV using compass
MongoDB export to CSV using compass
  • Compass display the following tab
export to CSV using compass
Select the way to export collection

Here, we Export Full Collection so click on that, and after that click on the SELECT FIELDS button.

  • Now, select document fields to include in your exported file.
MongoDB export to CSV in compass
MongoDB export to CSV in compass

Here, the fields are checked are included in the exported file and you can also add document fields to include with the Add Field button if the field is not detected automatically that you want to include.

  • Choose the file type and export location.
export to CSV using compass example
select file type and export location

Here, as we are exporting into CSV so we select CSV file type and also select the file location where to export the file.

  • Click on EXPORT button.
compass export to CSV
compass export to CSV

Now, a progress bar displays the status of the export and we successfully export data into the artists.csv file. If there an error occurs during export then it into red and displays an error message on the tab.

To export a subset of documents from a collection follow the below steps:

  • Open MongoDB compass
  • Connect to the deployment containing the collection that you wish to export the data from
  • Navigate to the collection
  • Now click on the Collection in the top-level menu and select Export Collection.
MongoDB export to CSV using compass
MongoDB export to CSV using compass
  • Specify a filter in the query bar and this export only the documents which match the filter.
MongoDB export filter documents to CSV using compass
Export query with a filter
  • Now, select document fields to include in your exported file.
MongoDB export to CSV in compass
MongoDB export to CSV in compass

Here, the fields are checked are included in the exported file and you can also add document fields to include with the Add Field button if the field is not detected automatically that you want to include.

  • Choose the file type and export location.
export to CSV using compass example
select file type and export location

Here, as we are exporting into CSV so we select CSV file type and also select the file location where to export the file.

  • Click on EXPORT button.
compass export to CSV
compass export to CSV

Now, a progress bar displays the status of the export and we successfully export data into the artists.csv file. If there an error occurs during export then it into red and displays an error message on the tab.

Read: MongoDB group by multiple fields

Export MongoDB documents to CSV using atlas

In this topic, you are going to learn how to export documents into CSV using MongoDB atlas. For exporting the documents into CSV MongoDB provides a command-line tool mongoexport. This tool will help you to export the documents into JSON or CSV.

Now, for exporting the documents into CSV using atlas you have to follow the below steps:

  • Login into MongoDB atlas
  • Create a new project and a cluster
Export MongoDB to CSV using atlas
Create a new project and cluster

Here, I created a project called ExportData and also created a cluster, user.

Read: If you want to learn how to create a new project and cluster in MongoDB atlas?

  • Click on the project name
Export MongoDB documents to CSV using atlas
Database deployments

Here, click on Connect button right side of the cluster named Cluster0.

  • Now, we connect the cluster using MongoDB shell.
Export MongoDB documents to CSV file using atlas
Connect to cluster0 using MongoDB shell
  • Open MongoDB shell and run the above command to start MongoDB server
mongo "mongodb+srv://cluster0.mrbxt.mongodb.net/myFirstDatabase" --username test123
Export MongoDB to CSV by using atlas
Start MongoDB server using the atlas

Here, when you execute the above command then it will ask you to enter a password so write the password to start the MongoDB server.

Note that, when you created a new user at the time of the project creation, you created a password there so write that password. It is required to start the server.

  • These below documents we are going to export into CSV
Export MongoDB to CSV file using atlas
Export documents to CSV using MongoDB atlas
  • Now we use the mongoexport tool to export the documents into CSV file
mongoexport --host cluster0-shard-00-01.mrbxt.mongodb.net:27017 --db starwars --collection test --type csv --fields StudentId,StudentName,age --out E:\test.csv --authenticationDatabase admin --ssl --username test123 --password test123

Here, with the mongoexport tool we write full hostname for connecting to the server then we specify the database and collection name starwars and test respectively.

Then specify the file type as we are exporting into CSV and also write the field name. After that write the path or location where we store the export.csv file. In the last, we specify –authenticationDatabase admin that is mandatory and writes –SSL username and password. See the below image for reference.

Export MongoDB documents to CSV file using atlas example
Export MongoDB documents to CSV file using the atlas

We successfully exported the documents of MongoDB into a CSV file using the atlas.

Read: MongoDB drop collection

MongoDB export documents to CSV using python

In this topic, you will learn how you can export the collection of MongoDB into CSV with the help of python.

For this python provides the Pandas library that gives you the ability to export MongoDB documents into different formats like CSV, JSON, and HTML.

You will more understand with the help of Example so here I am taking an example that will help you to understand the process behind exporting the document into CSV.

Prerequisites:

  • Install the MongoDB pymongo driver
pip3 install pymongo

This command will help you to install the pymongo driver that is used to make connections between python and MongoDB.

  • Install the Pandas library
pip3 install pandas

This command will install the Pandas library that provides various methods. With this help, we export the collection.

Example:

To understand the example go through the following steps, step by step:

Import all required library

import pymongo
import pandas
  • Here, Pymongo is used to make connections between python and MongoDB
  • The pandas library is used for export functionality (convert to CSV).

Make a new MongoClient instance

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["Company"]
col= db["persons"]
  • After that, to access MongoDB use port number and IP address with the help of MongoClient class
  • Access database and collection Company, persons respectively.

Retrieve documents with an API call

cursor = col.find()
  • This is used to make an API call to the MongoDB server using a collection object.
  • The find() is used to return every document from the MongoDB collection.

Count the number of documents in the MongoDB collection

print ("total docs in collection:", col.count_documents( {} ))

mongo_docs = list(cursor)
  • Here, the print() function is used to print the total number of documents in a MongoDB collection.
  • And count_documents() is a method that is used to count total documents in a collection.
  • To access all the MongoDB documents with the python list() function.

Store documents in a Dataframe object

# create an empty DataFrame obj for storing Series objects
docs = pandas.DataFrame(columns=["_id","Name","Age","Gender"])
  • With the help of this store the documents in a Dataframe object.
  • Make a Dataframe object and provide the column name toas per the documents stored in MongoDB collection.

Iteration through the function and pandas objects creation

for num, doc in enumerate( mongo_docs ):
    # convert ObjectId() to str
    doc["_id"] = str(doc["_id"])
    # get document _id from dict
    doc_id = doc["_id"]
    # create a Series obj from the MongoDB dict
    series_obj = pandas.Series(doc, name=doc_id)
    # append the MongoDB Series obj to the DataFrame obj
    docs = docs.append( series_obj )
  • Here, we used the enumerate() function for iteration
  • Make a python string of documents by converting objecteId() to str() also save it so later you can use it.
  • After that, construct a series object and name the same as the string of doc_id.
  • Use append() method to add it to the DataFrame array.

Export MongoDB documents to a CSV file

# export MongoDB documents to CSV
csv_export = docs.to_csv(sep=",") # CSV delimited by commas
print ("\nCSV data:", csv_export)

# export MongoDB documents to a CSV file
docs.to_csv("export.csv", index=False)
  • To export the MongoDB documents to CSV we use to_csv() method.
  • After that conduct an export MongoDB documents python, the conversion format is automatic for whichever file type you choose based on the call method.
  • here, index=False to remove the index column becase sometimes its automatically generate.

Note that, the file path is used to place the file in your chosen directory otherwise it stays in the same directory as your script.

Export MongoDB collection to CSV python
Export MongoDB collection to CSV using python

Output:

After executing the above code output will generate as :

Export MongoDB collection to CSV using python
Output

You can also see the export.csv file as well to check the data.

Export MongoDB collection to CSV using python example
export.csv file

This way you can easily export the documents of MongoDB into CSV using python.

You may also like to read the following MongoDB tutorials.

In this tutorial, you have learned How to Export MongoDB documents into CSV. We will also cover this with different operations and examples. These are the following topics that we covered in this tutorial:

  • MongoDB export to CSV with query(command)
  • Export MongoDB documents to CSV with example
  • MongoDB export to CSV without header
  • MongoDB export to CSV using compass
  • Export MongoDB documents to CSV using atlas
  • Export MongoDB collection to CSV using python

Leave a Comment