Re: Can't get JSON data into Airtable through API

AirKV · ‎Jul 21, 2022

Hi all,

I pulled JSON data through an API that looks like this:

{'odata.metadata': 'https://opendata.cbs.nl/ODataApi/OData/80416ned/$metadata#Cbs.OData.WebAPI.UntypedDataSet',
 'value': [{'ID': 0,
   'Perioden': '20060101',
   'BenzineEuro95_1': '   1.325',
   'Diesel_2': '   1.003',
   'Lpg_3': '   0.543'},
  {'ID': 1,
   'Perioden': '20060102',
   'BenzineEuro95_1': '   1.328',
   'Diesel_2': '   1.007',
   'Lpg_3': '   0.542'},

I have created corresponding column names in my table. I have also read through the API docs and set up my Base ID / Table ID / API key. I managed to GET and POST requests into/from Airtable with dummy / hard coded data as presented in the API docs.

I have tried to POST the JSON data into Airtable in different ways, which resorted in all the error messages you can think of :grinning_face_with_big_eyes: .

So how do I structure the JSON data so that Airtable can interpret it?

Look forward to hearing from you

kuovonne · ‎Jul 21, 2022

Welcome to the Airtable community!

Did you see the information in the the API docs on the format of the data? Note that the format for the REST API can be slightly different from the format for the scripting, depending on the field type.

The format also varies depending on if you are creating versus updating a record, and if you are processing a single record or an array of records.

You say that you have manage to POST data to Airtable with the example data in the docs. I recommend you compare the format of your data to that of the example data, and try to get it to match, one field at a time. If you try to post several fields or records all at once, it is hard to tell which field is the one causing problems.

Bill_French · ‎Jul 22, 2022

This is where you have to get real chummy with the API docs as they will advise you concerning your base and target table(s).

Step 1 - layout the data table fields and set it up to receive your API data
Step 2 - open the API docs and select the target table
Step 3 - look closely at the create record example for the target table

The documentation will tell you exactly how the JSON should look when posting a new record. You will then need to transform your above data example into that new format before posting to the Airtable API.

AirKV · ‎Jul 24, 2022

Thanks for your replies. I am a Python beginner, so I hope there’s a guru who can help me out :slightly_smiling_face: ! I created a DataFrame that looks like this:

	time	chlorophyll	iron	nitrate
19	2022-07-24T17:00:00+00:00	1.29	0.01	0.80
20	2022-07-24T18:00:00+00:00	1.29	0.01	0.80
21	2022-07-24T19:00:00+00:00	1.29	0.01	0.81
22	2022-07-24T20:00:00+00:00	1.28	0.01	0.81
23	2022-07-24T21:00:00+00:00	1.28	0.01	0.81

Data types:

time            string
chlorophyll    float64
iron           float64
nitrate        float64

Then I create a dict of dicts that looks like this:

{19: {'chlorophyll': 1.29,
      'iron': 0.01,
      'nitrate': 0.8,
      'time': '2022-07-24T17:00:00+00:00'},
 20: {'chlorophyll': 1.29,
      'iron': 0.01,
      'nitrate': 0.8,
      'time': '2022-07-24T18:00:00+00:00'},
 21: {'chlorophyll': 1.29,
      'iron': 0.01,
      'nitrate': 0.81,
      'time': '2022-07-24T19:00:00+00:00'},
 22: {'chlorophyll': 1.28,
      'iron': 0.01,
      'nitrate': 0.81,
      'time': '2022-07-24T20:00:00+00:00'},
 23: {'chlorophyll': 1.28,
      'iron': 0.01,
      'nitrate': 0.81,
      'time': '2022-07-24T21:00:00+00:00'}}

What I need is the piece of code that transforms the above into the format that Airtable understands:

'{
  "records": [
    {
      "fields": {
        "Time": "2022-01-05T23:00:00.000Z",
        "chlorophyll": 1.33,
        "iron": 2.33,
        "nitrate": 3.44
      }
    },
    {
      "fields": {
        "Time": "2022-01-05T23:00:00.000Z",
        "chlorophyll": 1.33,
        "iron": 2.33,
        "nitrate": 3.44
      }
    }
  ]
}'

Hope someone can help me out!

Cameron_Kaiser · ‎Jul 25, 2022

Writing in pseudo Java but Python is probably pretty similar:

JSONObject dicts = "{19: {'chlorophyll': 1.29... etc";
JSONArray recordsToPost = new JSONArray();
for(String key : (Set<String>) dicts.keySet()){
      JSONObject dictFields = dicts.get(key);
 
      JSONObject recordToPost = new JSONObject();
      JSONObject recordFields = new JSONObject():
      for(String dictField : (Set<String>) dictFields.keySet()){
            // Get String version for better airtable compatibility
            recordFields.put(dictField, String.valueOf(dictFields.get(dictField)));
      }
      
      recordToPost.put("fields", recordFields);
      recordToPost.put("typecast", true);      // Allows airtable to create single selects that didn't exist
      recordsToPost.add(recordToPost);
}

The code looks kinda complex because it’s Java, but basically you just want to

Loop through all of your JSON Objects
In each JSON Object, loop through its keys and assign those key/value pairs to a new JSON Object
Add new JSON Object to your recordsToPost array that you will send to airtable via API

In Python it will likely look a lot simpler and you won’t need to do weird casting like (Set). If you look up “Python loop through JSON Object keys” (or dict if that’s a native python thing) you will probably get handy tutorials on how to do it. There’s nothing special about Airtable’s format, it’s just

Object : 
      fields :
            field1 : x,
            field2 : y