Hello
Task: import xls/xlsx file.
Share how best to organize the import due to third-party solutions and which ones? Wappler does not support importing this format, but the solution is required now.
There aren’t many libraries that support xls/xlsx files. Easiest way is to open the files in Excel and then export them as csv, then you can use the importer supplied with Wappler.
Here’s the thing. If xls/xlsx tables contain characters beyond the ASCII standard, all data will be distorted during normal CSV saving. And to prepare in this case the correct csv file, you need to do a multi-step procedure. In this case, if any errors are made, the file will not be suitable for import.
Excel is a fairly common program all over the world. My client is from Russia, but works with European countries and Asia. His contractors, like himself, use Excel in their work. In such a situation, a simple xls/xlsx import tool is needed, because the procedure for converting a file to csv is too complicated for the average user and the probability of error is too high (due to the fact that the tables contain characters not included in the ASCII standard).
Since Wappler has a lot of users who are not in English-speaking countries, it seems to me that the lack of common international file formats for import is a serious limitation. I think many have faced or will face in the future with a similar problem.
As for my situation, I will be grateful for advice. What library is better to use and how it is easier to integrate it into the project on Wappler? I am using PHP and MySQL in a project.
Didn’t know it only exported as ASCII, but after a short search I found that you can export as Unicode Text (*txt). It will create a tab separated file.
Yes, I know this procedure. That’s what I was talking about. I’m using it myself at the moment. But I can not offer it to the client, because there are too many steps, and use the import will not be administrators, but ordinary users.
The procedure is too multi pass:
- It is necessary to save xls/xlsx in unicode format.txt
- Then replace the tabs with signs separators
- Then save the file in utf8 format.txt
- Then change .txt on .csv
If somewhere in this procedure the user made a mistake, then the import will be impossible. From the point of view of usability to leave the functionality of the created service in such a raw form for users will be a big mistake on my part.
In the csv import step you can set a Delimiter. Try using a tab character there, then they can just upload the unicode txt file to the server for import.
In the csv import step, you cannot use a tab as a separator character.
Did you test with \t as value? Do you use ASP or PHP?
I’m using PHP. Just tried the \t value, but unfortunately an error occurs while importing.
I import tab-separated files quite often. I haven’t come across any problems.
Could the error be related to a different issue?
Could you post the error that you are getting.
I would look into zapier or integromat and find a third party tool to handle the conversion from there before returning the resulting file in your application.
Happened what I wrote above. I exported xlsx file in unicode.txt but forgot to resave the txt file in utf8 format.txt. This caused an error. Thus, we can consider this a temporary solution, but in the future we will still have to look for a solution for the project and integrate it, because already now I foresee how the client swears about the fact that his employees are constantly suffering with imports…
I also have a question about a strange situation related to imports.
Here’s the file I’m importing: newtest4.zip (1.6 KB)
All its values are normal:
For some reason, when importing, the value of the first column of the first record is zeroed. All other values are imported normally. And all subsequent entries have no problems either:
What is the reason for this and how to solve this problem?
It has a BOM at the start of the file, that is probably causing the zeroed first record since the import script doesn’t handle that correctly.
And how can this problem be solved?
Try this update, place it in dmxConnectLib/modules. It should detect the BOM and skip it if found.
import.zip (917 Bytes)
2 Likes
That is possible, try with the new import script if it is solved.
1 Like
Yes. Now the import is carried out correctly. Thank you for your timely help!
PS It would be great if in the future Wappler could support importing more formats, not just csv and xml. Because these formats are used mainly by advanced users or site administrators. If we are talking about ordinary users, then these formats are not familiar to them. They use the more familiar formats xls/xlsx, pdf, doc/docx, etc. As a result, it turns out that now with the help of Wappler it is impossible to make a service aimed at a wide audience of users, in which data import from users is widely used, if you do not use third-party services or libraries.