Quote from BGUK on April 13, 2019, 7:31 pm

Please advise how I process a json file containing multiple records. For example, a loop which would display each record in an alertbox.

Quote from luishp on April 14, 2019, 7:25 am

Hi @bguk,

There is a very simple sample app included with VisualNEO Web install.
Please open the "neoPhpSimplestDataBase.neoapp" sample project that you will find into "Sample Apps > neoPhpSamples" folder and follow the instructions on how to run it.

The data from the database is loaded as a JSON with multiple records.
User interface buttons allow you to navigate forward and backward with very few commands.

Let me know if you have any doubt.

Quote from BGUK on April 15, 2019, 6:18 pm

Thanks Luis.

The neoPhpSimplestDataBase app is very useful but the neoPhpExecSql action does not describe how the database content is translated to json format. I have the app working successfully in both localhost and my Web Server.

Your app load-json.neoapp loads individual json records from separate files and app neoTableTutorialPart1.neoapp loads from in-line json. I would like to load a multi record json file exported from Excel into a VisualNeo container but need further advise.

 

 

 

Quote from luishp on April 15, 2019, 9:44 pm

the neoPhpExecSql action does not describe how the database content is translated to json format

The conversion is done using PHP. All the PHP functions are into neofunctions.php, a special file added to your publication folder from neoPhp plugin that allows neoscript to execute php functions.

Can you share your .json file as it is exported from Excel so we can take a look?

Thanks!

Quote from BGUK on April 17, 2019, 12:33 pm

Below is the json data exported from an Excel table using VBA :-

[
{
"CUSTOMER":"Widget Corp"
,
"REGION":"NORTH"
,
"ORDER DATE":"13/04/2018"
,
"SALES":"55815"
,
"MONTH":"April"
,
"YEAR":"2018"
}
,
{
"CUSTOMER":"VisualNeoWeb"
,
"REGION":"SOUTH"
,
"ORDER DATE":"21/12/2018"
,
"SALES":"94908"
,
"MONTH":"December"
,
"YEAR":"2018"
}
,
{
"CUSTOMER":"Demo Company"
,
"REGION":"EAST"
,
"ORDER DATE":"15/02/2018"
,
"SALES":"57088"
,
"MONTH":"February"
,
"YEAR":"2018"
}
,
{
"CUSTOMER":"123 Warehousing"
,
"REGION":"WEST"
,
"ORDER DATE":"14/05/2018"
,
"SALES":"56539"
,
"MONTH":"May"
,
"YEAR":"2018"
}
]

Hope this helps.

Quote from Gaev on April 18, 2019, 4:49 am

@BGUK:

Please advise how I process a json file containing multiple records. For example, a loop which would display each record in an alertbox.

1) Can you tell us where your App will be running ? ... from a local drive (like when testing) or at some url (http/https) ?

Browsers are very picky (and rightly so) for security reasons ... so the first thing is to see if you can use FileToVar or LocalFileToVar

See if you can use one of these commands to load into a variable ... and then display the variable inside a Container.

2) Notice that all your data is wrapped inside square brackets [ ... ] , which means that it is an array ... f.y.i. here are examples of arrays ...

This is an array of numbers
[1,24,367,5432]

This is an array of strings
["Apple","Banana","Cherry","Fig","Grape"]

This is an array of JSON objects
[{"userId": 1,"id": 4,"title": "et porro tempora","completed": true}, {"userId": 2,"id": 74,"title": "cata and dogs","completed": false}]

3) When you load the file of data downloaded from the Excel sheet, you get an array of JSON objects; each row (record) of the spreadsheet is a JSON object ... i.e. stuff wrapped within curly brackets { ... }

So, if the variable containing the contents of your file is [mydata], and you want to extract the fourth row/record (array item numbers start at zero) ...

SetVar [mydataRecord] "[mydata(3)]"

... and if you want to loop through them ...

ArrayLen "[mydata] [mydataLen]
SetVar "lastItem]" "[mydataLen]-1"

Loop 0 [lastItem] [x]
   SetVar [mydataRecord] "[mydata(x)]"
   ... your command to display item here
EndLoop

