topic Re: read specific spreadsheets in several excel files in Talend Studio

read specific spreadsheets in several excel files

citrods — Thu, 07 Jan 2021 11:03:42 GMT

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

Re: read specific spreadsheets in several excel files

Prakhar1 — Thu, 07 Jan 2021 12:16:13 GMT

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.

Re: read specific spreadsheets in several excel files

citrods — Thu, 07 Jan 2021 15:38:57 GMT

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

Re: read specific spreadsheets in several excel files

Prakhar1 — Fri, 08 Jan 2021 06:25:58 GMT

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.

Re: read specific spreadsheets in several excel files

citrods — Fri, 08 Jan 2021 11:06:26 GMT

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

Re: read specific spreadsheets in several excel files

Prakhar1 — Sat, 09 Jan 2021 12:33:58 GMT

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.

Re: read specific spreadsheets in several excel files

citrods — Mon, 11 Jan 2021 09:39:33 GMT

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?

Re: read specific spreadsheets in several excel files

Prakhar1 — Mon, 11 Jan 2021 12:05:58 GMT

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.

Re: read specific spreadsheets in several excel files

citrods — Wed, 13 Jan 2021 18:36:34 GMT

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

Re: read specific spreadsheets in several excel files

Prakhar1 — Thu, 14 Jan 2021 06:03:59 GMT

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"

Re: read specific spreadsheets in several excel files

citrods — Thu, 14 Jan 2021 14:07:02 GMT

exactly. this is what i figured out to avoiD error message if an excel file doesnt have the worksheet which is named "year XXXX".

Also, the fake spreadsheet doesn't need to contain any data. It can be empty as I don't want to read information from that fake worksheet. just wantto avoid error messages.

I'm OK with to other solutions

Re: read specific spreadsheets in several excel files

Prakhar1 — Fri, 15 Jan 2021 06:18:10 GMT

I have one idea where you donot need to add spreadsheet of "Year".

What you can do create a master and subjob concept.

1) Master Job

tFileList -> iterate -> tRunjob

2) Child Job (Subjob)

Create a subjob and create the logic to read the Excel and store the data and then use this job in tRunJob.

in that subjob add a context.FileName and pass this context value using the master job.

Also untick the DieOnError setting in tRunJob

By this logic your job will throw the error message but will not fail and continue for the Excel having "Year XX" spreadsheet.

Re: read specific spreadsheets in several excel files

citrods — Tue, 19 Jan 2021 21:03:13 GMT

I'm a beginner, i didn't understand all theses stuffs😅

what is a master job and what is a child job?

what is contextFILE?

do you have a screenshot and all the settings that I have to use?