Check for valid sheet name in an excel file

  • Posts: 204
  • Thank you received: 1

Check for valid sheet name in an excel file was created 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
6 years 2 months ago #15850

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

  • Posts: 8628
  • Karma: 33
  • Thank you received: 590

Replied by admin on topic Check for valid sheet name in an excel file

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
The following user(s) said Thank You: imchaz
6 years 2 months ago #15851
Attachments:

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

  • Posts: 204
  • Thank you received: 1

Replied by imchaz on topic Check for valid sheet name in an excel file

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
6 years 2 months ago #15860

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

  • Posts: 8628
  • Karma: 33
  • Thank you received: 590

Replied by admin on topic Check for valid sheet name in an excel file

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
6 years 2 months ago #15861

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

  • Posts: 8628
  • Karma: 33
  • Thank you received: 590

Replied by admin on topic Check for valid sheet name in an excel file

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
The following user(s) said Thank You: imchaz
6 years 2 months ago #15927
Attachments:

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

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies