Import data from CSV

I conducted quick testing for New Action of “Import CSV file” as it seems to be functional right now.

I save the CSV file (made out of Excel) and saved to local. This CSV have first row with exactly same names of column against the target table

This is observation so far.

  1. Add new row

    It works. Make sure to add “unique value explicitly” on the csv file otherwise this action wont add new rows (for rows missing the key value)

    Even we have initial value set and leaving the value in cells for key field in CSV as null/blank, action will not upload rows. Suggest to Appsheet dev team to review their code to let the action add new row even missing the cell key value while we have initial value set on the column definition on appsheet editor.

  2. Update existing row

If the value in key field in CSV matches the one in existing data key value, then the uploaded rows from CSV will update the existing row. Lovely

  1. Key value missing

See comment in 1). If we miss the value for key, either existing or new, this new action will not add new rows.

  1. Data type mis-match

For instance, we have number type on Appsheet editor, but we have text value for that field in CSV, upload csv seems to be failing. Meaning nothing is going to happen is data type mismatch is detected.

  1. CSV file type sensitive?

Save in the format of CSV UTF-8, and upload to Appsheet, it works. Other CSV file MS DOC does not work.

Those were the results of quick test withe super simple use cases . Hope other community member will add something new findings.

20 114 11.9K
114 REPLIES 114

It seems that a detective is working

2X_4_4eacacb75601e0febae1d752fe8dd26a2f861d3e.gif

Sean_Lim
Participant V

Do any Workflows linked to row additions or updates via this action get activated?

Yes apparently.

Well then.
This is exciting!

GreenFlux
Participant V

I just found this new feature and came straight here looking for more info. Thank you for posting your test results! Very helpful info!

@tsuji_koichi ditto.

This… is epic. New levels of functionality are possible now. 2X_4_465daf14f4099bbb3d5bbb4c4dd4dce77ef1da20.gif

Where exactly do you save the CSV File to?

I’m curious if this would mean that we could use CSV files as Data Sources?

Hi Fabian. No, this is a different feature. I was referring to csv files “staying where they are on s3, azure blob, or google file storage, and then connecting to them as a kind of data source.”

@TyAlevizos What we need: We can do a CSV export from our ERP with all our Item Numbers. This can be saved to Google Drive. I would like to use this CSV as a Data Source for my App.

You can import your CSV… and if the records already exist in the system it updates them instead of just making new duplicates.

It’s close… but not a true data source integration.

Will the updates and adds from CSV import trigger workflow?

that’s a good question… my guess is not at this time.

My guess is they’re leveraging the internal API for record adds, and it would…

HI @Fabian . Couldn’t you just save your CSV as a Google Sheet and then use that as your data source? Or, create a Google Sheet matching the columns, and create your app using that Data Source, then use the CSV import. What is your use case where you want the CSV to directly be a data source? Is it so that you can also “export” the CSV and send the app data back to an application? or ?

Hi @Scott_Haaland
Our ERP system exports a CSV every day. Via Google Backup and Sync we can Sync this CSV file with Google Drive.
I want to use this as a data source for my App.
Actually we have to convert the CSV to Google Sheet or EXCEL.
But it would be easier to have the CSV directly as the data source.
It has 35.000 rows and 4 columns.

Could you not use a google app script to read the .csv file into a google sheet every day?

Something like this:

For anyone that cares…
CSV import happens VERY fast. Seemingly faster than a paste values run into google sheets.
At small file sizes you import, and it syncs and bang, you’re up…
At medium file sizes, it waits and then does a sync
At large files ~15,000 records 5.4MB it hangs forever, then says “Action Failed” but in reality all the records are in.

Also, it does trigger add and update workflows…
Strange behavior… AFTER the workflows run, which can take some serious time depending, then it does a flash sync… Which means you could have already navigated to another portion of the app, tried to do another import, etc. left the app, etc…

AppSheet never ceases to amaze me. 2X_4_465daf14f4099bbb3d5bbb4c4dd4dce77ef1da20.gif


Okay @Grant_Stead I have another question for you:

