API_ImportfromCSV doing nothing

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • In Progress
Hi all!

I'm trying to run this API_ImportfromCSV but it's getting me nowhere. 
I'd appreciate some help as I've been stuck with it for many hours, and haven't gotten to my error.

https://(DOMAIN).quickbase.com/db/(TABLE)?a=API_ImportFromCSV&usertoken=(USERTOKEN)&apptoken=(APPTOKEN)&records_csv=Guia,34168,102999&clist=6&skipfirst=1
This should create new records in the table when the API is run. 
Added "apptoken" as the API returned an error even when using usertokens.
Everything seems to be fine, but the reply is: 

<qdbapi>
<action>API_ImportFromCSV</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<num_recs_input>0</num_recs_input>
<num_recs_added>0</num_recs_added>
<num_recs_updated>0</num_recs_updated>
<num_recs_unchanged>0</num_recs_unchanged>
</qdbapi>


Nothing seems to happen!
I've tried doing this from google scripts, as I want to automate the import but this also doesn't seem to work.. I have the values I need for the CSV in an array, which i'm joining using
    var csvString = "Guia," + arregloGuias.join(",\n");
    var url = baseURL + table + "?a=API_ImportFromCSV" + usr_token + apptoken;
  var options =
      {
        "method"  : "POST",
        "records_csv" : csvString,
        "clist" : "6",
        "skipfirst": "1"
      };
  Logger.log(url + options);
  var result = UrlFetchApp.fetch(url, options);
  Logger.log(result);

The result is the same
<?xml version="1.0" ?>
<qdbapi>
<action>API_ImportFromCSV</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<num_recs_input>0</num_recs_input>
<num_recs_added>0</num_recs_added>
</qdbapi>



Appreciate any help!!

Thanks!




Photo of Carlos

Carlos

  • 796 Points 500 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
So I can't say for sure about your google scripts example without seeing your array for arregloGuias - but in your first URL example - you have the skipfirst=1 meaning you're skipping the first row, but you only have 1 row so it's not going to import anything. The skipfirst is usually intended if you have headers. Remove that from your first example and that should get you moving

Side note - your clist only has one field (clist=6) but it looks like you're trying to import to 3 fields (records_csv=Guia,34168,102999). You'll want to make your clist have 3 field IDs to match
Ah - now I see what you're doing. So then same reason - different explanation from my first comment. Since its an importFromCSV - you have to force it to understand a new row. By doing value,value 2,value 3 as comma delimited - your browser just treats that as 1 row. 

So you'll want something like this:

"https://yourrealm.quickbase.com/db/yourtable?a=API_ImportFromCSV&records_csv=Guia" & URLEncode("\n") & "row1" & URLEncode("\n") & "row2" & "clist=12"

The URLEncode if you're in QB will force it to interpret them as new lines. You can leave or remove the skipfirst at that point

Photo of Carlos

Carlos

  • 796 Points 500 badge 2x thumb
Well that seems to have done the trick nicely!!


I'd like to get it to work without using the URL alternative, basically so an unlimited number of records could be posted at any given time, but this will definitely do for now! 

Thanks a lot!
Based on your comments about using Google scripts to automate it - and knowing how you're using that array now - the method will still be the same for why the google script equivalent isn't working. Likely the .join(",\n") needs to be converted. I haven't played with that exact method of POST nor have an easy test, but 1) remove the comma at a bare minimum and just do .join("\n"). If that doesn't work - I would suggest trying to pass a couple different carriage return equivalents like <br> or &#xD; ( which would be the XML equivalent of a carriage return )
Photo of Carlos

Carlos

  • 796 Points 500 badge 2x thumb
Carriage return works with encodeURIComponent( "\r\n") on the URL.
However, I still can't get it to work pasing the array.
 
Tried using   
var cdata = XmlService.createCdata(csvString);
With the csvString = '<![CDATA[' + csvString + ']]>' 
csvString contains the numbers separated by \r\n as described above.. 
Have you tried it using the stand XML carriage return? --> &#xD;