Giving users the ability to import into tables.

[jimarch]

Is it possible to give users a Datapage that they can pick a file and then import into a table?  The situation I have is that I have a basic datapage that will be used to record volunteer hours.  But also we will have some HR managers adding bulk hours to a spreadsheet template that will then be imported into the same table.  I know if I have them drop the file on our internal network, I can build a process to FTP it to Caspio and have a Scheduled Task do the import.  I actually do that for some other processes.  But for this scenario ,  they are wanting to live in one form area where they can do the manual entry or a mass import via a nice interface without have to remember the local file path to drop the spreadsheet into.  I have a datapage that will upload the file to the Caspio Data Stor but then I can't automate anything from there.  Unless I'm missing how to do that.  Any thoughts or idea's are greatly appreciated.  Or maybe someone has come up with a slick way to use the Tools Import feature in a data page. 

[lengleng]

Quote

• Is it possible to give users a Datapage that they can pick a file and then import into a table?

Can you elaborate on this? Pick a file? From their system? and what do you want the data to look like after submission? Are you only using 1 table? do you have clearer steps of your workflow? It's kinda hard for me to visualize.

[Kurumi]

Hi @jimarch,

This is possible, however might require custom programming and backend setup. I believe they have a Professional Services that can do this. You can have a free consult here: https://www.caspio.com/services/project-consultation/

Hope it helps!

[jimarch]

@lengleng Thanks for the reply.  Basically have them pick a file locally from their system and then import into one table.  Basically do the same process as if I were importing from the Tools menu.

[jimarch]

Thanks for the info @Meekeee  I'll check into that option as well.

[chuckiecc]

On 5/13/2021 at 1:04 PM, jimarch said:

Is it possible to give users a Datapage that they can pick a file and then import into a table?  The situation I have is that I have a basic datapage that will be used to record volunteer hours.  But also we will have some HR managers adding bulk hours to a spreadsheet template that will then be imported into the same table.  I know if I have them drop the file on our internal network, I can build a process to FTP it to Caspio and have a Scheduled Task do the import.  I actually do that for some other processes.  But for this scenario ,  they are wanting to live in one form area where they can do the manual entry or a mass import via a nice interface without have to remember the local file path to drop the spreadsheet into.  I have a datapage that will upload the file to the Caspio Data Stor but then I can't automate anything from there.  Unless I'm missing how to do that.  Any thoughts or idea's are greatly appreciated.  Or maybe someone has come up with a slick way to use the Tools Import feature in a data page. 

Jim did you solve this by chance?  We have the same issue with the nuance of adding a unique record check for the upload such that IF the record/information exists that one out of the bunch are NOT uploaded, yet the others are allowed..  

I don't want to rely on the professional services group, would rather know how it was done if I can ask.. thanks

[jimarch]

@chuckiecc  Unfortunately I did not come up with a solution.  Due to some added business rules that were added to the entire app process, allowing an import isn't feasible any longer as it would cause a lot of data integrity issues.  And the point of building all of the Caspio forms was to alleviate the need for imports since if the user is going to open a spreadsheet to enter the records, then why not just open the forms and enter.  That is a battle we're currently fighting but in the real world, a lot of users fall back to their comfort zone and think it's easier to work in a spreadsheet.    Anyway, I do still feel a simple way to do an import from an uploaded template inside of a datapage still would be a nice addition to have available for other scenarios.  Please keep me looped in if you come up with anything.

[kpcollier]

3 hours ago, jimarch said:

That is a battle we're currently fighting but in the real world, a lot of users fall back to their comfort zone and think it's easier to work in a spreadsheet. 

This is all too true! And frustrating! Haha

[chuckiecc]

