[resolved] Loading Excel Data into Oracle Database

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

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

[Anonymous]

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

[Anonymous]

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