B4J Code Snippet CSV to B4x Localizer tool

Hi all,

Since the tool for converting excel to sql-db recently stopped working for me completely. No matter which XLSX file I tried (even old Excel 2007 files, Libre Office), the tool kept crashing due to missing POI classes such as:

Code

ThemeDocument$Factory

After some digging it became clear that the POI version bundled with B4J cannot read modern XLSX files that contain themes or styles. Or creates a empty database

✔ Working Solution​

The workaround that finally solved it for me:

1. Save your Excel file as .csv instead of .xlsx​

This gives you a clean, comma‑separated file without themes, styles or XML structures that POI cannot handle.

2. Use a small B4J tool to convert the CSV into SQLite​

The SQLite database is generated using the same structure that B4XLocalizator expects.

I’ve attached a ZIP file containing the small B4J converter app.

⚠ Important Notes​

• Make sure to enable the jSQL library in your B4J project.
• Ensure the correct SQLite JDBC driver is present in your Additional Libraries folder.
• The messages in the tool are written in my native language, but they can easily be translated to English if needed.

If this helps anyone who is still using B4XLocalizator or maintaining older projects, feel free to improve or extend the tool.
Note: this program was created by Gemini bot
Note2: B4xLocaliser itself works without any problem. https://www.b4x.com/android/forum/threads/b4x-localizator-localize-your-b4x-applications.68751/

 

[emexes]

For people who haven't yet committed to a particular localisation method:

The one program I was involved with that worked with multiple languages, we used Google Sheets (spreadsheet) which can access Google Translation using the GOOGLETRANSLATE function. Actually, now I think about it, we were using Alta Vista, but the principle was the same, just less automated.

What we then did was backcheck the translation in the reverse direction, and flag for human intervention any that didn't come back the same as they went in.

eg "Now is the time" translates to "Jetzt ist die Zeit", and "Jetzt ist die Zeit" translates back to the same "Now is the time", so it's probably a good translation.

whereas "Hold your horses" translates to "Ruhig Blut!", but "Ruhig Blut!" translates back to the different "Keep calm!", so we'd double-check that manually. Actually, if the initial customer in the language was friendly, we'd just leave it as is and supply them with a printed list of the translations we weren't sure about, and they'd usually come back the next day with their native-language-speaker suggestions which we'd immediately incorporate into the translation file and somehow get it back to them.

We were using a 16-bit BASIC (Microsoft PDS 7.1) so we couldn't store the translations in memory. Instead we had a function that we called with a phrase number that would do a random-access lookup into a file, ie in the program code it looked like PRINT Phrase$(3779), and then we had a coding tool that would go through all the source files and add(/update) comments to all the Phrase$() calls eg PRINT Phrase$(3779) 'Hello, World!' The same coding tool would find instances like Phrase("Hello, World!") and lookup/add the text in/to the translation file, eg find that phrase/line 3779 in the translation file is "Hello, World!" and then update the code to Phrase$(3779) 'Hello, World!'

In a sample of the system that I apparently made here in 2023 and/or 2021, I included a column that stores the translations as a Map, that could be used in develop systems that don't have the memory restrictions that we worked under. ie basically using a Map instead of an SQL database.

Link to sample Google Sheet

 

[aeric]

Have you checked:

B4X Localizator 2-Way File Converter (strings.db <-> strings.xlsx)

This is a 2-way converter for B4X Localizator files which is a combination between Erel's B4J Localizator Convertor (xlsx->db) and my Localizator File Converter (db->xlsx). GitHub: https://github.com/pyhoon/localizator-file-converter-b4j Download (22MB)...

www.b4x.com

this work?

 

[MbedAndroid]

Have you checked:

B4X Localizator 2-Way File Converter (strings.db <-> strings.xlsx)

This is a 2-way converter for B4X Localizator files which is a combination between Erel's B4J Localizator Convertor (xlsx->db) and my Localizator File Converter (db->xlsx). GitHub: https://github.com/pyhoon/localizator-file-converter-b4j Download (22MB)...

www.b4x.com

this work?

no. creates a database of 0 bytes. And the messages are not correct, but that's a easy fix

 

[Erel]

Not sure that I understand. Which problem have you encountered with: https://www.b4x.com/android/forum/threads/b4x-localizator-localize-your-b4x-applications.68751/ ?

It should work with xlsx workbooks.

 

[MbedAndroid]

spend already a couple of hours on it. Or you get a crash. Or it creates a empty database. To be sure i downloaded the POI and the code again, tried it. No crash this time but db empty. (attached, the db should be around 170kb, now 3kb)

Using the latest b4j.

The csv convertor is a good alternative. Wheter you take the excel file or the exported cvs. The result is the same.

 

[aeric]

I believe the project is wrongly configured. I don't remember we have jPOI. If I am not mistaken, we use XLUtils which depends on POI dependencies.

 

[MbedAndroid]

