topic Re: Read multiple excel files in Talend Studio

Read multiple excel files

Anonymous — Sat, 13 Feb 2016 04:22:37 GMT

Hi ,

Could anyone please let me know how can I design a job where I am reading from multiple excel files and loading it to a database.
I could create separate jobs each having its own

texcelfileread ----> tMap --> tOracle
texcelfileread ----> tMap --> tOracle
texcelfileread ----> tMap --> tOracle
so on...
But I would like to design in the below way or any other efficient way that you can suggest :

texcelfileread ----\
texcelfileread ----> tMap --> tOracle
texcelfileread ---/

So here I collect all the data from 3 excel files first then apply any transformations any then load it to tOracle component.
Kindly let know your suggestions asap.

Re: Read multiple excel files

Anonymous — Mon, 15 Feb 2016 08:08:36 GMT

Hi,
Do you want to load your 3 excel files into a table or 3 tables? Are your excle files same schema structure?
Best regards
Sabrina

Re: Read multiple excel files

Anonymous — Mon, 15 Feb 2016 12:43:04 GMT

Yes same schema structure is the same for all three inputs and they all need to be uploaded into the same table.

Re: Read multiple excel files

Anonymous — Tue, 16 Feb 2016 08:01:52 GMT

Hi,
Have you tried to design the workflow as:tFileList( set the file mask as *.xlsx to get your 3 excel files )-->tfileinputexcel(same schema structure)-->tMap(further processing)-->tOracleOutput?
TalendHelpCenter:tFileList component is usually used to iterate on files or folders of a set directory.
Best regards
Sabrina

Re: Read multiple excel files

Anonymous — Tue, 16 Feb 2016 09:10:37 GMT

Thanks Sabrina,
Will check that,
Could you please also let me know this error I am getting,I know there are several topics for date and I followed those,still I am facing the issue as below :

Type mismatch: cannot convert from String to Date

1) In tMap component I am using the below 3 columns.The dates are hard coded :
TalendDate.formatDate("dd-MMM-yyyy", TalendDate.parseDate("dd-MMM-yyyy", "29-FEB-2016")).toUpperCase()
TalendDate.getDate("dd-MMM-YYYY").toUpperCase()
TalendDate.formatDate("dd-MMM-yyyy", TalendDate.parseDate("dd-MMM-yyyy", "31-DEC-1991")).toUpperCase()

2) In tMap the out metadata is in the below format :
Type : Date , date pattern : "dd-MMM-yyyy"
Type : Date , date pattern : "dd-MMM-yyyy"
Type : Date , date pattern : "dd-MMM-yyyy"
Could you please tell what change is required.

Re: Read multiple excel files

Anonymous — Tue, 16 Feb 2016 09:27:44 GMT

Hi,
Actually, TalendDate.formatDate(String, Date) return "String" and TalendDate.parseDate(String, String) return "Date"(Your outout is "Date" type). You have to parse it as Date type.
Such as,
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("dd/MM/yyyy"))
Best regards
Sabrina

Re: Read multiple excel files

Anonymous — Tue, 16 Feb 2016 11:02:24 GMT

Thanks Sabrina, for your reply,
But then in that case how could I get this output correctly :
As the below is giving error :

TalendDate.parseDate("dd-MMM-yyyy", TalendDate.formatDate("dd-MMM-yyyy","29-FEB-2016" ).toUpperCase())

Exception in thread "main" java.lang.Error: Unresolved compilation problem:
Could you please help me out.

Re: Read multiple excel files

Anonymous — Tue, 16 Feb 2016 16:56:44 GMT

Hi Experts,
Could you please help me out here.
All that is required here is converting a hard coded string ("29-FEB-2016") to date format.

Re: Read multiple excel files

Anonymous — Wed, 17 Feb 2016 07:52:19 GMT

Hi Team,

Any help here ?

Re: Read multiple excel files

Anonymous — Wed, 17 Feb 2016 09:12:38 GMT

Ok the below seems to be working :

TalendDate.parseDate("dd-MMM-yyyy","29-Feb-2016")