API_ImportFromCSV duplicate key

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
I am using the API_ImportFromCSV to load data from an external system into QB but it is giving me an error:
Trying to add a non-unique value to a field marked unique.

 https://fixandpaint.quickbase.com/db/bmk9bpw2f?a=API_ImportFromCSV&userid=59121696.7ziy&tick...
st=6.7.8.9.10.11.12&records_csv=<![CDATA[<records_csv>320982934,1687480,39744970,2017/03/11 09:10:33 AM,2017/03/11 01:08:02 PM,2017/03/11 01:08:35 PM,14249]]>

I have changed the key of the table to be field 6, and am expecting an update to happen and not an add.

What is wrong?

Thanks for the help,
Dean
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Double check the field properties for all the fields involved in the import and make sure that non of them are requiring unique values.

Then you might be able see the error in the import.
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb
Thanks Matthew, the only field that is unique is 6, which is the key
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
Dean,
Was that your actual URL? Are you just adding 1 record or is it possible that the data in the actual import set has a duplicate Key in multiple records.
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb
That is the actual URL. I originally had more data but reduced it to one row for debugging
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb
I did not run it through urlencode. Might that make a difference?
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb
Btw. This same process creates an output file and I can import using the ui just fine. Inserts and updates both work as expected.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
I did not go back to the documentation, but I wonder if there is a syntax error and in fact there is not a duplicate key but some kind of syntax error in the URL.
Photo of Dean

Dean

  • 616 Points 500 badge 2x thumb
The solution involved adding a carriage return after each data row ("`r`n ").  Then I had issues with strings, but encapsulating them in double quotes fixed that.

Saga over for now... :)

Thanks for the suggestions and your time.
Photo of QB Development

QB Development

  • 330 Points 250 badge 2x thumb
Is anyone able to post an example? I'm trying to use the same technique, but cannot get multiple rows to read....

var text URLONE = URLRoot() & "db/" & [_DBID_ITEM_REQUESTS]        
& "?a=API_ImportFromCSV&usertoken=[USER TOKEN]&apptoken=[APPTOKEN]"
&"&records_csv=<![CDATA["
&"71,Request Item for Job,1276"   <---- this record will trigger
&"7,Request Item for Job,1276"      <--- this one will not....
&"]]>"
&"&clist=6.44.15";
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,144 Points 20k badge 2x thumb
Get rid of the "<![CDATA[" and "]]>" and include explicit newlines in the URL. Slightly reformulated try this instead:

var text URLONE = URLRoot() & 
"db/" &
[_DBID_ITEM_REQUESTS] &
"?a=API_ImportFromCSV" & 
"&usertoken=<USER TOKEN>" &
"&apptoken=<APPTOKEN>" &
"&records_csv=" &
"71,Request Item for Job,1276\n" &
"7,Request Item for Job,1276" &
"&clist=6.44.15";

Notes:

(1) Normally API_ImportFromCSV is not called as a GET method as there is a limit on how long a URL can be and the CSV data can be quite long. 

(2) I am not sure why you are using a user token when you have a application token and presumably the user is already logged in.

(3) Your formulas will be more readable if you lay it out with one parameter line value per line and continue the line with an ampersand at the end.

(4) Note there is a newline character at the end of the first line of the CSV.