how to extract a value of a cell in the excel with tfileinputexcel

One Star

how to extract a value of a cell in the excel with tfileinputexcel

i want to extract a value in a cell, i dont want to have all the column.
Could u help me please?
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

If I understand your question, you have a Cell, C4 for example, and you want to get this value.
I suggest you to create a schema with the cell name please verify that the schema of your cell has the right format.
A quite non elegant solution is to put into the tFileInputExcel value Header to 3 (for C4) and First column to 3 (for C)
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

thank u for ur help
but in the same file i want to get the value of 3 cells(B9,K24 and J7)
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

how can i create a schema with a cell?
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

you can use this method to get in my exemple the value of A3,C9 and B36
Employee

Re: how to extract a value of a cell in the excel with tfileinputexcel

I advise you to read 970
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

i have followed u but it did not work.
i have only the out3 which contain K22 between tMap and tMysqlOutput.
i obtain this msg
Warning: Usage of a local non-builtin name
Unknown column 'K22' in 'field list'
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

these are the images of my work
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

,
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

the value of B9 is a string in the excel file
K22 is an integer
J7 is a date
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

Because you have to join the 3 tmap output to 3 dbOutput (which coulb be the same table)
Highlighted
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

thank u.
Now i have an other type of problem.
when i run i got this message
Starting job test at 11:54 27/03/2008.
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Warning: Usage of a local non-builtin name
Field 'ID_AUDIT' doesn't have a default valueField 'NON_CONFORMITE' doesn't have a default valueField 'ID_AUDIT' doesn't have a default valueField 'ID_AUDIT' doesn't have a default value
Job test ended at 11:54 27/03/2008.
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

i solve that problem but i still have other exceptions.this is what i got
Column 'DATE_AUDIT' cannot be nullColumn 'ID_AUDIT' cannot be nullColumn 'ACTIVITY_NAME' cannot be nullException in component tMap_1
java.lang.NullPointerException
at test.test.test.tFileInputExcel_1Process(test.java:667)
at test.test.test.runJobInTOS(test.java:1156)
at test.test.test.main(test.java:1075)
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

I think that you've checked the key checkbox or you've unchecked the null one
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

no.i didn't do that
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

this is what i did
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

the problem comes from your tOuputDB component, In your database you have configured your columns DATE_AUDIT,ID_AUDIT,ACTIVITY_NAME as not null, so you have to set in TOS a default value
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

yes these column are not nul in my database.where should i set in TOS a default value?i already do this and it did not work
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

have u any idea please?i need ur help
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

ID_AUDIT is a primary key so where can i sign a default value in TOS?can anybody help me?
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

Maybe to solve your problem we have to restart completely the analyse of your job, please answer to the following questions:
how many column have you xls file?
Did you have a look to plegall links?
Where do you want to put the value of your 3 cells?Do you want to put them in the same DB rowor in 3 different rows? What is your target fields name?
I'll do your job completely tomorrow
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

thank u very much.
My purpose is to extract data from cells(B9 contain the name of activity,B8 contain the name of the project,K22 contain le nombre de non conformite and J7 contains the date) from different files excel that have the same format.then i want to put these all data in a table "audit" that has this syntax:
CREATE TABLE `staging_area`.`AUDIT` (
`ID_AUDIT` VARCHAR(30) NOT NULL default '',
`NON_CONFORMITE` NUMERIC NOT NULL default '0',
`ACTIVITY_NAME` VARCHAR(30) NOT NULL default '',
`DATE_AUDIT` DATE NOT NULL DEFAULT 0,
PRIMARY KEY(`ID_AUDIT`)
);
ID_AUDIT will have the value of B8,NON_CONFORMITE will contain the value of K22,ACTIVITY_NAME will contain the value of B9,DATE_AUDIT will contain the value of J7 as if i did an insert in the table audit.then i will use this table as a dimension and other tables to generate report.
the columns of the file are(A,B,C,E,F,G,H,I,J,K)
The data extracted from the same file will be in the same row :example("XX",5,"YY","12/10/2007"),(XX,5,YY and "12/10/2007") are in the same file.
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

Ok
So I did your Job, I don't know if my method is the best one but it works well.
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

before i use ur method i want to say u thank u very much for ur help!
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

It did not work Smiley Sad
i have this error message
Warning: Usage of a local non-builtin name
NumberFormatException For input string: "23/12/07"
NumberFormatException For input string: "Duration in h: "
The cell format is not Date in row 11 column 9
The cell format is not Date in row 12 column 9
The cell format is not Date in row 13 column 9
The cell format is not Date in row 14 column 9
The cell format is not Date in row 15 column 9
NumberFormatException For input string: "PR.MA.01"
The cell format is not Date in row 17 column 9
The cell format is not Date in row 18 column 9
The cell format is not Date in row 22 column 9
The cell format is not Date in row 35 column 9
Exception in component tAggregateRow_1_AGGOUT
java.lang.NumberFormatException: NumberFormatException For input string: "null"
at routines.system.ParserUtils.parseTo_float(ParserUtils.java:68)
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

Ok I know where your problem comes from, put your colomn J format to String in your tfileExcel and change the code of the tjavarow to :
int seq= Numeric.sequence("topic970",1,1);
if (seq == 7) {
output_row.J7 = TalendDate.parseDate("dd-MM-yyyy",input_row.J);
}
if (seq == 8) {
output_row.B8 = input_row.B;
}
if (seq == 9) {
output_row.B9 = input_row.B;
}
if (seq == 22) {
output_row.K22 = input_row.K;
}
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

the problem is that each column can contain many types of data.So i can not specify for example for the column J the type DATE
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

but if i put J format to string and so J7 i have this msg error
"Type mismatch: cannot convert from Date to String"
because DATE_AUDIT is a DATE type
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

you have to let the J7 format in the output schema to Date, you just have to change the input schema and the tfileExcelinput schema J to String
If you use the new code of tjavarow you will convert the string to a date
One Star

Re: how to extract a value of a cell in the excel with tfileinputexcel

this exactly what i did i put J7 format to date but i get this msg
NumberFormatException For input string: "23/12/07"
NumberFormatException For input string: "Duration in h: "
Exception in component tJavaRow_1
java.lang.NullPointerException
at java.text.SimpleDateFormat.parse(Unknown Source)
at java.text.DateFormat.parse(Unknown Source)
23/12/07 is in J7
Duration in h: is in J8