1 : I reckon your CSV file include [Region] column and all the value remains Empty. Then delete this [Region] column from CSV file and try to import the file.

Koichi_Tsuji · 04-07-2020 10:02 PM

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.

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.
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

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.

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.

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.

Phil

I released a change yesterday that makes Import CSV much faster when performing updates to a SQL database. Previously updates were performed one row at a time. Now up to 100 updates are combined in a single call to SQL.

Koichi_Tsuji

Hi Phil,

I carried out a bit of further testing, here are some feedback.

Yes, we are moving the Key fields around, any place in the CSV file. Upper left, somewhere in the middle, upper right, it does not matter.
Likewise, the positions of any other fileds are not restricted. We can place the fieds in the arbtray order rather than the same order of the original table on the data source. Even we are able to place blank column (no field name as well as value) in the middle. It sounds like this feature will conver the tabular text data to JSON and pass to your API, so the order of the key name won’t be questioned.
Initial value setting ; When we have inital value formula and expression in the column setting, it will push the data “when the data field name are not on the CSV”. For instance, if we have a filed with initial value like “today()” - and then on the CSV file, we have date value in the row. However, when we leave some row for this fields as empy cell, the initial value will not be placed to the data source at the end. It sounds like that API passing a value of “null” as a value to set . Not sure which is convenient. Leaving the csv cell blank is a sign to Appsheet, hey push “null” value to return. Or hey Appsheet, when cell is left blank, push “Initial value” for it? Probably your current setting should be taken.
Update fields : Passing row values with the same Key IDs value which is existing in the table, Upload operation will UPDATE the existing row as far as key value matches. This is fine. Out of curiosity, I passed the existing table value , but removing the some data fields. For instance, I have “Date” fields and there are value in the backend data base. Then on CSV file, I removed this Date field. I assumed the existing value will not be affected, it stays unaffeted, but obviously CSV upload for “Update” operation will overwite full set of data fields on the target table. In otherwise, the fields name and value not on the CSV file will be refreshed. If initial value is set, then that will be applied. Without inital value set, then null (blank) value is returned.
Date field ; Always date field is trickly. The date format must be mm/dd/yyyy? on the CSV file? I passed the dd/mm/yyyy format (this is a text data so not a format involved though), then Appsheet could not read those presumably “date” value. so when we push date value, it always must be mm/dd/yyyy order?
The error message. For instance, we dont have initial value for key field, as well as missing key value fields on the CSV file, error message pop up in small window. Appsheet stop the import operation, which is fine. In your previous message, I understood Appsheet will detect any mismatch the data type, for intance, we place number on text field, as error. I assumed when we have such a data mismatch, then Appsheet wont start upload. However, no error message was thrown, and then the data sources left blank (Null). In my opinion, if there is date type mismatch, upload/import operation should cease immidiately, by throwing error.
Pop up modal. When there is error detacted, then Appsheet throw error message in pop up window which is fine and great. Then no error, it render another message window like this.

But this windows stay on screens “just for a moment”, as Appsheet is start to “sync”, presumably to referct the data change on backend to client. On this window, upper right corner, there is button of “OK”. Assuming the user will hit this OK button to start the import operation and stop by clicking X button at the upper right? What is the intended chain of action? I prefer to let the user expliciately hit OK button then appsheet start to load/import operation. Once the CSV file is selected, the user still "cancel’ by clicking X button on this window.

Maybe I come up with more as testing goes on, but I acutually have use case to start to implement this new userful feature, I m now deploying Import CSV to my production app. I believe the list of obvservations I made above will not stop to use new features on the prodution basis.

In the meantime, I would say upload speed is ultra fast. I m testing with Googlesheet, not on SQL, but it is fast enough. I think it is faster than usual “Sync” app… Wondering if any improvement of the performance on Appsheet when the app sycn naturally.

Cheers, thank you again Phil.

John_Dow

Hello @Phil I’m having exactly the same issue, as mentioned in the issue 3 of @tsuji_koichi one year ago.

Initial value setting ; When we have inital value formula and expression in the column setting, it will push the data “when the data field name are not on the CSV”.

Basically I have a field which should be filled in according to initial value formula IF the other field in this row is filled (for example other field is country, and auto field is region). When the other field (country) is empty, I have to provide the value for this field (region) manually, so the CSV Import column is filled in some rows and omitted in some other with expectation it would be completed through initial value formula where possible. however it doesn’t work, because intimal value formula is not triggered, as the column is present in the CSV import file and all the omissions are treated as null input, which is very inconvenient for the large imports.

Please advice on this issue. Thanks.

Koichi_Tsuji

Try this workaround

1 : I reckon your CSV file include [Region] column and all the value remains Empty. Then delete this [Region] column from CSV file and try to import the file.