4) As you loop through the records, [mydataRecord] will contain the stringified format of the JSON object ... so the next step is to separate (parse) the individual key/value pairs ... e.g. ...

SetVar "[thisCustomer]" "[mydataRecord('CUSTOMER')]"

5) You can place your AlertBox command inside the loop, but beware that the loop does not stop after popping up the box ... so you will end up with one such box for each row/record ... all piled on top of each other ... in reverse order.

Hope this helps; otherwise ask more questions.

Quote from BGUK on April 18, 2019, 7:02 pm

Thanks Gaev for the useful JSON information.  I have used the following code but without success.

FileToVar "https://www.dovesoft.uk/visualneo/multirecord.json" [myvar]

SetVar [fname] [myvar('fname')]

.AlertBox "DoveSoft" "[myvar('fname')]" ""

ArrayLen [myvar] [mydataLen]
SetVar "[lastItem]" "[mydataLen]-1"

Loop 0 [lastItem] [x]
   SetVar [mydataRecord] "[myvar(x)]"
   AlertBox "DoveSoft" "[mydataRecord]" ""
EndLoop

If I substitute singlerecord.json for multirecord.json and remove the AlertBox comment, it works! I have been testing using the VisualNeo test environment.

Finally, how do I assign the json array value to a container?

Quote from Gaev on April 19, 2019, 6:48 pm

BGUK:

If I substitute singlerecord.json for multirecord.json and remove the AlertBox comment, it works!

This is because you have this command ...

SetVar [fname] [myvar('fname')]

... in the wrong place/order ... you have to separate items in the array first ... then extract the key/values from each array item

I have been testing using the VisualNeo test environment.

Assuming that you are able to download contents of an internet located file from your test environment (as evidenced by your success with just one array item) ... try this ...

FileToVar "https://www.dovesoft.uk/visualneo/multirecord.json" [myvar]

...get number of items in the array
ArrayLen [myvar] [mydataLen]

...subtract one to account for items being referenced 0,1,2, etc.
SetVar "[lastItem]" "[mydataLen]-1"

...now loop through all items
Loop 0 [lastItem] [x]
  SetVar [mydataRecord] "[myvar(x)]"
  ... for now just show the contents of each the json object (string format)
  AlertBox "DoveSoft" "[mydataRecord]" ""
EndLoop

... let us know how it went.

Once you get expected results, you can tell us what you want to do with the individual key/value pairs (fname, lname, email etc.) for each item (record) in the array.

Finally, how do I assign the json array value to a container?

Are you asking about displaying the array's contents in a neoTable within a Container ?

Quote from BGUK on April 20, 2019, 12:22 pm

Gaev:

Your solution does not work. I replaced the https://www.dovesoft.uk/visualneo/multirecord.json content with the first 5 records of the table exported from your neoTableTutorialPart1.neoapp. However, this still returns the number of items in the array as 0.

Are you asking about displaying the array's contents in a neoTable within a Container ?

Yes as at present I am using in-line json similarly to the neoTableTutorialPart1.neoapp.

If I use an internet located file, do I need to recompile my app each time the source file content is updated?

Quote from Gaev on April 20, 2019, 2:59 pm

@BGUK:

I replaced the https://www.dovesoft.uk/visualneo/multirecord.json content with the first 5 records of the table exported from your neoTableTutorialPart1.neoapp. However, this still returns the number of items in the array as 0.

I looked at this json file ... it contains additional information (about Column Headers) that makes the format incompatible with what was expected for loading the content.

My suggested code was based on the file content you had before.

Your solution does not work.

I am going to be out all day today. Tomorrow, I will make up a .neoapp script file that uses your multirecord json file.

Please replace the content of this json file with what you had before (presumably exported from Excel or other platform).

Yes as at present I am using in-line json similarly to the neoTableTutorialPart1.neoapp.

Does that give you a table inside a Container object ?

If I use an internet located file, do I need to recompile my app each time the source file content is updated?

As long as the structure/format of the source content remains the same, you do NOT have to recompile your app,  ... by that, I mean that the keys (columns) like Customer, Region etc. etc. remain the same (note that these are case sensitive; Customer is different from customer) ... only the values like "Widget Corp", "North" etc. can change.

 

