Loading data into DB from Excel with conditions

Five Stars

Loading data into DB from Excel with conditions

Hi All,

Please help me with this:

I need to design a job which has to load an excel sheet having unique batch_Id . 

when this excel file comes in with new data the job will first check if the batch ID is already in the table or not. IF not then it will load the data in the table and move the file to Archived folder. If the batch ID is already in table then the excel file will not be loaded. It will be placed in the Error folder.

 

Regards,

Skp22


Accepted Solutions
Five Stars

Re: Loading data into DB from Excel with conditions

Yes you can do all these things with Talend, but some are more difficult than others.



You have to ask yourself why some of these conditions are occurring. If the spreadsheet is being created from an automated process, why would (1) the worksheet be missing or (2) some of the columns be missing. Is there something you can do outside of Talend to remove those types of errors. You may need to ask the people providing the excel data to do a better job Smiley Happy



If a sheet is missing you will get an error, so you can handle with On Error for the Component or Subjob.



If a column is missing or out of place that causes your schema not to match, you can check with schema validation.



You can create a log table in a database, but you may have to write custom java with the tJava component to put into the file the exact information you want to capture. It all depends on what you need to log. You will have to investigate each Talend component to see what data values they provide you. Each component provides an error message which is very handy. Talend has some built in logging if you look in the documentation. There are three files they write to if you set your jobs up correctly; a Stats table, a Logs table and a Meter table. Find those in the documentation and use them if they fit your needs.



tMap has an expression editor to let you do almost anything you need to do to put data into an output field, so review all the code they provide in that editor.



Trimming can be done automatically with many components, like the tFileInputxxxx conponents. And the expression editor in tMap has code that will let you trim.



Coalesce can be done with Java equivalents like this syntax (expression true or false) ? do this if true : do this if false where you test if some field is null. You can nest these as well.



Hope this gives you some good ideas.



Thanks,

dg

All Replies
Five Stars

Re: Loading data into DB from Excel with conditions

Is the Batch ID inside the file in a field/column of the excel? Or is the Batch ID in the filename?

 

Also if the Batch ID is inside the data of the Excel, will there be only one Batch ID per file or possible more than one?

Five Stars

Re: Loading data into DB from Excel with conditions

It's inside the Excel sheet as a Field.
Five Stars

Re: Loading data into DB from Excel with conditions

Field Address

 

   |       A            |   B

1 | BATCH_ID  | XYZ

 

Five Stars

Re: Loading data into DB from Excel with conditions

This may give you some ideas. You could use tFileList to pick up the filename, input the file into tMap along with a lookup of the distinct BatchIDs of your database, filter your output to only the records that do not match the lookups (see second picture below), then move the file with tFileCopy to the archive if the insert record count was greater than zero or to the error folder if the record count equal to zero.map1.JPG

 

Map2.JPG

 

 

 

Five Stars

Re: Loading data into DB from Excel with conditions

This look great. Thank you so much . Is it possible to give the following conditions too? :
1) If the sheet doesn’t exist then the file would be moved to error folder.
2) If there is any column missing from the excel sheet to be loaded the file will be moved to error folder.
3) Create a log table and capture the logging information as well as the error information inside the job.
4) Put trim and coalesce conditions in String and numeric columns respectively.
Thanks
Five Stars

Re: Loading data into DB from Excel with conditions

Yes you can do all these things with Talend, but some are more difficult than others.



You have to ask yourself why some of these conditions are occurring. If the spreadsheet is being created from an automated process, why would (1) the worksheet be missing or (2) some of the columns be missing. Is there something you can do outside of Talend to remove those types of errors. You may need to ask the people providing the excel data to do a better job Smiley Happy



If a sheet is missing you will get an error, so you can handle with On Error for the Component or Subjob.



If a column is missing or out of place that causes your schema not to match, you can check with schema validation.



You can create a log table in a database, but you may have to write custom java with the tJava component to put into the file the exact information you want to capture. It all depends on what you need to log. You will have to investigate each Talend component to see what data values they provide you. Each component provides an error message which is very handy. Talend has some built in logging if you look in the documentation. There are three files they write to if you set your jobs up correctly; a Stats table, a Logs table and a Meter table. Find those in the documentation and use them if they fit your needs.



tMap has an expression editor to let you do almost anything you need to do to put data into an output field, so review all the code they provide in that editor.



Trimming can be done automatically with many components, like the tFileInputxxxx conponents. And the expression editor in tMap has code that will let you trim.



Coalesce can be done with Java equivalents like this syntax (expression true or false) ? do this if true : do this if false where you test if some field is null. You can nest these as well.



Hope this gives you some good ideas.



Thanks,

dg

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads