- Posts: 204
- Thank you received: 1
Check for valid sheet name in an excel file
6 years 7 months ago #15850 by imchaz
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).
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
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.
6 years 6 months ago #15851 by admin
Mike
ETL Architect
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.
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
Please Log in or Create an account to join the conversation.
6 years 6 months ago - 6 years 6 months ago #15860 by imchaz
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.
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
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.
6 years 6 months ago #15861 by admin
Mike
ETL Architect
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
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.
6 years 6 months ago #15927 by admin
Mike
ETL Architect
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.
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
Please Log in or Create an account to join the conversation.