Check for valid sheet name in an excel file

More
6 years 7 months ago #15850 by imchaz
If I load an exel file file into a directory called PROCESS, I would like to validate within that file if any of the sheet name (tabs) contain an exact sheet name.
it has to have at least one of the following names as a valid sheet name (exactly typed).
  • School
  • Library

If it contains one of the the sheet names listed, I would like to move the file to a Directory called GOOD and if it doesnt contain any of the names, move the file to a Directory called BAD.
How could I check for the existence of a sheet name in question?

Thanks,
Chuck

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago #15851 by admin
I have created a working example for you,
It has a transformation which creates list of "GOOD" files which is later used by loop object.
All the files in the attached zip file.


Mike
ETL Architect
Attachments:
The following user(s) said Thank You: imchaz

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago - 6 years 6 months ago #15860 by imchaz
Hi Mike,
Thanks again for giving me a working example. It was very helpful and I was able to adapt it to my folder structure and it works.
I was wondering if you can give me an idea how I can modify your example?

In your working example, it is looping through the spreadsheets to see if it has any of the following sheet names in the excel file.
  • Library
  • School

Is it possible that when looping through the excel file, before it moves it to a BAD folder because it does not have the sheet name Library or School that it checks if the sheet name falls with a list of name and I could replace it with the proper respective sheet name?

For example, the excel file has the following sheet name "Public Lib " or "Lib System", if it sees that sheet name, I would like to rename the sheet name to "Library" as for the same goes for "Public H.S. " or "Elementary School", I would rename the sheet name to "School", then the file would be moved to the GOOD folder.

How could this be achieved from your working example?

Appreciate it.

-Chuck
Last edit: 6 years 6 months ago by imchaz. Reason: additional clarification

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago #15861 by admin
Renaming sheets is much more complicated issue.
We will investigate it,
If it is possible at all

Mike
ETL Architect
The following user(s) said Thank You: imchaz

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago #15927 by admin
Hi

Regarding renaming renaming Excel sheets.
In the latest version we added Excel Operation Action.
I think it is the most convenient option for you.


Mike
ETL Architect
Attachments:
The following user(s) said Thank You: imchaz

Please Log in or Create an account to join the conversation.

Save
Cookies user preferences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accept all
Decline all
Read more
Marketing
Set of techniques which have for object the commercial strategy and in particular the market study.
Google
Accept
Decline
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline
Functional
Tools used to give you more features when navigating on the website, this can include social sharing.