Quote from BGUK on April 20, 2019, 5:09 pm

Gaev:

Your support is appreciated.

1. File https://www.dovesoft.uk/visualneo/multirecord.json has been restored to the Excel content.
2. The neoTableTutorialPart1.neoapp uses the following code to populate a container object:-

   ... Reset any previous column definitions
   neoTableResetColumns "BasicTableContainer"
   
   ... setup columns; IMPORTANT: column numbers MUST start at 1
   neoTableSetColumn "BasicTableContainer" 1 "id" "Id" "" false false false ""
   neoTableSetColumn "BasicTableContainer" 2 "name" "First Name" "" false false false ""
   neoTableSetColumn "BasicTableContainer" 3 "surname" "Last Name" "" false false false ""
   neoTableSetColumn "BasicTableContainer" 4 "age" "Age" "" false false false ""
   
   ... define user permissions
   neoTableInitTable "BasicTableContainer" "" 0 "Table" false false false false false ""
   
   ... load Data
   neoTableLoadData "BasicTableContainer" [mydata]

   3. If the source content values from Excel change on a daily basis, the app must be recompiled and uploaded to my web server on a daily basis?

Quote from Gaev on April 21, 2019, 10:06 pm

@BGUK:

I have attached a copy of the .neoapp file that I promised.

1) As I had said in an earlier email, in order to access files on the internet, your App has to be running with the http/https protocol (NOT the file protocol used when you test the application by clicking on the Chrome Icon inside VisualNEOWeb).

Even after compiling the project, running the neoPhp server and entering localhost in the Browser's address box, I could not get your json file to be loaded from here ... https://www.dovesoft.uk/visualneo/multirecord.json

In order to access this file, you have to configure your server to override the regular CORS security restrictions imposed by modern Browsers.

Perhaps this file is only available from Apps created on your domain and you might be able to access it by loading your compiled application folder to the same server/folder as the file.

After uploading the app to the same server/folder, try and load it from the button on Page1.

2) In case, you do not have the ability to upload your app and/or override the CORS settings, I set up a Text Entry Box on Page 2 ...

- after starting your app, copy the contents of the json file (just open the link in another tab of the Browser, select everything and do a Ctrl+C key combo)
- then return to the tab running Page 2 of the app, click on the Text Entry Box and paste the data (Ctrl+v key combo)

3) Now you can follow the conversion in a step by step process ...

- on Page 2, click on the button labelled "Remove Array Wrapper Characters" ... the wrapping [ and ] characters will be removed (note that I had to use javascript code because the VisualNEOWeb commands have issues with the [ character, which is the wrapper character for variables).

- then click on the button labelled "Show JSON Strings" ... the resulting AlertBoxes will show you the contents of the individual (parsed) items in reverse order (a quirk in the platform)

- then go to Page 3 and click on the button labelled "Create mydata"

- finally, when you see the AlertBox with the message "All Done", click on the button labelled "Create Table" ... voila !!!

I hope this gives you an understanding of the conversion process.

Next Steps ...

1) Let us know if you were able to load the file on page 1; otherwise, at least you can display the table by copy/paste of the excel data.

2) If you are able to setup your json file to be accessed by anybody (on any domain), I can modify this sample app to show a shorter/easier process of loading its contents into [mydata] and showing it in a Table format.

Quote from BGUK on April 23, 2019, 7:35 pm

Apologies Gaev for not replying earlier.

1. After compiling your app and uploading to folder https://www.dovesoft.uk/BGUK on my web server, I too was unable to load file https://www.dovesoft.uk/visualneo/multirecord.json.

2. I then copied file multirecord.json to folder https://www.dovesoft.uk/BGUK and am able to load it into the app page 1. If you load file https://www.dovesoft.uk/BGUK/multirecord.json into Chrome you will notice it is in the format as exported from Excel. However, the LoadJSON action loads it in a different format when accessed from URL https://www.dovesoft.uk/BGUK and characters [object Object],[object Object],[object Object],[object Object] are loaded into page 2.

