To homepage To homepage
Solved

Loading multiple Excel files with 1 data connection using RSD

  • 5 October 2023
  • 7 replies
  • 169 views
erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi community,

I have multiple Excel files on a sFTP location, all having the same structure. I would like to load these files using 1 data connection and create a RSD file for each file/sheet that needs to be loaded.

When generating the RSD via TX, it does not contain the api:set attr=”URI” property. An example:

 

On line 2 (api:info) there is a parameter other:uri=”[...]”, but modifying this does not change anything.

Manually adding the api:set attr=”URI” does not change anything either

The data will always be loaded from the Excel file that you enter in the UI:

 

Emptying the URI field results in an error that either URI or Excel File parameter should be set:

 

Is there a way to load multiple Excel files/sheets with a single data connection using RSD files, same as we do with eg. REST API endpoints and CSV files? It will save us from creating ca. 15 separate connections :)

Best,

Erik

TX: 20.10.43

CData Provider for Microsoft Excel 2023, v23.0.8565.0

icon

Best answer by Thomas Lind 6 October 2023, 12:12

View original

7 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @erik.van.mastrigt 

You can aggregate files in that version of the Excel provider.

So I don’t think you need to use an RSD file to do it.

 

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Thomas Lind,

The files are located in different folders.

Each division has it’s own FTP-folder to fill pre-formatted Excel files with additional data. So for instance, the connection has to read Forecast.xlsx from folders sftp://folderA/, sftp://folderB/ etc

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @erik.van.mastrigt 

I see. I was trying this feature on my setups. I got this message.

So the aggregate function will not work for you as well.

You can connect to multiple folders by connecting to lowest level and turning on the include subdirectories option. The fields also exist in the Excel provider, but it will not work for sftp.

I don’t see any way of doing this with a RSD file, other than using it to generate multiple excel tables in a data source instead of just one for each data source.

I tried to generate a RSD file and it does not seem like you have many options to customize them.

I have asked CData about why aggregate doesn’t work.

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Thomas Lind ,

Do you have a working example with the aggregate function?

The settings tell that you can set a local folder in URI if aggregate is enabled:

 

However, when I enter a local folder, the log says that I should enter a .xlsx file:

 

Log file:

2023-10-25T11:31:41.637+02:00    1    [1|Q-Id]    [EXEC|Messag] Executed query: [RESET SCHEMA CACHE] Success: (15 ms)
2023-10-25T11:31:41.684+02:00    2    [1|Q-Id]    [EXEC|Parsed] Executing query: [SELECT * FROM [sys_tables]]
2023-10-25T11:31:41.840+02:00    2    [1|Q-Id]    [META|Schema] Executing table schema query: [<NULL>]
2023-10-25T11:31:41.997+02:00    1    [1|Q-Id]    [META|Schema] Executed  table schema query: Table: [<NULL>] Error: You must specify an Excel file: Set the URI property to an .xlsx file or .xlsm file.
2023-10-25T11:31:41.997+02:00    1    [1|Q-Id]    [SQL |Transc] You must specify an Excel file: Set the URI property to an .xlsx file or .xlsm file.
2023-10-25T11:31:42.012+02:00    1    [1|Q-Id]    [EXEC|Messag] Executed query: [SELECT [sys_tables].[CatalogName], [sys_tables].[SchemaName], [sys_tables].[TableName], [sys_tables].[TableType], [sys_tables].[Description], [sys_tables].[IsUpdateable] FROM [sys_tables]] Success: (344 ms)
2023-10-25T11:31:42.090+02:00    0    [1|Q-Id]    [INFO|Connec] You must specify an Excel file: Set the URI property to an .xlsx file or .xlsm file.
2023-10-25T11:31:42.122+02:00    1    [1|Q-Id]    [INFO|Connec] Closed Excel connection

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Hi @Thomas Lind ,

I’m still not getting this to work. Would it be possible to ask CData for help?

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @erik.van.mastrigt 

I unfortunately haven’t gotten any proper responses since my initial request for a solution. I can share that however.

Hi Thomas,

Thank you for reaching out.

I made some tests regarding the issue that you are facing and would like to inform you that currently, our Excel ADO .NET connector does not support setting the URI property to a local folder path.
So, unfortunately, you will not be able to connect unless you provide the file name in the URI property. This is because the driver will look for the file that you specified in the URL to represent the data that will be accessed by any application.

Additionally, I wanted to let you know that a feature request has been opened to consider the possibility of querying multiple Excel files from a folder path. Our team is currently dedicated to investigating its feasibility and potential implementation but would like to note that it may take some time before we can determine if this feature will be implemented at all. It is important to note that this is not a simple change. It involves complex considerations and development efforts, which means it might not be ready for implementation shortly.

Regards,
Dorando Myslimi

So it seems like you have no option to use this yet. I will ask for a status regarding this new feature.

erik.van.mastrigt
Rank
Userlevel 3
Badge +2

Ok, that’s unfortunate, but thanks for asking. I’ll start setting up separate data connections instead.

Reply


Terms and ConditionsCookie settings