Can't get JSON data into Airtable through API

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.

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.

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!

	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

time            string
chlorophyll    float64
iron           float64
nitrate        float64

{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'}}

'{
  "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
      }
    }
  ]
}'

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

1. Loop through all of your JSON Objects
2. In each JSON Object, loop through its keys and assign those key/value pairs to a new JSON Object
3. 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