I know of services which are aimed at very large audiences (thousands of users) where standard formats - eg xml, tab-separated or CSV - are the only options. I would have thought this is the most common/typical situation and offering Excel import would be unusual. (I wouldnât have thought there would be any point or use in offering Word or PDF import option.)
I appreciate your point about the difficultly users might have in following the procedure you outline - surely this is an example of what Excel macros and VBA are for?
Or, because they are not familiar with them, youâll need to educate them. Consider back when CVV came out. Nobody knew what it was, so every site accepting a credit card added instructions on where to find itâsome with tooltips, some with pictures, etc.
The reason csv works across so many platforms is because of its simplicity. There are no versions to deal with and no proprietary formats.
Educating people on File > Save As > CSV seems very doable and well within any excel users ability.
An xls file is more then just some spreadsheet. It contains a workbook with multiple worksheets. It contains formulas, styles, charts, metadata etc.
Also xls is not an open standards, it is a proprietary binary format from Microsoft.
This does not negate the fact that there are many services that in addition to csv support xls/xlsx format. Why? Because regular users are much more likely to use xls/xlsx than csv. Many people do not even know what csv is.
I donât think thatâs a valid comparison. In the case of a credit card, there is no alternative to using a CVV code. Therefore, if the user wants to pay for the purchase on the site by credit card, he is forced to specify the CVV code, regardless of which site is located.
With data import, the story is different. If I offer the option of importing only in csv format, there are a dozen alternative services that offer a similar service, but with support for importing in xls/xlsx format.
If the user maintains data using characters outside the ASCII standard (this is very, very, very many people around the world), he can not just save the table in csv, because the data in this case will be distorted. In this case, it needs to do the conversion procedure to the appropriate format with the correct encoding. And these are additional steps. An ordinary user is essentially a consumer, if you do not have a unique service, he will simply close the browser window and open the site of an alternative service that supports everything necessary for simple operation.
I perfectly understand your logic based on the technical side of the question. I would like you to understand my logic based on customer focus and UI / UX development. In this vein, it is necessary to follow the principle of no counterbalance - no need to explain to the user how to do, if you can do as the user wants.
As Patrick said xls is a closed binary proprietary format from Microsoft. So even if we want to we canât and are not allowed to use it.
Yep, that is a tough one. How about an external Excel to JSON converter?
I have to admit there seems to be a massive shift to using google sheets instead of microsoft excel these days and google sheets seem to very easily import excel files.
Maybe try just as a test taking your excel file with its special character set into google sheets and then exporting to cav from there just to see if it removes a step in the process.
If it does then see what options are available in the google API for sheets.
I have recently tried out (https://sheety.co/) which can turn any google sheet into an API. I have then been able to pull the data into Wappler using the API source tool quite easily.
Wonderful name 
Why not write an Excel Macro for this client?
You can code it or record the export to CSV.
I use this library for parsing Excel file in PHP: https://packagist.org/packages/phpoffice/phpspreadsheet
In my case, I havenât seen any ASCII related issues. Have worked with pretty standard data files so far.
But I am sure that can be easily worked out with PHP.
The way I use this is:
- I have a separate PHP file in the same project which I have configured to work as an API end point. From the file name sent in the request params, this PHP script passes that file to the library, iterates through row & columns to create a JSON array of the data as required, and finally returns this JSON as response.
- In wappler, I create a regular file upload in UI which calls a server action.
- This server action first uploads the file, then calls the PHP script via API server action step.
- Lastly, I perform DB activities upon the data received from API action.
This process works quite well for me so far. And I get to keep most stuff under Wappler.
I donât know if there are any legal issues with integrating this within Wappler itself (as @George wrote), but if they can, I am sure they can come up with a UI to allow processing of Excel data files in row/column format directly in the server action - complete with headers, worksheet and encoding handling etc.