Apply the same transformation to many single Excel files and put them all together in one Tibco file. Not manually every single time, of course!

[Mahla Mirzaee Kakhki]

Hi guys,

I have many Excel files that I get them daily.(so every day, one new Excel file).

How should I apply the same transformation to for example each thirty of those Excel files and bring them all in one Tibco file for comparison). Not that I do it manually thirty times for each Excel file that I add!😕

Thanks for your help in advance..😊

[Kevin Flynn]

I have done something similar in the past. It really depends on how many excels you are talking about and the naming format.

Using Automation Services:

In my case, all the files were <filename1>.<yyyyMMdd>.xlsx, <filename2>,<yyyyMMdd>.xlsx

I then used Iron Python to add rows to the current data table. The current Dataset would have 1 column you would manually set to the Current Date. so it would look like "<column 1>,<Column2>, <ImportDate>"

I would then either use that same dashboard to do the analysis or in most cases, I would use Automation Services to export the data to the library where other dashboards would hit it.

I would set up the Automation Services job to open the analysis and run the Iron python. I normally would put the Iron python script to fire on document property change and set the Document Property with Automation Services. Then I would have Automation Services save the data to Library

The less time-consuming way:

The other way I did it is with an ETL process to just import the excel files into a SQL table and hit it all the time. (I used it this way due to the client already having software that was easy to create the processes).

All the times I did it though the condition of the Naming of the files was the same. <Filename>.<yyyyMMdd>.xlsx so we could find the current day's files and only add them.

[Kevin Flynn]

Based on my previous post:

You can possibly use IronPython to loop through the files in a folder to process each file also.