topic Re: Extract data from specific cell from excel and store in DB table column in Talend Studio

Extract data from specific cell from excel and store in DB table column

Vijay_Kumar — Sat, 16 Nov 2024 01:44:51 GMT

Hello Experts,

I have an excel with the format as mentioned in the screenshot. I have requirement to store "Event ID" and "No.of registrants" data in MYSQL DB table. But the problem is, these value are in a specific cell in the excel (A5). I need to create a new column in my output schema and store these values against all other column values present in the excel.

I am very new to Talend, not really sure how to do it. Could you please help me to design this job in Talend.

Thank you and appreciate all your help in advance.

Input Excel format:

Expected Output Schema:

Re: Extract data from specific cell from excel and store in DB table column

Anonymous — Tue, 04 Aug 2020 07:32:54 GMT

Use a tFileInputExcel to read the specific cell, define only one column on schema, set the Header field as 4, the limit field as 1, see

Read this specific cell value as a string, and link tFileInputExcel to a tJavaRow, extract the event ID and no. Of Registrants using Java code, store these values to context variables for used later.

In next subjob, use another tFileInputExcel to extract value from column First Name, Last Name, Company, etc, link tFileInputExcel to a tMap, add new columns, and set their values using context variables which was assigned value in previous subjob.

The job design loos like:

tFileInputExcel1--main--tJavaRow

|onsubjobok

tFileInputExcel2--main--tMap-->tMysqlOutput

Please try and let me know if you have any questions.

Regards

Shong

Re: Extract data from specific cell from excel and store in DB table column

Vijay_Kumar — Tue, 04 Aug 2020 07:48:27 GMT

Thank you and appreciate your reply Shong 🙂

Could you please send me the code that I need to enter into tJavaRow to extract specific text ?

If you can send me the screenshot of the each job component would be very helpful.

Thank you So much again !

Re: Extract data from specific cell from excel and store in DB table column

Anonymous — Tue, 04 Aug 2020 10:25:09 GMT

Here is an example Java code on tJavaRow:

context.event_id = input_row.value.substring(input_row.value.indexOf(":")+1,input_row.value.indexOf("No. of Registration"));

context.Registration = input_row.value.substring(input_row.value.lastIndexOf(":")+1);

context.event_id=context.event_id.trim();

context.Registration=context.Registration.trim();

//System.out.println(context.event_id);

//System.out.println(context.Registration);

I did't create a complete job, just guide you the way that you can follow and make some testing, I believe the process should work.

Regards

Shong

Re: Extract data from specific cell from excel and store in DB table column

Vijay_Kumar — Tue, 04 Aug 2020 10:58:43 GMT

Thank you again Shong 🙂

I may sound dumb as I am new to Talend. But happy to learn.

I tried your code but I keep getting the error message. Could you please check and let me know what am I doing wrong here ?

Please let me know if you need more detail.

Thank you,

Re: Extract data from specific cell from excel and store in DB table column

Anonymous — Tue, 04 Aug 2020 12:48:46 GMT

what is the error message? Have you defined the context variables in Context view?

Re: Extract data from specific cell from excel and store in DB table column

Vijay_Kumar — Wed, 05 Aug 2020 07:45:02 GMT

Dear Shong,

Please find the attached screenshots of the job component and error message.

Thank you,

Vijay

Re: Extract data from specific cell from excel and store in DB table column

Vijay_Kumar — Wed, 05 Aug 2020 13:57:12 GMT

Please ignore my reply.

I have successfully created the job based on your suggestion.

Thank you so much and appreciate your help 🙂

Thank you!

Vijay