3. I successfully followed step 2 of your post by copying the contents of the multirecord.json file by returning to the tab running Page 2 of the app, clicking on the Text Entry Box and pasting the data. Page 3 then created the table.

4. Can you please modify this sample app to show a shorter/easier process of loading its contents into [mydata] and showing it in a Table format?

Quote from Gaev on April 23, 2019, 10:12 pm

@BGUK:

I just saw your post as I began to compose this post; so I will review/respond to your points in a follow up post tonight.

In the mean time, I have good news.

Thanks to @luishp, who set up this multi-record json file ... https://visualneo.com/filedownloads/multirecord.json ... with the correct CORS access parameters on his server ,,, you can now see how to load external json files (like the ones generated from your excel spreadsheet) in the attached (revised) .neoapp

on Page1, you can ...

- view the contents of this file in another tab of your browser (this just to see what it looks like)
- load its contents into the Container
- loop through the array items and see (in AlertBoxes) the values of fname and lname (in order 3,  2, 1 and 0)

On Page 2, you can ...

- copy the loaded content into [mydata] (not necessary if you load neoTable from the variable [loadedJSON])
- use the contents of [mydata] to setup a neoTable

Please view the code for each step.

Hope it helps you.

If you have problems loading your json file into this .neoapp, luishp advised me that in order to grant access to everybody (even when running from file://), you have to set up these lines in your .htaccess file ...

<Files "*.json">
Header set Access-Control-Allow-Origin "*"
</Files>

If you have authorization/skills to update your server, try this out.

Quote from luishp on April 24, 2019, 9:59 am

Please, note that this code only works on Apache Web Server (not useful for file:// protocol).
It must be on the .htaccess file located in the same folder than your .json.
.htaccess file is a special configuration file for Apache Web Server.

<Files "*.json">
Header set Access-Control-Allow-Origin "*"
</Files>

Also it is mportant to understand that CORS (Cross Origin Resource Sharing) is only something to worry about when your app is located in a different domain or server than your data, or if you are loading your assets through file:// (local files) protocol instead of http:// or https://

If you need to load any file within your local environment while developing, please use the included neoPHP Server tool as a web server. It makes unnecessary any configuration and serves all files through http:// as if your app were hosted in a hosting service.

Quote from BGUK on April 24, 2019, 4:34 pm

Thanks Gaev and Luishp.

I compiled Gaev's BGUK2 app and uploaded to my web server folder https://www.dovesoft.uk/BGUK2. As my web server runs Apache, I then uploaded Luishp's .htaccess file to the same folder and the app ran successfully.

To further test, I updated the Page1FileToFetch variable's initial-value property to https://dovesoft.uk/BGUK2/multirecord.json and the app again run successfully. I prefer to process  apps where my data is located in the same domain or server.

I also successfully tested in my local environment while developing, and used the included neoPHP Server tool as a web server. Incidentally, the local test also run successfully if not using the neoPHP Server tool.

Thanks again for your quick support.

Quote from Gaev on April 24, 2019, 8:24 pm

@BGUK:

I then uploaded Luishp's .htaccess file to the same folder and the app ran successfully.
Incidentally, the local test also run successfully if not using the neoPHP Server tool.

To my surprise (and that of @luishp), I found that I could access the file on the VisualNEO website during the design/development/testing process as well.

I thought that any of these overrides to the CORS restrictions could only be specified for request coming from url's that start with http/https; looks like Luis has found a way around :-))

 

I have added a Page0 in the latest revision of the sample .neoapp ...

It contains just 2 buttons ...

1) Fetch Data From File

Apart from the SetVar commands that provide feedback information, there is just one command of consequence (LoadJSON).

2) Show in neoTable Format

- it has the neoTable commands to define the rows and columns of the Table to be shown
- it uses the data fetched in the previous button directly (as is)

That's all.

Quote from thibeault911 on June 17, 2019, 3:13 pm

Hi, what if the data is an .txt file?

Thanks

Thibeault

Quote from luishp on June 17, 2019, 8:28 pm

@thibeault911 what do you mean exactly?
JSON data in a .txt file located on the server or local device?
Regards.