Jump to content

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

Recommended Posts

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..😊

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...