i got this on 3 computers. Here the screenshot sql viewer of your app.
Strings1 is how it should be.
Strings is the output of your app. Database empty no records.
So far i remember this is already for years but i never mind as i didnt need to update the languages.
Yesterday i tried the get a build on all 3 computers. Or a crash pointing to POI. Or empty database.
Whatever it's fine for me. Just saving as .csv and i got the build i need with the tool i supplied. It's fast and not depending on POI
(I think the issue came when POI became a internal lib or was updated tot 5.1)

 

[aeric]

jPOI is not a B4J internal library that should be checked in the IDE. If I am not mistaken, it should not have XML file thus it should not be listed in the IDE libraries manager. It is linked to the XLUtils automatically as a dependency.
If you have that listed, maybe someone has modified it.

Sorry, there is indeed jPOI 5.0.1

XLUtils / jPOI 5 - Read and write MS Excel workbooks

As written here, I plan to make it easier to read and write Excel workbooks. The solution is based on three components: - Apache POI - https://poi.apache.org/ Large open source project that provides APIs for Microsoft documents. Note that the files are accessed directly, it doesn't depend on the...

www.b4x.com

Edit: I am not in front of my PC. I have checked my 2-way file converter code on GitHub. The project depends on XLUtils but not jPOI. As I written, I think the jPOI should not be checked in the libraries manager.

 

[aeric]

CSV format is simple to parse but my concern is the comma can be wrongly parse if the value contains comma.

So far I never face any issue with B4X Localizator so I suspect there is something wrong in the setup.

Can you upload your project which shows the error?

 

[MbedAndroid]

as you see in the screenshot it's listed as internal. I tried a couple of hours to fix this issue . Older jPoi's, other computers, taken the output from Openoffice, Libreoffice, Exel 2007. Tried a new excel sheet etc . Removing the Poi dependency's and installing it again.
Result was still or a crash, or empty database.

For me it's fine. I got my build through another method and shared it with the community for others.

 

[aeric]

I just tested your attached project, I get an empty sqlite db.
Here is my test csv file.

 

[Intelemarketing]

Hi all,

Since the tool for converting excel to sql-db recently stopped working for me completely. No matter which XLSX file I tried (even old Excel 2007 files, Libre Office), the tool kept crashing due to missing POI classes such as:

Code

ThemeDocument$Factory

After some digging it became clear that the POI version bundled with B4J cannot read modern XLSX files that contain themes or styles. Or creates a empty database

✔ Working Solution​

The workaround that finally solved it for me:

1. Save your Excel file as .csv instead of .xlsx​

This gives you a clean, comma‑separated file without themes, styles or XML structures that POI cannot handle.

2. Use a small B4J tool to convert the CSV into SQLite​

The SQLite database is generated using the same structure that B4XLocalizator expects.

I’ve attached a ZIP file containing the small B4J converter app.

⚠ Important Notes​

• Make sure to enable the jSQL library in your B4J project.
• Ensure the correct SQLite JDBC driver is present in your Additional Libraries folder.
• The messages in the tool are written in my native language, but they can easily be translated to English if needed.

If this helps anyone who is still using B4XLocalizator or maintaining older projects, feel free to improve or extend the tool.
Note: this program was created by Gemini bot
Note2: B4xLocaliser itself works without any problem. https://www.b4x.com/android/forum/threads/b4x-localizator-localize-your-b4x-applications.68751/

Watch out for CSV files when the data contains Customer Data like Customer Address Fields which notoriously contain commas in the Field - You can still create the CSV File but make sure it is Tab Delimitted rather than Comma Delimitted when you save it.

 

[aeric]

I think the OP has forgotten to attach a sample CSV format file.
I only can make assumptions that comma is used as the separator and the columns are same as the excel file in Erel's tutorial but I found the tool has failed to process the file.

 

[MbedAndroid]

I let Gemini adjust the project. It's now autodetect for the separator. And messages in Englisch. It reports what separator is used. The source code is there, any modifications are welcome. New zip file is closed in this post.

Example csv: FILESIMPORTED;Files imported!;Files imported!;Files imported!;Fichiers importés!;Pliki zaimportowane!;File importati!;Ficheiros importados!;¡Archivos importados!;Filer importeret!;Fájlok importálva!

 

[MbedAndroid]

I just tested your attached project, I get an empty sqlite db.
Here is my test csv file.

 

[aeric]

View attachment 171461View attachment 171462

The output is not correct. e.g, see the key "hello, {1}"
This is due to the value contains comma.

The correct output should look like the following:
(which is generated by my localizator file converter tool)

 

[MbedAndroid]

then you should need another separator.... I though you can specify in excel which separator you want. Otherwise let a bot adjust the cvs

 

[aeric]

I just reporting that this could be a possible issue with csv file.

 

[MbedAndroid]

i dont have any issue with the csv. And i think any other who want to use this convertor, knows that this is a limitation of csv format which can be avoided easily by not using separators which are bound to the local windows settings. Anyway solution seems to be found, however i cant read the results. For you to check. Included csv convertor version 1.2

version 1.2