json format question

1

I am having a little trouble with my program and hope you can point me in the right direction. I wrote a simple program to allow our distributors to do pricing on their phone. We had a price increase this week and I am trying to update the json file to recompile the program.

I converted the prices from an Excel spreadsheet to json, but my program is not reading them. Looking at the old json file, it is formatted like this:

{
_ “fields”: [“Attribute”, “Description”, “UOM”, “Price”],_
_ “data”: [_
_ [“6100550-ATT”,“Attribute to use to modify p/n 6100550”,“EA”,113.45 ],_
_ [“ADDSR”,“Additional Layer of SR”,“SQIN”,0.2 ],_

All the online converters I am trying to use are formatting the json file like this:

[
_ {_
_ “Attribute”: “6100550-ATT”,_
_ “Description”: “Attribute to use to modify p/n 6100550”,_
_ “UOM”: “EA”,_
_ “Price”: 115.7_
_ },_
_ {_
_ “Attribute”: “ADDSR”,_
_ “Description”: “Additional Layer of SR”,_
_ “UOM”: “SQIN”,_
_ “Price”: 0.21_
_ },_

Am I correct that this is where my problem is? If so, do you know of a converter that will format the data correctly?

2

Have you checked the resulting JSON in an online site, like https://jsonlint.com/?

(You’ll have to use the version of the JSON without the _ characters).

3

Actually all those underscores got inserted when I tried to use italics when posting. Here is how it should look.

{
“fields”: [“Attribute”, “Description”, “UOM”, “Price”],
“data”: [
[“6100550-ATT”,“Attribute to use to modify p/n 6100550”,“EA”,113.45 ],
[“ADDSR”,“Additional Layer of SR”,“SQIN”,0.2 ],

All the online converters I am trying to use are formatting the json file like this:

[
{
“Attribute”: “6100550-ATT”,
“Description”: “Attribute to use to modify p/n 6100550”,
“UOM”: “EA”,
“Price”: 115.7
},
{
“Attribute”: “ADDSR”,
“Description”: “Additional Layer of SR”,
“UOM”: “SQIN”,
“Price”: 0.21
},

4

So what happens when you paste the text into https://jsonlint.com/?

5

Not that it should make any difference which JSON validation tool you use, but I enjoy using this one for my needs, and it says the JSON snippet below is valid.

Kind regards,
Doug

{
  "fields": [
    "Attribute",
    "Description",
    "UOM",
    "Price"
  ],
  "data": [
    [
      "6100550-ATT",
      "Attribute to use to modify p/n 6100550",
      "EA",
      113.45
    ],
    [
      "ADDSR",
      "Additional Layer of SR",
      "SQIN",
      0.2
    ]
  ]
}
6

OK, back from break now. I pasted the text into a validator and got:

The JSON input is valid according to RFC 4627 (JSON specfication).

The JSON input is valid in JavaScript.

7

Maybe we need to go back to the original assumption: why do you think it is not being read properly?

8

This is the error I’m getting:

Uncaught TypeError: Cannot read property ‘0’ of undefined.
line 1584 column 23

Line 1584 is the third line in this function:

function getPrice(key) {
    for (i = 0; i <= Len(data) - 1; i++) {
        product = data[i];
        if (product[0] == key) {
            return product[3];
        }
    }

When I check the value of i, it is 0 meaning the error is being thrown the first time through the loop.

9

How are you setting the value of data?

10 
Function loadComplete(result, status)
  console.log(result, status)
  data = result.data
End Function

The function worked with the previous json file formatted as shown, so I am not suspecting any error in the function itself.

11

Put a console.log(data) as the second line of getPrice. What’s the output?

12

Same error, same place.

Does anyone know of a json generator that will generate the json file in the first format? That seems like the simplest solution.

13

What’s the output of the new console.log(data) statement? It’s not what you are assuming.

14

With the new json file it is undefined. With the old json file it is Array(1996).

15

That means the value of data hasn’t been set. The problem doesn’t seem to be in the format of the incoming data - it’s what happens to it once you read it in. Time to put in some more tracing to see what happens to data.

16

Why does the one file work and the other doesn’t?

17

The problem is that data does not have a value. I don’t think the problem is with the file - it’s how are you handling it in your program.

From what you’re saying, it’s fine when you read it in, but not there when you call getPrice().

18

When the json file is in this format, the program works fine:

{
  "fields": ["Attribute", "Description", "UOM", "Price"],
  "data": [
  ["6100550-ATT","Attribute to use to modify p/n 6100550","EA",113.45  ],
  ["ADDSR","Additional Layer of SR","SQIN",0.2  ],
  ["ADJTSTAT","Stock Adj. T-Stat","EA",71.4  ],
  ["AH125F","1/8 FPT Adder for AH Inlet/Outlet","EA",12.95  ],
  ["AH125M50","1/8\" Brass MPT for 1/2\" AH","EA",25.45  ],

When the json file is in this format, the program does not work:

[
 {
   "Attribute": "6100550-ATT",
   "Description": "Attribute to use to modify p/n 6100550",
   "UOM": "EA",
   "Price": 115.7
 },
 {
   "Attribute": "ADDSR",
   "Description": "Additional Layer of SR",
   "UOM": "SQIN",
   "Price": 0.21
 },
 {
   "Attribute": "ADJTSTAT",
   "Description": "Stock Adj. T-Stat",
   "UOM": "EA",
   "Price": 74.26
 },
 {
   "Attribute": "AH125F",
   "Description": "1/8 FPT Adder for AH Inlet/Outlet",
   "UOM": "EA",
   "Price": 13.47
 },
 {
   "Attribute": "AH125M50",
   "Description": "1/8\" Brass MPT for 1/2\" AH",
   "UOM": "EA",
   "Price": 26.47
 },

The program is the same in each instance, only the json file is changed. The program should be handling the same with either file. Why does it handle one differently?

19

First, a tip: surround any code in triple back ticks so it formats as code.

Please do some console.log stuff to see what the actual value in data is.

20

Ok. I got the problem fixed and it had nothing to do with my code. I found a json converter the formatted the data properly and it worked fine with the new json file.

This is the converting site I used: http://www.convertcsv.com/csv-to-json.htm. I then had to set the output parameters as follows:

  • If to JSON Array, create array for column names with name = fields and Data name = data. This one has to be checked.

  • MongoDB Mode Each record on one line, no commas in between. This one has to be checked for the numbers to be formatted properly.

  • Finally, you have to choose to output as a json Array.

Once I did this, the json file worked perfectly.