2 : If you are not able to delete the [Region] column for some reasons, set those up.

Create action to add value to [Region] Column with your expression which is currenlty used for initial value expression, such as [Country].[Region] something like that.
Create new bot for this table. Event is set to “Add new record” . And fire this action

Once recoards are added by import CSV action, then BOT will be triggered to add the value to your [Region] field.

Hope this will help.

@Takuya_Miyai

John_Dow

Thank you, it’s filled in some rows and not in the others. I thought about it and the bot seems like an overkill. I think I’ll just split the file into two, one with filled column and the other is without column and load them separately.

Thanks a lot.

Koichi_Tsuji

Splitting files is and overkill to me, adding bot is easy, but you decide
Cheers.

John_Dow

Haha, true, depends on the time needed to create one

John_Dow

Actually I’ll set up a bot as I have too many in my app already anyways. But still- I have validation rules in the country and region fields in order to enable dependent drop downs, so I won’t be able to create a new record with csv action without prefilling it, because it will fail validation before moving to the next step with your suggested bot.

Koichi_Tsuji

I m not aware of your table/column structures etc , so not able to provide the sharp comment.
However, you could still be able to escape your validations by passing expression into valid if.
For instance, if xxx column is null, then no validation to yyy column. Validation works only there is value in xx column using just simple If or Ifs expression.
Good luck.

Lynn

Hi
Are we going to be able to import data with dates in the dd/mm/yyyy format?

Koichi_Tsuji

As you can see my previous one, the value in the cell in the form of “mm/dd/yyyy” was dropped off, and the data was left blank (null). I tested few times with the same result. But I might be wrong. Appreciate if you could test same to see how it works.

It could be a bit dangerous I feel. For instance, 11/1/2020 something like this. It should be accepted, Import Operation will take this into the data base.

The problem will be ;
I would call 11th January 2020. But It could be returned as 1st November 2020. …

Value like “13/1/2020” should be rejected though.

Phil

If you attempt to import thousands of records into a table that uses Google Sheets, the Import CSV could fail with the error “Invalid URI: The Uri string is too long”. That problem should be fixed when we release this afternoon.

Grant_Stead

So glad to see you making gains here!

Lynn

Has the date problem been fixed?

Fabian_Weller

@Phil

Can we import also XLSX files?
Can the CSV / XLSX file have header rows like this one?
Actually the CSV has to be Comma separated. Can you please add also support for semicolon separated CSV?
See Wikipedia:
Example of an analogous European CSV/DSV file (where the decimal separator is a comma and the value separator is a semicolon):

Year;Make;Model;Length
1997;Ford;E350;2,35
2000;Mercury;Cougar;2,38
Please add Localization to the Pop Up Messages.

Grant_Stead

@Phil poking at this a little today.
If I am on a detail view, looking at aREF_ROWS child list, I would like to click “View” and then from that "filtered view do an import, and it stab in the correct parent REF. Just like how from that same filtered view I can click the “Add” button and it carries the Parent. What do you think?
It makes sense to me to match the behavior.

adityo_reksopra

Hi,

Is it possible solve the business case like this?

Juan_Camilo_Sie

Hi I have 2 quiestions abot the Import CSV File.

What should I do when the table I’m trying to update by CSV dile has virtual columns ?
I’m getting an Error and Don’t know how to solv it

This is based on the Sample app “Advanced Inventory management”

I also tryed creating my own CSV file and updating to a table without any virtual column and It is “Working”, I have a mesage of success, but… The database is empty, the only columns that has data are those that I have initial values

sonam2

Hi, I have a question about the Import CSV File.

In my AppSheet application, we have made some columns mandatory for users and also we have an Import option only for Admin. So while importing the CSV file in the application it shows an error regarding the required mandatory fields.

so can we need to make all these columns non-mandatory for admin only so admin can import data without any restriction?

Phil

I am currently implementing Locale support for both the Import and Export data actions. Here is a description of what I am planning to release in the next few days. Comments are welcome.

Enhance CSV Import and Export to Allow the CSV File’s Locale to be Specified

Previously, CSV Import required that the input CSV file be in US English format.
For example, date values were required to be in MM/DD/YYYY format.
Decimal values were required to use period as the decimal separator.
Input data values were always delimited by commas.

Previously, CSV Export always wrote the CSV file in US English format.
For example, date values were written in MM/DD/YYYY format.
Decimal values were written using period as the decimal separator.
Output data values were always delimited by commas.

You can now specify the locale for both CSV Import and Export.
For example, if you specify locale “English (United Kingdom)” imported and exported dates are in DD/MM/YYYY format and data values are delimited by commas.
if you specify locale “German (Germany)” imported and exported dates are in DD.MM.YYYY format and data values are delimited by semicolons.

