Most of the people who work in departments like accounting, operations, human resources, risk, finance, etc., use Excel in their daily work. In fact, many companies have software that delivers reporting and information to users, but they continue to validate said information in Excel, why? Because they have all the knowledge of the business and the software is made by developers where most He doesn't understand the business, but this is another topic. B2XLS allows you to export data from different sources to an Excel spreadsheet, you concentrate the logic that you need to obtain from the databases in one place, which obviously allows you better maintenance when you need to change the logic and know where to have the data available for work them.
- Completely free and open-source
- Currently the connections are available for SQL Server, Oracle 18 or above, MySql 8
- All connections and script definitions in json files, very easy to understand.
Before beginning to work with B2XLS, you need to have instaled jave in your machine, probably you have installed.
- In the moment to build this tool, I have installed JDK 11.0.18 and JRE 1.8. Installation Guide
- Download the tool, is a zip file.
- Download the json files
- If you are a developer and you want to changue or view the source code, you need to work with Talend Open Studio for Data Integration, and import the LOCAL_PROJECT. I use the 7.3 version.
- The json files must be into a C:/B2XLS folder
Don't worry about "output" and "log" folder, the tool will create it.
- The db.json file, is a referential file for what database you can use in the tool, here is important to know the "name" of each DB, an example:
- The connection.json file is where you define all databases connection, an example:
- The query.json file is where you define all the scripts which will be executed in the databases. Here you can define the name of the excel file, in what sheet you want the data, an example:
Is important in this file fill the "fields" property, this data will be use in the header of the spreadsheet of each script
For example: You have the follow script
select * from schema.table
OR
select campo1, campo2, campo3 from schema.table
in the "fields" property you must to have
"fields": "campo1, campo2, campo3"
That is because in the open source version of TALEND, I don't have the obtain a dynamic schema for each script, so I solved this problem with the "fields" property.
- For run the tool, you need to execute "b2xls_run.bat", remember you must have downloaded the zip file. Download the tool.