How to convert csv file containing MAC addresses to JSON array

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?

[Alex Owens]

note: currently matlab imports the MAC addresses as text data

[KSSV]

Attach your csv file. If big..atleast a small part which is shown above.

[Alex Owens]

attached ..

Answer 1

[Guillaume]

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.

[Gareth]

Indeed this is a more elegant solution:)

[Alex Owens]

is there a way to replicate the function readtable in older versions (e.g. 2007)?

[Guillaume]

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.

Answer 2

[Gareth]

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}]'

[Guillaume]

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