Knowledge Base

API ImportFromCSV | ADVANCED

API_ImportFromCSV is a powerful developers tool and one of my personal favorites to use via Webhooks and custom scripting. This API call would be synonymous with mass importing, or sending multiple records to add or update in Quick Base at one time via the API. The use cases and examples for using an import will be the same relative to API_AddRecord or API_EditRecord, the difference being that you can use ImportFromCSV to do multiple adds, multiple edits, or both, all at once.

So for example, if I were setting up a project and wanted a pre-defined lists of tasks to be generated with it, let’s say 15 tasks, one option would be to string together 15 AddRecord calls together to accomplish your goal. Or, create all 15 at once using ImportFromCSV.

EDITORS NOTE: This Document will cover using a POST via Quick Base to demonstrate the concept. You can use a POST in webhooks as one example. Another alternative would be to use custom scripting (Javascript, Python, Ruby etc). It is possible, but I personally don’t recommend using the URL-Alternative for using ImportFromCSV given the formatting constraints.

A simple example of a basic import call might be forecasting out a calendar year when a new project is created. A new project is created, and I want a financial forecast created in a ‘Forecasts’ module, for Months 1-12.

If we’re utilizing Webhooks in this example – then what I’m hoping to have happen is that when a new record is created, I automatically create 12 forecast records so I can plan my spend. Our example POST then, might look something like below:

<qdbapi>
<usertoken>example</usertoken>
<records_csv>
<![CDATA[PROJECT #,MONTH
1000,1
1000,2
1000,3
1000,4
1000,5
1000,6
1000,7
1000,8
1000,9
1000,10
1000,11
1000,12
]]></records_csv>
<clist>6.7</clist>
<skipfirst>1</skipfirst>
</qdbapi>

To break down the above – let’s looks at the the individual tags themselves

– The opening tag for any Quick Base API call

– Where you put your User Token

– This is where you are actually putting your data. Much like the name suggests, API_ImportFromCSV interprets data like a CSV file. So think so about the way yout might do a standard Quick Base import. You organize your data into a file structure with rows and columns, Quick Base interprets that and you confirm which field(s) you want to map your data to, and then you import it. Same thing applies here, your is basically your import file, so it should have a similar row/column structure. In the example above:

Project #, Month represent our Column headers

Each line after that,

1000,1

1000,2

….

represent each new ‘row’ in our data, still organized in the same column format

Note, that each new row is on a new line, and each data point is separated by a comma. Again, the name importFromCSV means you are structuring your data like a CSV file, where the data is structured and formatted by having each new line interpreted by a line break, and each data point separated by a delimiter, in this case a comma (CSV -> Comma-Separated Values).

If you had additional data – say an ‘owner’ of this forecast, such as the PM assigned to the project, then you would add another value to each line after the # like so:

Project #, Month, Owner

1000,1,Chayce Duncan

1000,2,Chayce Duncan

……

– Your clist corresponds with the actual fields in Quick Base that you want to write data to. Each field ID is separated by a period delimiter, so in our example above, I want to write to fields 6 and 7 (Project # and Month), so my clist would be 6.7

For each additional field you want to write to, add another period and then the pertinent field ID. It’s important to note that the number of fields present in your clist should match the number of columns in your data set.

– With this, you are telling Quick Base to skip the first line of your data. This is common when you have Column headers. Putting a value of ‘1’ will mean that the first line of your data is ignored.

BEST PRACTICE: One thing not discussed here is the usage of Record ID# or Key fields. ImportFromCSV has the ability to both add and update at the same time. If you pass in an existing Record ID# for example, that record will be updated. If you do not have a value entered for Record ID# when you make the API call, then Quick Base will generate a new record from the info provided. You can send rows of data where some have RIDs and some do not, and Quick Base will take the appropriate action.

When evaluating when to use ImportFromCSV – the biggest driver in my experience is not knowing how many records might be created. In the example above – you could get away with setting up a QB Automation and chain 15 Add requests together. But what if you need 18 this time? Or 30? The unknown is where this import call comes in handy. You can utilize custom scripting, creative formulas etc. to account for that variability and achieve your desired result.

For an example of how to use formulas to achieve dynamic record creation, I suggest taking a look at Formulas + Webhooks = Dynamic Record Creation to explore further.


Copyright ©2020 - Quandary Consulting Group