multiple Excel files to one

Hi,

yes with some caveats: https://cdn.cdata.com/help/RXJ/ado/RSBExcel_p_AggregateFiles.htm

The aggregation functionality only works for local connections, so I expect some of the cloud providers may not allow it without doing something like creating a mapped drive to a share. It can be useful to use a template Excel stored separately as the https://cdn.cdata.com/help/RXJ/ado/RSBExcel_p_MetadataDiscoveryURI.htm to avoid type detection issues.

@ebay 

This feature came with the newest release for 23, so you need to be sure you are on the newest edition of the provider for the option to exist.

I do not have a lot of knowledge of how it works due to this, but for CSV it is really important that the field names are exactly the same across all files, if say a field is called Budget JAN and in the next file called Budget FEB it will not be able to merge them, even if you use the MetaDataDiscovery field.

Thanks @Thomas Lind  and @rory.smith 

I'm using the latest version  Microsoft Excel Online  23.0.8565.0. But I couldn't figure out which feature did that.

I'm not very good at Cdata, unfortunately.

Hi @ebay 

It is the Excel one that does what I described.

• Connect to an Excel file Tutorial

If you use the Excel Online it does not seem like it can merge files together into one file.

Excel Online one can connect to multiple files in one data source though, so you will have to decide what you want.

Hello @Thomas Lind 

The customer mentioned that they can convert xlsx data to JSON. Currently, we are avoiding the csv file format. Is there a feature available in TX that allows us to combine all JSON files in one file?

Best Regards

@ebay 

Yes, if the JSON file has the same structure you can point at a folder and it will attempt to merge the fields into one automatically. You can also use asterisks in the name to specify what file names needs to be merged.

URI C:\files\JSON\File*.json

Let me know how it goes.

Hello @Thomas Lind 

Unfortunately, we cannot proceed with JSON. Right now, I only need to connect to Excel files. The files are located locally, and an FTP server is set up. Therefore, I'm connecting via the FTP URL.

Do I have to upload these files to SharePoint or OneDrive? Is there another way? Because then I would need to do OAuth. Is it possible to do this without OAuth?

Additionally, when an Excel file is uploaded, I want to see it directly in DSA. How can I do that? Can you provide an example FTP URL? I am using TimeXtender 20.10.28.64.

If you connect to Excel files in a FTP server. It is explained in this guide.

• Connect to an Excel file Tutorial

You can also connect to files on an FTP server like below.

Set the URI to the address of the server followed by the path to the Excel file. To authenticate, set User and Password.

URI=ftps://localhost:990/folder1/file.xlsx; User=user1; Password=password1;

Since you are in the LTS releases this guide for CSV shows how it can be set up for that.

https://legacysupport.timextender.com/hc/en-us/articles/360016346972-Connect-to-CSV-with-CData#connect-to-csv-files-on-a-ftpsftp-server

There is not really any difference between the two methods, only that you generally need to point at a file.

The issue connecting to multiple files will also exist here as you can’t do as with the Excel Online provider.

If the files share the same structure there is a new feature in the newest edition of the Excel provider to aggregate similar files into one.

Hi @Thomas Lind ,

I'm trying to use the Aggregate Files option in de CData Microsoft Excel 2023 connector using these settings, all files starting with ‘Book’ need to be loaded into TimeXtender.

Aggregate Files: True
Buffer Changes: False
URI: C:\Users\xxxxx\Desktop\Temp\Book*.xlsx

The test connection is failing and shows this error message:
 

Synchronize task is is succeeding.

In the transfer task I cannot select tables because it is telling me no tables are synchronized. 

Any idea how to fix this?

Hi @bas.hopstaken ,

does the user that the test connection runs under have access to the user folder you are pointing to? It is usually safer to use a general folder all accounts will be able to reach.

Hi @bas.hopstaken ,

I faced exactly the same issue two days ago, which I posted as a reaction here:

• Loading multiple Excel files with 1 data connection using RSD

In TX 20.10 it has the same issue with field URI

It’s odd, as the documentation says that you can point to a folder when Aggregate is set to True

https://cdn.cdata.com/help/RXJ/ado/RSBExcel_p_AggregateFiles.htm

Hi @rory.smith ,

I know it is not best practice to point to a user-specific folder. But the file path I shared in this article was on my local PC just to test if I get it working correctly on my local PC. 

But nevertheless, a good suggestion is to check if the service account user has access to the folder. 

@bas.hopstaken Thomas posted a reply from CData in the other topic. Sadly, and despite what is written in the documentation, aggregation of Excel files is not supported (yet).

https://support.timextender.com/rsd%2Dfile%2Dcustomization%2D96/loading%2Dmultiple%2Dexcel%2Dfiles%2Dwith%2D1%2Ddata%2Dconnection%2Dusing%2Drsd%2D1489?postid=5293#post5293

Hi @erik.van.mastrigt & @bas.hopstaken 

I mentioned to CData that this aggregate option did not work and they responded that this had been fixed.

We have a new release 23.0.8866.0 where the aggregate files work. I just updated my Excel guide to show this and made a short guide about how to set it up in it. Essentially you point at a folder, turn of Buffer Changes and sets it to Aggregate the files.

Aggregate+files