How to Export MongoDB Data as a CSV File

 

Sometimes we need to convert the non-structured MongoDB data into structured data and export it as a CSV file for being migrated to other applications, such as the database.
Below is collection score containing information of student scores. The collection has nested subdocument subject to record scores tables of all subjects. Now we are trying to export the data to a CSV file.

name  

age

province

subject

zhou gao gao

24

guang xi

{"name":     "Chinese","score": 80},
  {"name":   "Math","score": 84},
  {"name":   "English","score": 84},
  {"name":   "Chemic","score": 98}

li chao

16

shan dong

{"name":     "Chinese","score": 94},
  {"name":   "Math","score": 88},
  {"name":   "English","score": 75},
  {"name":   "Chemic","score": 73}

yang hao hao

26

guang xi

{"name":     "Chinese,"score": 85},
  {"name":   "Math,"score": 83},
  {"name":   "English ,"score": 64},
  {"name":   "Chemic,"score": 71}

We can use esProc SPL to first structuralize the data and then export it to a CSV file.

Directions:
1. Write SPL script scores.dfx:

A

B

1

=mongo_open("mongodb://localhost:27017/student")

/ Connect to MongoDB database

2

=mongo_shell(A1,"score2.find(,{_id:0})").fetch()

/ Get data from score collection

3

=A2.new(name, age, province, (r=subject.(score))(1): Chinese,r(2):     English,r(3): Math, r(4): Chemic)

/ Structuralize data by converting scores of each subject into a new   column

4

>file("D:\\tmp\\exp_data.csv").export@tc(A3)

/ Export the data as a CSV file

5

=A2.news(subject; name:subject,   score, A2.name, age, province)

/ Structuralize data by corresponding each subject to its scores

6

>file("D:\\tmp\\exp_data2.csv").export@tc(A5)

/ Export the data as a CSV file

7

>A1.close()

/ Close database connection

  2. Start debugging and execute the code. Below is the value of cell A3:

A3

name

age

province

Chinese

English

Math

Chemic

zhou gao gao

24

guang xi

80

84

24

8

li chao

16

shan dong

94

24

50

32

yang hao hao

26

guang xi

44

11

26

80

Value of cell A6:

A6

subject

score

name

age

province

Chinese

80

zhou gao gao

24

guang xi

Math

84

zhou gao gao

24

guang xi

English

24

zhou gao gao

24

guang xi

 

3. After the script is executed, two csv files are generated under D:\tmp directory.

undefined

A5 Since the “name” under “subject” is namesake with name field, it is renamed “subject”.

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.