Have you been able to build a way to download a csv for parents and their children? Like… push a button and get all the data for the relevant record you’re looking at?

I think I have… I just did a csv template, and created the selects…

Hi Grant,
I am trying to use the import data from .csv file function to load data to two different tables: OrderHeader and OrderLines. Is there any way to do this?
How can I make a .csv file that will be upload both OrderHeader and OrderLines?

Looking forward to your reply.

Out of the box… No…
You could restructure your data to better match your import and then use slices, etc.

OR

Working on something like this right now… I’m creating a “loading table” that matches the import, with an added couple of columns that initialize based on criteria that define what table you want the records to go to. Then I’m creating workflow api webhooks that on add to the loading table push the records to the different tables.

I see. So one aux table to load the data from the csv and then, when that table has new records, an action occurs that will load the aux data to the respective tables, right?

That’s my plan. It’ll also allow downstream triggered events base don add’s…
AND the import uses that API anyway so it should basically do what we need.

Hi Koichi-san,

Thanks for doing the CSV Import tests.

I am in the process of fixing the following problems. I hope to release theses changes later this week.

  1. You will be able to omit the value for a key field having an initial value. The system will assign an InitialValue to the newly added record.
  2. Some invalid input data values were not being detected. For example, if you entered a text value in a numeric field, we did not report an error. Such errors are now detected.
  3. In found some cases where InitialValues were not being applied properly. They will now be applied.
  4. The Open File dialog that appears when you click the CSV Import button was displaying many file types. It will now only display .CSV file.

As Koichi-san and Grant said, workflow rules are triggered when you do a CSV Import. It is exactly as if you had entered the records from a client application.

Will computed keys function fine?
I don’t have much experience with computed keys but I’ll probably need to employ it here.

Hi Phil,
Thank you for your works! This new feature will work far better supported by those new enhancement, cheers.

Koichi

When the key column is left empty I get this error message. I have initial value in the app. @Phil

I don’t even have a column on my CSV if that column has an initial value. Do you need both, rows with a certain key to edit and new rows?

Computed keys should be fine.

Austin
Participant V

2X_0_0b4341ad37857da620b9381ebee42a422f255ec4.png
Would it be possible to get some indication of what is failing on the CSV upload? I’m uploading to a database if that’s whats causing the fail. I even went in and filled out the whole row in the CSV not just my necessary fields. I see that there isn’t any documentation on this action yet so I will probably just need to wait for that before trying to add this to a production app. I will be loving this feature though once it is fully working! Didn’t even image this as a possible feature!

I’m having the same issue… Can’t wait to see it in Action;)

@Austin_Lambeth did you check the audit logs for the app? That might contain some hint as to why things didn’t pan out.

The audit logs do contain the reason that the upload fails!
They only show 1 error at a time so gotta constantly trouble shoot the issue but it is possible to trouble shoot it. Would still love the error on the action failed notification so users can troubleshoot the error.

Austin
Participant V

I did not but that’s an idea that definitely should have popped into my head!

I Also tried the feature. I went step by Step to see where i was going wrong. Added only 2 Columns - One unique ID and 1 no column. I added till 400 rows step by step and went fine with the CSV Upload. Then when i added 401 row, it said Action failed.
Also then i tried uploading new 400 values again, it said action failed, but the new values were added to the Google sheet. There is some issue and the feature is not yet ready i guess. Also i added the same values again with Column B Changed now, it didnot add those values.
Need to wait till the feature is ready i guess with documentation

I suspect this operation is possibly working fine, it is updating (overwriging) existing row which contains same values for all the fields as one for rows being attempted to be added.

Updating existing rows, but pushing the same values to all the field, then eventually it looks the operation is not doing any jobs, as apparently the number of the rows are same before/after operation and values are also the same.

Yes, But it should update the B Column since Unique IDs are the same in CSV as well as Google Sheets as per your first post, which was very useful. When more than 400 rows are added it is not working.
Guess need to work more on it. A video of the same would be useful to all if posted by Appsheet team please.

Top Labels in this Space