Create records with API call

API & Customization Ask Developers
1

I have many customer who have their data recorded in excel file and they find it difficult to complete form in my knack app using those data one by one. So, my target is to make API call with that excel file and it will create individual record for every single row from that excel file. I tried to do this using excel VBA but I can not find any solution.

Does anyone one know how can I achieve this or Is it possible to do this using Excel VBA.

Also I want to let you know that I want to build this system for my customer so that they can create their own record without any help.

2

zapier or Integromat will do this very easily for you. Doing this in excel/VBA not a trivial undertaking Id think
Integromat $9/month seems to provide a lot of transactions.

3

You have a few options all with their own pros/cons. I will outline them below:

  1. Convert excel to csv and import in the Knack builder. Pros: you don’t have to write any code or pay for any third party services. Cons: only people with builder access can do imports.
  2. Write the code in VBA. You can do this in VBA by looping the rows and doing a xml http request for each row. The request uri is going to be the knack ID and you will send a JSON payload. XmlHttpRequest - Http requests in Excel VBA - Coding is Love. This might not be a great solution because you will have to have the code on each worksheet and it might not be easy to teach your customers how to run it.
  3. Write client side code to parse a csv/xlsx file and loop doing POST request for every record. Pros: Your clients will be able to upload easily. Cons long spreadsheets will require the user to wait for a long time as they are uploaded
  4. Set up a server. Send the request csv/xlsx to the server, parse, send POST requests via Knack api. Pros: users can upload their own files. Cons: Server development required (In my opinion this is the best approach)
  5. Integromat/Zapier. Pros: Easy to setup Cons: Monthly fee, lots of 503 timeout errors. Not the most reliable solution.

I prefer setting up a server side application in node.js. This method lets us validate the spreadsheet when the user uploads it into Knack. The request to the server doesn’t force the user to wait. The server doesn’t cost any monthly fees (depending on where you deploy) . It’s what i’ve found the be best solution for most of my clients because we are able to set up the process however it’s needed and it’s far more reliable than other solutions i’ve found.

2 Likes
4

Thank you so much. I will look into this if I can not find any other solution.

5

Thank you so much for your details answer. You make it easier for me.

Now I am also prefer to setting up a server side application.

It will be great if you can provide me more details on how to do this.

6

@Kelson Do I need Domain, host or Database if I want to achieve my goal by setting up a server?

7

Hi Sanjida and Kelson,

I am also looking to set up own private server, can you please provide steps on how to do that…?

Regards;
Simon