Hello
Every day, I have a new Excel file in the Azure storage or OneDrive account. I use OneDrive to test phase. These file’s name change with a new day. How can I this multiple Excel files to one? Is there a option for Excel like aggregate files option in CSV?
Best Regards
Best answer by Thomas Lind
View original
+7
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.
+5
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
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.
+5
Hi
It is the Excel one that does what I described.
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
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
+5
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
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.
+5
If you connect to Excel files in a FTP server. It is explained in this guide.
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.
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.
+4
Hi
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?
+7
Hi
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.
+2
Hi
I faced exactly the same issue two days ago, which I posted as a reaction here:
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
+4
Hi
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
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.
+2
+5
Hi
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.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
