How to make JSON file from existing MS Excel file

Target audience: MS Excel users
Used tools: MS Excel
What’s the purpose: Make JSON file from existing MS Excel file

This will be a story about how to make JSON file from existing Excel file. Need to do this arises when i try to load user’s data in Mayan EDMS system using API interface of the system. I can get some user’s data in Excel file, add another data to this file, but to load these data i need to represent these data in JSON format. There are two possibilities, to resolve these problem :

1. To use some kind of external utility
2. To convert data myself

Here is a sample solution by second method . Below is a picture of Excel file with user’s data :

The resulting JSON file have the below structure (this structure must correspond to structure for API interface of Mayan EDMS system for adding user’s data) :

[{
	"first_name":"Janet",
	"email":"some@any.com",
	"last_name":  "Brown",
	"password":"somePass",
	"username":"user1"
 },
 {	"first_name":"Leo",
	"email":"some@any.com",
	"last_name":  "Fisher",
	"password":"somePass",
	"username":"user6"
}]

Here the fields email and password have the same value – if you wish to enter different values for them, you can add additional columns for their values and read them from these columns. In first 3 columns are data, received by some report from external application. In column D are data for structure of JSON file, in column E are first name of users from column C and in column F are are last name of users from column C – in cell C2 the formula is :

=LEFT(C2,FIND(” “,C2,1)-1)

to read the first name, for cell D2 formula is

=TRIM(MID(C2,FIND(“#”,SUBSTITUTE(C2,” “,”#”,2))+1,255))

to read the last name .

In column G are the resulting data in JSON structure. To make a JSON file we simply copy range G2:G7 and paste in some text editor – like Notepad++ or Notepad. Formula in cell G3 for making JSON record is :

=CHAR(123)&$D$1 & CHAR(34) &E3 & CHAR(34) & CHAR(44) & $D$2 & CHAR(34) & “some@any.com” & CHAR(34) & CHAR(44) & $D$3 & CHAR(34) & F3 & CHAR(34) & CHAR(44) & $D$4 & CHAR(34) & “somePass” & CHAR(34) & CHAR(44) & $D$5 &
CHAR(34) & A3 & CHAR(34) & CHAR(125) & CHAR(44)

Here is important to note that the first row – cell G2, have to start
with [ – CHAR(91) before { – CHAR(123) and the last row – cell G7 –
to end with ] – CHAR(93) instead of , – CHAR(44) !

And as final step we can save these data from column G as JSON file in Notepad++ !