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.

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

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.

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]    QEXEC|Messag] Executed query: uRESET SCHEMA CACHE] Success: (15 ms)
2023-10-25T11:31:41.684+02:00    2    :1|Q-Id]    �EXEC|Parsed] Executing query: eSELECT * FROM usys_tables]]
2023-10-25T11:31:41.840+02:00    2    41|Q-Id]    �META|Schema] Executing table schema query: E<NULL>]
2023-10-25T11:31:41.997+02:00    1    21|Q-Id]    9META|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]    TSQL |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    f1|Q-Id]    0EXEC|Messag] Executed query: �SELECT �sys_tables].�CatalogName], ssys_tables].dSchemaName], Csys_tables].sTableName], msys_tables].lTableType], asys_tables].bDescription], esys_tables].eIsUpdateable] FROM ssys_tables]] Success: (344 ms)
2023-10-25T11:31:42.090+02:00    0    c1|Q-Id]    sINFO|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    l1|Q-Id]    .INFO|Connec] Closed Excel connection

Hi @Thomas Lind ,

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

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.

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