The application author specifies the locale on the Behavior > Action page of the “import a CSV file for this view” or “export this view to a CSV file” actions.
You can either select the locale from the “CSV file locale” dropdown or you can supply an expression that yields the locale identifier such as “en-GB” or “de-DE”.
The locale dropdown allows you to select any of the supported locales listed here Supported Locales | AppSheet Help Center.
The locale expression allows you to compute the locale identified. For example, you might store the locale identifier for each user in a field in that user’s UserSettings.
Alternatively, you might store the locale identifier in a normal table and retrieve it using a LOOKUP expression.

Existing “import a CSV file for this view” and “export this view to a CSV file” actions are automatically updated to use locale “en-US” for backward compatibility.
You can override these default locale settings using the Editor.

The CSV field delimiter is taken from the Windows CultureInfo.TextInfo.ListSeparator for the specified locale.
For example, for “English (United States)” and “English (United Kingdom)” the delimiter is comma.
For “German (Germany)” the delimiter is semicolon.

Import and Export are symmetric. If you Export in a locale you can (and probably should) Import in that locale.

I will be including documentation for CSV Import in the online documentation.

Koichi_Tsuji

Hi Phil-san,
Thanks you for bringing this enhaunsement to us, which would help especially those who are working on non-us locale.

I was curious about this quote part of your mention. It sounds like we are able to use Usersettings and dynamically change the local to import and export. Then do we call Usersetting(“locale”) in action setings.
Anyway, we will learn this part once the feature become available in a few days.

Phil

You can define you own text field in UserSettings to contain the locale. For example, you might call the field “MyCsvLocale”. The field would contain a locale id value such as “en-US”, “de-DE”, or “ja-JP”. The expression in the “CSV file locale” property would then be the expression USERSETTINGS(“MyCsvLocale”)

Koichi_Tsuji

Thanks Phil san, I got it .
I may come up with further query after hands-on testing with feature, will be coming back if we do.
Anyway looking foward to testing it soon.

Lynn

@Phil
That’s good news. I will be able to revive one of my apps.

Phil

The following articles have been partially updated but significantly more work remains.

Actions: The Essentials | AppSheet Help Center now lists “Import” as well as “Export”.
Export This View to a CSV File | AppSheet Help Center partially describes Export
Import a CSV File for This View | AppSheet Help Center partially describes import
Configuring the CSV Locale | AppSheet Help Center describes configuring the CSV locale
https://help.appsheet.com/en/articles/4671936-supported-locales describes the locales including date, time, CSV delimiter and other relevant data for CSV Import and Export.

Koichi_Tsuji

Thanks Phil san for all of your hard works over this project.
Will examine those revised docs with care.

Lynn

Hi @Phil
I have tried a new Import CSV action but I dont see anywhere for the Locale.
Do you have a screen shot of setting the locale thanks.

Phil

Hi Lynn,
I should have said that I have not yet released the enhanced CSV Import and Export data actions with the locale option. I am close, but it might not happen until late this week or early next. I will post here when it is released.

Admittedly the docs are getting a little ahead of the code but updating the docs is a big effort so I wanted to get that underway.

Lynn

Ok, thanks for the update. @Phil
I will try again next week.

Koehn1345

Do we know if the CSV download has been updated @Phil ? I am still getting blank downloads generated from filtered views

Ahmed_Zeidan

Hello All, I am facing an issue with the import. When I upload CSV in Russian or Greek language, I get a strange charter set instead of the normal language available in the CSV file; I have implemented CSVlocal option but with no success
Please see the images below:

I appreciate if any solution can provided to solve this issue

Phil

Hi Ahmed,

I tried a simple test case in which I imported a CSV file containing three rows of data that looked like this:

EmpName,Age,Count
Νόα,20,0
Μάικλ,30,0
Ντάνιελ,40,0

The imported names appeared correctly in the table.
You must be doing something more complex than what I tried.
Please submit a problem report that explains how to reproduce the problem.

Phil

Hi Rifad,

I tried a simple test case using a table having a Text key field with an InitialValue of UNIQUEID().
I then did a CSV Import and did not specify a key value.
It worked for me.

There must be something more complex going on in your case.
Please submit a problem report that explains exactly how I can reproduce the problem.

Phil

When adding rows, I tried both omitting the column from the CSV entirely and including the column but omitting a key value. Both worked.

If you are updating records or both adding and updating records, you must include the column in the CSV and you must specify a key value for any row being updated.

Phil

Hi Ahmed,
When you import Unicode data make certain that you save the CSV file in Unicode format.
I have updated the CSV Import documentation to explain this more fully.
See topic CSV File with Unicode Data

Import data from CSV

1 : I reckon your CSV file include [Region] column and all the value remains Empty. Then delete this [Region] column from CSV file and try to import the file.

2 : If you are not able to delete the [Region] column for some reasons, set those up.