Files connectors

Excel file

No matter if you want to access it locally or online you have to specify a connection name.

Stored locally

It is possible to add an Excel file in several ways:

By dragging and dropping it into the drop zone.
A click on the drop zone opens the Windows explorer where you can select your Excel file.
Enter the location of your file.

Don't forget to fill in the password if the Excel workbook is protected.

Stored on a FTP/SFTP

To access your Excel file on an FTP you must fill in the following fields:

FTP Server
Full location of the file
Authentication:
- Username
- Password

SFTP settings
- Key SSH
- PassPhrase

Don't forget to fill in the password if the Excel workbook is protected.

Stored on One drive / Google drive

To access your Excel file on a One drive or a Google drive, connect to your account:

Once you have logged in and authorized Biwee to access your files you can return to Biwee where you will select your Excel file.

Query definition for Excel files

Once your file is loaded, you have the possibility to choose the sheets to use. To do this you can either:

Check the sheets to load
Enter a regular expression that will define all the sheets to be loaded

⚠️ In order to load several sheets, they must have the same number of columns, the same name and be in the same order.

Method 1: Select sheets from the drop-down list

Biwee takes care of concatenating the data from the selected sheets into a single data grid. In the "Results" tab, you can see all the data of the selected sheets and an additional column indicates the corresponding sheet.

Method 2: selection of sheets by regular expression

To do this, check the option "Use a regular expression" and in the free text area enter the desired regular expression.

In this example, by entering "^Sales", Biwee will load the data of all sheets whose name starts with Sales.

Common regular expressions

Here are some regular expressions that can be useful :

select all the sheets whatever the sheetnames .
select all the sheets whose name starts with the word : ^word
select all the sheets whose name ends with the word : word$
select all the sheets whose name contains one or many numbers : \d or[0-9]
select all the sheets whose name contains one or many low case characters : [a-z]

It is possible to combine different sets, for example:

select all the sheets whose name contains numbers, letters or underscores: [a-zA-Z0-9_] or\w
select all the sheets whose name start with Sales and tha ends with a date MM_YYYY (for example exemple 05_2023): ^Sales.*\d{2}_\d{4}$

This regular expression checks if the sheetname starts with Sales with the expression ^Sales

Then, it accepts all the characters (.) repeated from 0 to many times (*), with the expression .*

Then it checks if the name ends with exaclty two digits \d{2} followed by an underscore _ then exactly 4 digits \d{4} with the end marker $

The regular expression ^Sales.*\d{2}_\d{4}$ enables to get the following sheetnames for example :

Sales_05_2023, Sales_06_2023, SalesProducts_France_06_2023, etc.

CSV file

No matter if you want to access it locally or online you have to specify a connection name.

Stored locally

It is possible to add a CSV file in several ways:

Drag and drop in the drop zone.
A click on the drop zone opens the Windows explorer where you can select your CSV file.
Enter the location of your file.

Stored on FTP/SFTP

To access your CSV file on an FTP you must fill in the following fields:

FTP Server
Full location of the file
Authentication :
- Username
- Password

SFTP Settings
- SSH Key
- PassPhrase

Stored on One drive / Google drive

To access your CSV file on a One drive or Google drive, log in to your account:

Once you have logged in and authorized Biwee to access your files you can return to Biwee where you will select your CSV file.

Properties definition for CSV files

For a better reading of the CSV file, please fill in:

Json

No matter if you want to access it locally or online you have to specify a connection name.

Stored locally

It is possible to add a Json file in several ways:

Drag and drop in the drop zone.
A click on the drop zone opens the Windows explorer where you can select your Json file.
Enter the location of your file.