[resolved] Loading Excel Data into Oracle Database

One Star

[resolved] Loading Excel Data into Oracle Database

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 !!!

Accepted Solutions
Moderator

Re: [resolved] Loading Excel Data into Oracle Database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

All Replies
Moderator

Re: [resolved] Loading Excel Data into Oracle Database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Loading Excel Data into Oracle Database

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
Moderator

Re: [resolved] Loading Excel Data into Oracle Database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Loading Excel Data into Oracle Database

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.
Moderator

Re: [resolved] Loading Excel Data into Oracle Database

Hi,
You can use ternary if-else operator in tMap.
See my screenshot
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Loading Excel Data into Oracle Database

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

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

Re: [resolved] Loading Excel Data into Oracle Database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Loading Excel Data into Oracle Database

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 Smiley Happy
Regards,
   Omkar