MATLAB Answers

How to convert csv file containing MAC addresses to JSON array

15 views (last 30 days)
Alex Owens
Alex Owens on 9 Jan 2019
Commented: Guillaume on 9 Jan 2019
I have multiple csv files with a long list of MAC addresses and RSS (Received signal strength) values, as shown below.
34:a8:4e:fd:3c:50, -53
b0:7f:b9:bc:f0:e0, -61
b0:7f:b9:bc:f1:01, -82
34:a8:4e:fc:13:53, -58
b0:7f:b9:bc:f0:42, -79
34:a8:4e:fd:3c:53, -53
...
I need to convert these csv files in to JSON arrays in the format below:
"wifi":{ "34:a8:4e:fd:3c:50":-53,
"b0:7f:b9:bc:f0:e0":-61,
"b0:7f:b9:bc:f1:01":-82,
"34:a8:4e:fc:13:53":-58,
"b0:7f:b9:bc:f0:42":-79,
"34:a8:4e:fd:3c:53":-53,
... }
Currently I am trying to enter the values as a struct but MATLAB tells me MAC addresses that have ':' or start with a number, are not appropriate field names. Is there a way around this? Or can anyone suggest a way of converting the files?

Accepted Answer

Guillaume
Guillaume on 9 Jan 2019
Edited: Guillaume on 9 Jan 2019
I'm assuming you want to use jsonencode to do the encoding. To be honest, considering the simplicity of your output, you could simply encode it yourself.
Using jsonencode you will have to store your data in a map instead of a structure:
wifidata = readtable('20190108_113507_WifiSensor(1).csv', 'Delimiter', ','); %or however you're reading your input file
wifidata = containers.Map(wifidata{:, 1}, wifidata{:, 2}); %convert to a map
json = jsonencode(struct('wifi', wifidata)) %convert map to json
Bypassing jsonencode entirely:
wifidata = readtable('20190108_113507_WifiSensor(1).csv', 'Delimiter', ','); %or however you're reading your input file
json = compose('{"wifi":{%s}}', strjoin(compose('"%s":%d', string(wifidata{:, 1}), wifidata{:, 2}), ','))
Either way, note that matlab will not be able to read that json back with mangling the mac addresses as jsondecode does not have an option to read the json in map. It's unfortunately a limitation of matlab that I complained about a long time ago.

  3 Comments

Alex Owens
Alex Owens on 9 Jan 2019
is there a way to replicate the function readtable in older versions (e.g. 2007)?
Guillaume
Guillaume on 9 Jan 2019
No, you'll have to use textscan and figure out the format yourself (not hard for your file). However, R2007 did not have any jsonencode / jsondecode either, it was only introduced in R2016b. So if you don't have readtable you also don't have any way to encode json other than external libraries or your own encoder and your whole question is then puzzling.

Sign in to comment.

More Answers (1)

GT
GT on 9 Jan 2019
I did the following:
  1. Used Import Wizard, generated the script belo
  2. I am on R2018b, so I used the function jsonencode at the end to create the jsontext
%% Import data from text file.
% Script for importing data from the following text file:
%
% /Users/Gareth/Documents/MATLAB/MATLABAnswers/mytemp/20190108_113507_WifiSensor(1).csv
%
% To extend the code to different selected data or a different text file,
% generate a function instead of a script.
% Auto-generated by MATLAB on 2019/01/09 11:03:01
%% Initialize variables.
filename = '20190108_113507_WifiSensor(1).csv';
delimiter = ',';
%% Format for each line of text:
% column1: text (%s)
% column2: double (%f)
% For more information, see the TEXTSCAN documentation.
formatSpec = '%s%f%[^\n\r]';
%% Open the text file.
fileID = fopen(filename,'r');
%% Read columns of data according to the format.
% This call is based on the structure of the file used to generate this
% code. If an error occurs for a different file, try regenerating the code
% from the Import Tool.
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'TextType', 'string', 'ReturnOnError', false);
%% Close the text file.
fclose(fileID);
%% Post processing for unimportable data.
% No unimportable data rules were applied during the import, so no post
% processing code is included. To generate code which works for
% unimportable data, select unimportable cells in a file and regenerate the
% script.
%% Create output variable
WifiSensor2 = table(dataArray{1:end-1}, 'VariableNames', {'Mac','Var'});
%% Clear temporary variables
clearvars filename delimiter formatSpec fileID dataArray ans;
%% jsonencode
jsonencode(WifiSensor2)
This gives the following:
'[{"Mac":"34:a8:4e:fd:3c:50","Var":-50},{"Mac":"34:a8:4e:fc:13:53","Var":-55},{"Mac":"34:a8:4e:fd:3c:54","Var":-50},{"Mac":"34:a8:4e:fc:5b:e0","Var":-71},{"Mac":"34:a8:4e:fc:5b:e1","Var":-71},{"Mac":"34:a8:4e:fc:5b:e3","Var":-71},{"Mac":"34:a8:4e:fd:3c:53","Var":-50},{"Mac":"4c:5e:0c:04:f9:ac","Var":-62},{"Mac":"34:a8:4e:fc:13:50","Var":-56},{"Mac":"b0:7f:b9:bc:f0:e1","Var":-59},{"Mac":"b0:7f:b9:bc:f0:e0","Var":-59},{"Mac":"00:1d:aa:ad:b3:00","Var":-41},{"Mac":"00:1d:aa:ad:b3:01","Var":-41},{"Mac":"1a:60:24:67:3f:d5","Var":-70},{"Mac":"78:8a:20:51:33:f9","Var":-70},{"Mac":"7a:8a:20:51:33:f9","Var":-73},{"Mac":"00:1d:7e:42:e0:ba","Var":-58},{"Mac":"b0:7f:b9:bc:f0:f0","Var":-69},{"Mac":"b0:7f:b9:bc:f0:f2","Var":-70},{"Mac":"78:8a:20:52:33:f9","Var":-82},{"Mac":"7a:8a:20:52:32:62","Var":-85},{"Mac":"7a:8a:20:52:33:f9","Var":-81},{"Mac":"78:8a:20:52:32:62","Var":-83},{"Mac":"34:a8:4e:fc:5b:ef","Var":-85},{"Mac":"34:a8:4e:fc:5b:ee","Var":-86},{"Mac":"34:a8:4e:fc:5b:ec","Var":-85},{"Mac":"34:a8:4e:fc:13:5f","Var":-62},{"Mac":"34:a8:4e:fc:13:5c","Var":-63},{"Mac":"34:a8:4e:fc:13:5b","Var":-62},{"Mac":"b8:62:1f:e4:d9:3f","Var":-77},{"Mac":"b8:62:1f:e4:d9:3b","Var":-77},{"Mac":"b8:62:1f:e4:d9:3c","Var":-77},{"Mac":"34:a8:4e:fd:60:8f","Var":-85},{"Mac":"34:a8:4e:fd:60:8c","Var":-85},{"Mac":"34:a8:4e:fd:3c:5f","Var":-64},{"Mac":"34:a8:4e:fd:3c:5b","Var":-65},{"Mac":"34:a8:4e:fd:3c:5c","Var":-64},{"Mac":"b0:7f:b9:bc:f0:e2","Var":-59}]'

  1 Comment

Guillaume
Guillaume on 9 Jan 2019
If you're going to use a table, using readtable would be a lot simpler than parsing the file yourself.

Sign in to comment.

Sign in to answer this question.

Tags


Translated by