read specific spreadsheets in several excel files

[citrods]

Hello

I have lots of excel files to read.

Each excel files have many speadsheets but I don't have to real all of these spreasheets.

The spreadseehats have different names and I'm intersted in spreashsheets which are named "Year 1990", "Year 1992", "Year 1992" and so on. Not all years exist and I don't know the exact list of years.

Actually I'm interested in spreadsheets whichs contains "Year".

How I can use tfileinput and set the parameters so that I can read some specific spreadsheet?

I know that I can tick a box which will allow to read all spreadshest. I can also write the name of the spreadsheet I want to read. But I want something in between ; just read spreadsheet which contains "year". Is it possible?

thanks

[Prakhar1]

Hi,

Try to use the regex option in the tFileInputExcel component and write expression as I have write in my job.

Check it and let me know if it works for you or not.

[citrods]

what does ^\\d(4)$ mean ?

Idon't see the connection with my spreadsheets which contain the word "year"

I believe I have to define a variable called "year" somewhere abd spcify in this expression to check for this variable, right?

Anyway, I'Il try tonight and let you know

[Prakhar1]

Hi @citr dsz​ ,

This is a regex expression to check if the value is like a year (for ex - 1990, 1991 etc.) , But now I realized that the name of your spread sheet is like "year 1990", right ?

So, try this "^.*Year.*$" in place of "^\\d(4)$"

Also if there is some more specific requirement could you please share screenshot of the spread sheet name so that I can prepare the regex accordingly.

[citrods]

I have this error messageIs it because Some of my files doens't have any spreadhseets which contains the word "Year"?

I tried as you told me

 

Here is what the excel file looks like:

But i have files which don't have worksheets without "Year" and I don't care about them

[Prakhar1]

I am using the same thing and i am not getting any error :

 

Yes you are right, it is because some of your files do not have the word "Year" in the sheet.

I am getting the same error as yours only when none of my sheets have there name starting as " Year " .

You should test with one file having year in the sheet name, if it works then you can handle the exceptional cases.

[citrods]

I will make sure all my files have a sheet with the oword "year"

for my curiosty, O you know how I can make a test?

[Prakhar1]

Take one of your file and use it in tFileInputExcel and then create schema according to it.

I have also attached a screenshot in the above answer that how i setup tFileInputExcel while testing.

Print the values using tLogRow.

In my testing file i have 3 sheets having different names but i was getting output only for the "Year 15" sheet.

Let me know if need further support.

[citrods]

hello again,

I added manually a worhseet named "year" to all my excel files and it works properly.

For the next times, i'd like to do it automatically.

1) how can you make a test and avoid an error message if your excel files doesn"t contain a worksheet named year.

if I can't make a test, can you automatically add a worksheet to all your files without deleting information in the files?

thank you

[Prakhar1]

Before continuing, correct me if i am wrong , you want below things

1) read all excel and just extract data from spreadsheets names as "Year XX".

2) If some of the excel don't have spreadsheets named as "Year XX", then add a new spreadsheet with name "Year"