How to programatically import CSV to AirTable, creating a new table in an existing base (CSV is a file on a cloud VM)?

Dev_Local · ‎Dec 03, 2019

I am sure this has been asked before, I have searched high and low, and have found no solution to do the following, even with Zapier.

I have a .csv file that I want to import direct to Airtable.

The .csv was created programatically (in Node.js), and exists on a Google Cloud VM (Red Hat Linux).

There is nothing in the AirTable API that allows for direct insertion of a full CSV as a new tab in an existing base.

I want to name the tab in the existing base the same name as the CSV file, without the .csv extension.

If anyone has actually done this successfully, and smoothly, please respond. The only articles I can find on how to import a CSV to AirTable involve a human being and clicking in the browser.

Justin_Barrett · ‎Dec 03, 2019

To my knowledge, this part must be done manually. I’m not aware of any API-available option—including using tools like Zapier or Integromat—for creating tables. You can only add or modify data in existing tables. That aside, both of those listed services can parse CSV data, so once the table has been manually made, either service can be set up to import the data.

Dev_Local · ‎Dec 03, 2019

Thank you for the info.

Even if I have a pre-existing table created manually:

I’m not seeing that Zapier can do this (get all rows in my CSV to Airtable). Zapier looks to be a tool connecting two web app end points.

A CSV on my local file system is not presented to me when I go through the Zapier wizard, as an integration end point to choose.

After I generate a CSV on the cloud VM (from a select from a DB), I’m not sure how Zapier can help or even work, or play a role. The data source is the CSV on a file system (cloud VM). Zapier has no access to the CSV.

I’m not sure about Integromat but will investiate.

I’m not seeing any solution that Zapier would provide in this case, but I see it mentioned quite often.

Justin_Barrett · ‎Dec 03, 2019

Admittedly I don’t have experience with cloud VM systems, but if it can be accessed online—and the “cloud” part of “cloud VM” leads me to believe that it can—my gut says that either Zapier or Integromat could retrieve the data and insert it into Airtable. I just don’t know the specifics, so I’ll have to defer to others to chime in on that front.

Bill_French · ‎Dec 03, 2019

It can’t be accessed by Zapier until it exists through a web service. Nothing but roadblocks between your data and Airtable.

JonathanBowen · ‎Dec 03, 2019

If the table already exists (with fields that match the file or, at least, fields that can be mapped to the file) and you are comfortable using the Airtable API, you could easily create a process/script on your VM that iterates through the CSV file and loads it into Airtable. This could be written in JS, Python, just about anything. The API has two record create methods - one is for single records the other is for a group of up to 10 records.

As you already have a process that produces the CSV file using Node, could you or your developer extend this and push the records into AT? Arguably, there is no need for the CSV file (unless it serves some other purpose) as the records could be POSTed straight from the Node app to AT.

JB

JonathanBowen · ‎Dec 03, 2019

For those that are interested, here’s an example Python script that imports records one by one (takes only a few seconds for 100 records):

import requests
import csv

post_url = 'https://api.airtable.com/v0/YOUR_APP_ID/CSVImport'
post_headers = {
    'Authorization' : 'Bearer YOUR API KEY',
    'Content-Type': 'application/json'
}

f = open('MOCK_DATA.csv')
csv_f = csv.reader(f)

for row in csv_f:
    name = row[0]
    number = int(row[1])
    role = row[2]

    data = {
    "fields": {
        "Field1": name,
        "Field2": number,
        "Field3": role
        }
    }

    print(post_url)
    print(data)

    post_airtable_request = requests.post(post_url, headers = post_headers, json = data)
    print(post_airtable_request.status_code)

Here’s the first few lines of the CSV file referenced:

Ross,92,Marketing Manager
Delmor,10,Help Desk Operator
Jazmin,79,Budget/Accounting Analyst III
Moore,50,Senior Developer
Winny,95,Quality Engineer
Goran,34,Environmental Tech
Linnea,94,Professor
Smitty,60,Tax Accountant
...

The end result is:

Enjoy!

JB

Dev_Local · ‎Dec 06, 2019

I want to thank everyone for the responses and ideas, and will look forward to an expanded API with additional features:

1. bases and sheets within bases created programatically through the API
2. insertion of a CSV direct

I was able to work through all CRUD operations using the Airtable API using Node / axios (to an existing base and sheet created by clicking and typing).

I still have a few things to work out (like how to insert once the column changes type from text to checkbox), I posted a new issue/question on this, but otherwise have a good handle on the API at this point.

Ryan_Wigley · ‎May 28, 2020

@JonathanBowen

Thanks for your suggested solution for this programatic import of CSV to AirTable. I used the code below, which reads the CSV fine however I am getting 422 response from AirTable. I have troubleshooted to the best of my skills, but can’t fix the error. What am I missing?

Please note the ingredient name is a lookup ID, that’s why its in brackets.

import requests
import csv

post_url = 'https://api.airtable.com/v0/appjntXiOLPBmOpKs/WEM%20Data’'
post_headers = {
    'Authorization' : 'Bearer YOUR API KEY',
    'Content-Type': 'application/json'
}

f = open('import_data.csv')
csv_f = csv.reader(f)
for row in csv_f:
    inventory = row[0] 
    usage = row[1] 
    received = row[2] 
    report_date = row[3] 
    batch_id = row[4] 
    lookup_id = row[6]
     name = row[7]
 
    data = {
        "fields": {
             "Name": name,
            "Ingredient": [lookup_id],
            "Inventory": inventory,
            "Usage": usage, 
           "Receipts": received, 
           "Report Date": report_date, 
           "Batch Number": batch_id  
      }
     } 

print(post_url)
print(data)

post_airtable_request = requests.post(post_url, headers=post_headers, json=data)
print(post_airtable_request.status_code)

I have been able to validate the post request using these credentials works via Postman, but still having issues. Here’s the first few lines of the CSV file I used:
949.7318,180.3,0,05/21/20,4421b1fa-9bb6-11ea-8f85-8c85904e2678,Agrado Plus,recg1ZpkYgUMXRgZf,Agrado Plus - 2020-05-21
4408,0,0,05/21/20,4421b1fa-9bb6-11ea-8f85-8c85904e2678,EGold Calcium Salt,recvCnq1AXSX7Y6cq,EGold Calcium Salt - 2020-05-21
14354 5441,0,05/21/20,4421b1fa-9bb6-11ea-8f85-8c85904e2678,Smartamine ML,rec36WGlYOrUdEDIu,Smartamine ML - 2020-05-21

Here’s what my local terminal is showing when I run the code using the csv:

Bill_French · ‎May 28, 2020

I think a 422 error is an obscure indication you are trying to write data of a certain type into a field that cannot handle that type.

Recommend you downsize the import to one or two fields and slowly introduce them to figure out which is triggering the errors.