I guess perhaps my question is a bit different that your? not sure.  Although I would like a datapage to be able to enter this info, OUR concern is that we receive .csv files, with data that needs to be added to a table.  Easy enough.  BUT the issue for us is that we receive this data multiple times in a day and SOME of the data we receive in a day is already in the table we want to append to.  WE have a unique identifier in our records/fields so that we can make sure our data integrity is ensured.  So while the import feature works fine and isn't really an issue for us, the ISSUE is that there is no way (that we are aware of) for us to CHECK the records of the proposed upload vs. the data already in the table and either a) identify those records that are duplicates and NOT import them, but import the others, or b) reject the entire file  and list WHICH records already exist? or c) allow us to append the data, bu only append the non duplicates

So there may be a way to do this with the import tool, but if there is I can't figure it out.. Any ideas anyone?

 

 

[jimarch]

That's a good point.  I don't believe I've seen an option to basically do an "upsert" with the import process.  Just my initial thoughts are that you possibly could set up a REST process to do it.  Obviously much more involved to set that all up then simply doing an import via the built in tool.  Or maybe a third party solution like Zapier.  We do use Zapier for a couple apps that we need to mash the data in a table that is submitted via another cloud based form app.  And one of those options might have actually been the route I would have ended up going if I still needed to allow for imports.  Which luckily I don't have too.

[kpcollier]

Do you have a unique field in your table? Do you include it in your uploads?

I have a similar workflow, where I need to upload excel files weekly. I have a unique ID field in my caspio table that I include in my excel data. When I do an import and some of the records from the excel sheet have a unique ID that already exists in the table, the import process will upload all records in my excel data besides the duplicates. Then, the import message will state the import is complete with some errors and show you that there were some duplicates that weren't added.

It doesn't tell you what records were duplicates. But it does insert the other records that are not duplicates while not inserting the duplicate records.

 

*I do not think you can use ID fields in the import process. For me, I am using a table formula field that is set to unique, and includes the ID and another field. However, I think you'll be able to just create a formula field in your table, have it just be set to copy the ID field, and set it as unique. Then, when the import process is happening, the system will not include any imported data that would break the formula in your table. Hence, it would not import duplicates. 

Or, you can use a couple of fields you have already in your table that you don't want to be duplicated. Say a Customer Name and Email... just create a formula with those two fields and make it unique. 

Edited January 17, 2022 by kpcollier
600th post. w00t

[chuckiecc]

On 1/17/2022 at 9:50 AM, kpcollier said:

Thanks for the help this did answer the issue.  It isn't as elegant as we need but it does resolve the issue for us I believe.. thanks

 

 

Do you have a unique field in your table? Do you include it in your uploads?

I have a similar workflow, where I need to upload excel files weekly. I have a unique ID field in my caspio table that I include in my excel data. When I do an import and some of the records from the excel sheet have a unique ID that already exists in the table, the import process will upload all records in my excel data besides the duplicates. Then, the import message will state the import is complete with some errors and show you that there were some duplicates that weren't added.

It doesn't tell you what records were duplicates. But it does insert the other records that are not duplicates while not inserting the duplicate records.

 

*I do not think you can use ID fields in the import process. For me, I am using a table formula field that is set to unique, and includes the ID and another field. However, I think you'll be able to just create a formula field in your table, have it just be set to copy the ID field, and set it as unique. Then, when the import process is happening, the system will not include any imported data that would break the formula in your table. Hence, it would not import duplicates. 

Or, you can use a couple of fields you have already in your table that you don't want to be duplicated. Say a Customer Name and Email... just create a formula with those two fields and make it unique. 

 

[Jmichael1425]

I just found a third party tool that allows you embed a snippet of html on to your website using a button. So when you click the button it offers the end user a way to upload a csv file. Its called EasyCSV.io

Its a direct integration into Caspio and its only $39 USD a month. The only downside is you will have to get their support involved and they are a little slow to respond. However, extremely friendly and wanted to help. They made screen share videos and explained the issue and how to fix the issue or showing what work they did for me. 

The reason you have to get them involved is because if you set it up on your end, the refresh token expires every 24 hours, meaning you have to reset it up in 24 hours. However they can set it up where it auto refreshes the token every 12 hours so you never have to worry about it expiring. Also, its cheaper than paying integromat or zapier to do this.