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.