How do I add records from an Excel file?

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
I am working on bringing in a tool list from Mastercam into QB. I have formatted a report template that mastercam uses to output the list. the list can be saved in various formats, excel being one.
How could I easily add records using the rows of data from the output? I thought the API importfromcsv would do it but I cant get my head around it. It looks like the value need to be in the call, not a file...? At anoth job we had a software team and a guy made me a program that i could drop the file on and it would prompt me for the parent record id and it would add the records. But I don't even know where to start something like that...?
Photo of Jonathan Roberts

Jonathan Roberts

  • 338 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Lets start with low tech and go from there.

You can directly manually import a CSV file or an xlsx Excel file.  Just click the table and then look for import export at the right and then go from there.

You will find that it is way way easier to do this on a manual repetitive basis if you can have the fields named so as to exactly match the Quick Base field names. One way to get the field names is to run a regular list all type report with all the fields to get the field names and then use the More button at the top right to "save as spreadsheet". The top row will be your field names.  



Photo of Jonathan Roberts

Jonathan Roberts

  • 338 Points 250 badge 2x thumb
I am pretty savvy on this method, I just do this task like 20 times a day sometimes. And i want to have a work flow that i can easily share with other CNC programmers. What is the mid tech way?
Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
The mid tech way is to set up a Sync table for these entries.  Then Quick Base would check once an hour, but that may not be often enough for you.

I guess they are more formally called "Connected Tables".
https://help.quickbase.com/user-assistance/adding_a_connected_table_to_your_application.html

The source can be those three cloud sources or an FTP path.  So, for example if the data is coming in from another system  and you have some programmers they can may be set up some kind of automated process to extract the data from your Enterprise system convert to csv format,  and  put the file into FTP path.  Quick Base can look every hour (or with Admin rights, you can push a button to also do ad hoc updates) and it will append in the first 10 CSV files it finds in that FTP path and then move them to a "Done" folder.  Then next hour it will wake up again and repeat looking for up to 10 csv files to process.





Photo of Jonathan Roberts

Jonathan Roberts

  • 338 Points 250 badge 2x thumb
I hit a snag, i have nothing good for refresh key... It is required. the fields i am importing are: tool #, D #, Related tool, length, related setup. none of these are going to be unique ever.
Photo of Jonathan Roberts

Jonathan Roberts

  • 338 Points 250 badge 2x thumb
I combined some fields to be a unique key, should work. I got stuff!

Ok I think i am curious enough for the Hi tech way!?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Ok, post back if you get stuck. What method woolly you be using?. Cloud or FTP path.
Photo of Jonathan Roberts

Jonathan Roberts

  • 338 Points 250 badge 2x thumb
I did the google drive method. It works ok. I don't like how i can only add data to the table that way and not manually in quick base. Maybe an automation or web hook to add the new records to a different table that i can add too also?