Opening MMD format in Excel

Introduction

A couple of days ago, a friend of a physics student wrote to me with a request to help open the data taken from laboratory equipment in Excel and threw me several files of a strange .mmd format. The Internet, on the request “how to open mmd”, gave out only dancing anime girls (MikuMikuDance) and the developer’s site mangodata, from which it was impossible to download the necessary software. Of course, not everyone will face a similar problem, but a precedent appeared in my face, which means that the Internet needs an article “Opening MMD format in Excel”.

Step by step guide

For those who just need to open the file without going into details. If you are familiar with Google Colab, you will only be interested in the second item. I tried to write in as much detail as possible, demonstrating every action. It is best to read the entire manual before proceeding with the steps.

1. Go to Google Colab

Google Colab is a convenient Python code execution environment. We are not afraid if we are afraid. Python skills (and indeed a programmer in general) are not required from you. I am not a python programmer myself. I use it only when I need to quickly solve a problem using code.
Follow the link https://colab.research.google.com

2. Download the colab notebook or create it yourself

You can download the file from my Google Drive here:

https://drive.google.com/file/d/1lA3-1Z1kiVmlrUsrv-THvvrT8bRcZMHn/view?usp=sharing

For those who are afraid to download incomprehensible files from the Internet, below will be a guide on how to create such a notebook on your own.

To open the MMDtoCSV.ipynb file in Colaba, click Download in the window from the screen above or File – Load Notepad in the main menu.

You can also not download my file, but just copy the finished code into the collab. To do this, click + Code

Copy this code into the created block:

mmd_file_name = "file_name.mmd" # write your mmd file name here
mmdfile = open(mmd_file_name,"r")
mmddata = mmdfile.read()
startpos = mmddata.find('"data":[[')+8
endpos = mmddata[startpos:].find(']]')+1
data = mmddata[startpos:endpos]
data = data.replace('],[','n')
data = data.replace('[','')
data = data.replace(']','')
csvfile = open("csvfile.csv","w")
csvfile.write(data)
csvfile.close()

3. Load your file into the collab and run the code

Drag your mmd file to the window Files

In the form below, click OK.

In the very first line of code, we change the file name to our own.

Let’s run the code. To do this, you can press Ctrl + F9.

4. Download and open the converted file

After executing the code in Files csvfile.csv file will appear

If this does not happen, refresh the folder by clicking the appropriate button.

Download the resulting file and open it in Excel (Data – From CSV file).

5. Enjoy the result

What did we just do?

A section for beginners with no knowledge of data formats, but anxious to get an answer to this question.

The mmd file actually represents data in the format of a JSON object. You can read more about JSON here. In short, our file contains a set of key-value pairs. This is how the structure of a regular mmd file looks like:

//пример mmd-файла
{
	"author":"User",
	"recordingDevice":"Windows NT 10.0",
	"date":1636465537725,"sampleRate":100,
	"configuration":{
				"decimals":null,
				"movingAverage":25,
				"mode":null,
				"measurementType":"continuous",
				"pointMeasurementNumbers":null,
				"diagramType":"line",
				"xAxis":null,
				"xAxisDeviceId":null,
				"lastMeasurement":null,
				"additionalYAxis":null,
				"radioactivityGateTime":1,
				"dropCounterVolume":0.05,
				"dropCounterTotalVolume":1
	},
	"channels":[{"id":1,"uid":"60-E9:63:CD:A8:07:84-1","sensor":{"id":60},"unit":"V","color":"#CC5045"},{"id":1,"uid":"60-EA:75:68:B2:3C:0A-1","sensor":{"id":60},"unit":"V","color":"#E49128"}],
	"data":[[0,0.005,-0.149],[0.01,-0.002,-0.134],[0.02,0.005,-0.134]],
	"annotations":null
}

Metadata (author, date and others) we are not interested in this case. All you need to do is retrieve the content stored under the key data… Note that the data is stored in a two-dimensional array.

Find the substring ‘”data” in the file:[[ ‘ и запоминаем ее позицию. Затем ищем первое (начиная с data:[[) вхождение подстроки ‘ ]]’- two square brackets in a row define the end of a two-dimensional array:

startpos = mmddata.find('"data":[[')+8
endpos = mmddata[startpos:].find(']]')+1

We remember all objects of a two-dimensional array:

data = mmddata[startpos:endpos]

This is how the contents of the data variable looks like:

[0,0.005,-0.149],[0.01,-0.002,-0.134],[0.02,0.005,-0.134]

The simplest format to convert the resulting sequence is CSV. In this format, columns are separated by commas and rows are separated by a newline character.

Within one line in our file, the data is already separated by commas. You can separate the rows from each other by replacing ‘],[ ‘ на символ переноса строки ‘ n ‘.

data = data.replace('],[','n')

//результат
[0,0.005,-0.149
0.01,-0.002,-0.134
0.02,0.005,-0.134]

Remove the two remaining brackets around the edges’ [ ‘ и ‘ ] ‘and save the final result in csv format.

data = data.replace('[','')
data = data.replace(']','')
csvfile = open("csvfile.csv","w")
csvfile.write(data)
csvfile.close()

Now you can export data to Excel.