Data issue in excel output

[Anonymous]

See the screenshot

The excel cell with only number's in it are formatted as text. Due to this the formula's in excel is not working. Whereas the data with Alphanumeric value is showing properly in excel.

I am pulling data from database and storing it in excel which has some formulas. Earlier the input schema was having the DB Type as VARCHAR, after this issue i changed it to NVARCHAR, still the excel cell in the output is storing as text. No issues with graph and transmission, everything went fine without an error. Only issue is cell formatting in excel. Please suggest.

[Anonymous]

Maybe I am misunderstanding, but if you want your Excel cells formatted as numbers, your Excel schema should have those columns set to a number not a VARCHAR or NVARCHAR.

If your data is coming from a database table where those values are stored as VARCHAR, then you need to convert them to numbers before sending them to the Excel file. However, before you can convert them, you need to either be sure that they are numbers stored as text or provide a mechanism (possibly using a tMap) to filter out values which cannot be converted.

[Anonymous]

Maybe I am misunderstanding, but if you want your Excel cells formatted as numbers, your Excel schema should have those columns set to a number not a VARCHAR or NVARCHAR. [Me] excel schema is not having an option to set as number, but "String", "Date", "boolean" etc.

If your data is coming from a database table where those values are stored as VARCHAR, then you need to convert them to numbers before sending them to the Excel file. However, before you can convert them, you need to either be sure that they are numbers stored as text or provide a mechanism (possibly using a tMap) to filter out values which cannot be converted. [Me] This field sometimes sends alpha also, so i cant convert them. i want all the data, cant filter out. See my earlier pic.

I will elaborate, Input field from database is nvarchar(50), take patient_ID field which i displayed in attached image. this patient is used in excel formula. Talend stores some cells as text, some as numbers. The data in text cells are not being captured for the excel manipulation which is causing my issues. I have given the output type in talend as String(50). Please suggest a solution. 

Graph3.jpg

[Anonymous]

excel schema is not having an option to set as number, but "String", "Date", "boolean" etc.

This field sometimes sends alpha also, so i cant convert them. i want all the data, cant filter out. See my earlier pic.

I will elaborate, Input field from database is nvarchar(50), take patient_ID field which i displayed in attached image. this patient is used in excel formula. Talend stores some cells as text, some as numbers. The data in text cells are not being captured for the excel manipulation which is causing my issues. I have given the output type in talend as String(50). Please suggest a solution. 

Graph3.jpg

[Anonymous]

You can't do that I'm afraid. You *might* be able to do it with some convoluted logic where you load a row of strings to the file, select "append" on another Excel component and then load numbers to the same column. I've not tried that. The problem you have is that columns can only hold 1 type in Talend. This is pretty standard with Data Integration tools. If your data is sent to Excel as a String, then Excel will treat it as a String. 

You either need to try out the convoluted suggestion I mentioned (but I think that is silly to be honest) or change the format of your Excel output so that your numeric columns are ALL numeric (or at least numeric in blocks).

Either that, or edit the Excel file by hand afterwards.

[Anonymous]

When i revisited the data again, i have found that the cells with only numeric value is appearing as text. Alphanumeric values are not having any issue. Will it work if the output schema has changed to integer? Though we need to figure out what can be done with alphanumeric fields.  

[Anonymous]

This is not down to what the cell holds. "1234532"may look like a number, but its data type is text. There is a big difference between what a human reads and understands and what a computer can interpret. A computer need to be told what type to expect and then the values MUST be of that type. This is why String is used a lot. It cover practically everything.

[Anonymous]

Now that the values storing in text is not accepting inside the formula written in the excel. i think we should bring the excel formula inside talend. Do you agree? Any other suggestions?

Also, if i select "Keep existing cell format" option in tFileoutputexcel properties then the graph processing time exceeds 30min and it gets hanged. Maybe i will get desired results if i select this option. What say? Like i said earlier, (FYI) the output excel has some formulas.

[Anonymous]

There is an Exchange component which might help you write the cell formula which would probably be a better solution to this. Take a look here: http://jan-lolling.de/talend/components/help/tFileExcelSheetOutput.pdf

This component was written by @lli who has written a number of very useful components for Talend.

[Anonymous]

Thanks rHall... i will try that.