topic Re: [resolved] Loading Excel Data into Oracle Database in Talend Studio

[resolved] Loading Excel Data into Oracle Database

Anonymous — Sat, 16 Nov 2024 11:24:55 GMT

Hi All,
I am new to Talend ETL. Can someone help me out with detailed steps of how to read an excel file and store it into Oracle database using Talend ETL version 5.6? Further can I write some rules of validation to check if the data is valid then store in database, else notify through e-mail to the intended users. Also, how do get the source code of all these activities and then integrate the same in a Spring application?
Thanks in advance !!!

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 25 Nov 2014 07:26:33 GMT

Hi,

Can someone help me out with detailed steps of how to read an excel file and store it into Oracle database using Talend ETL version 5.6?

From your description, you can use tFileInputExcel-->tOracleOutput in Talend 5.6 studio to achieve your goal.

Further can I write some rules of validation to check if the data is valid then store in database, else notify through e-mail to the intended users. Also, how do get the source code of all these activities and then integrate the same in a Spring application?

What kind of rules of validation do you want to use? Could you please set an example for us?

Talend is an RCP based on Eclipse, Talend offers open source skills and solutions. If you want to develop an app similar to Talend, launch Talend application from Talend source code is the best way to learn Eclipse skill and Talend. You can download source code from here:
http://talendforge.org/svn/tos/trunk
http://www.talendforge.org/trac/tos/

Best regards
Sabrina

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 25 Nov 2014 09:10:34 GMT

Hi,

Can someone help me out with detailed steps of how to read an excel file and store it into Oracle database using Talend ETL version 5.6?

From your description, you can use tFileInputExcel-->tOracleOutput in Talend 5.6 studio to achieve your goal.

Further can I write some rules of validation to check if the data is valid then store in database, else notify through e-mail to the intended users. Also, how do get the source code of all these activities and then integrate the same in a Spring application?

What kind of rules of validation do you want to use? Could you please set an example for us?

Talend is an RCP based on Eclipse, Talend offers open source skills and solutions. If you want to develop an app similar to Talend, launch Talend application from Talend source code is the best way to learn Eclipse skill and Talend. You can download source code from here:

Best regards
Sabrina

Hi Sabrina,
Thank you so much for the help. I could do a sample program to simply read an Excel file and insert the values into Oracle database. However, I need to know how can I validate the data in Excel before I push it to database. For example, if there are three columns ID, Name and Address in the Excel file. Before I push this in database I want to replace all the special characters (like $, @) with a space that appear in the name field and then push it to database. Optionally if I find "#" in name an e-mail should be triggered to an intended user.
Thanks in advance !!!
Regards,
Omkar

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 25 Nov 2014 10:01:40 GMT

Hi,
If I understand your requirement well, you can design your job as:
tFileInputExcel-->tReplace--> tOracleOutput
|
RunIf
|
tsendmail
Please see my demo job design screenshots as below.

Best regards
Sabrina

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 25 Nov 2014 10:32:35 GMT

Hi,
If I understand your requirement well, you can design your job as:
tFileInputExcel-->tReplace-->tOracleOutput
|
RunIf
|
tsendmail
Please see my demo job design screenshots as below.

Best regards
Sabrina

Yes, Thanks Sabrina. This is exactly what I wanted. Sorry to bother you, but can you also explain how this can be implemented using 'tmap'. In tmap I would like to do more complex processing. To start with I would convert all the characters in Excel to uppercase and push to database. I tried using 'tmap' but it give me an exception of ";" token missing.

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 25 Nov 2014 10:50:03 GMT

Hi,
You can use ternary if-else operator in tMap.
See my screenshot
Best regards
Sabrina

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 02 Dec 2014 13:45:29 GMT

Hi,
You can ternary if-else operator in tMap.
See my screenshot
Best regards
Sabrina

Ok. Great. Thanks Sabrina for the help.
Regards,
Omkar.

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Wed, 03 Dec 2014 09:15:05 GMT

Hi Omkar,
If this issue is fixed, could you please mark this topic as resolved?
Feel free to let us know if there is any further help we can give.
Best regards
Sabrina

Re: [resolved] Loading Excel Data into Oracle Database

Anonymous — Tue, 09 Dec 2014 10:30:50 GMT

Hi Omkar,
If this issue is fixed, could you please mark this topic as resolved?
Feel free to let us know if there is any further help we can give.
Best regards
Sabrina

Hi Sabrina,
Apologies for the late reply. Yes, this issue is fixed.
Thank you so much for your help
Regards,
Omkar