You can translate the question and the replies:

Excel text field is defined as double in base view

I am using Denodo 8.0. I have an Excel spreadsheet data source which contains multiple worksheets. I have created the data source for each worksheet. All of the data columns are defined as General or Text on the Excel spreadsheet. But when I create the base view for the Excel worksheet a few of the columns that tend to contain numeric (whole number) data are defined as a field type of double. And when I execute the base view, a decimal and two decimal places are add to the values in those cells. The same field on other worksheets is defined as text field type on the associated based view and the number displays correctly. When I try to modify the field type from DOUBLE to TEXT (with a type of VARCHAR) and execute the base view - the numbers are still not displaying correctly. What would cause this to occur?
Excel
user
15-04-2022 17:24:05 -0400

5 Answers

Hi, In general, when you add data with the 'Number' data type to the Excel data source, it will appear as decimal values. For example, if you insert a value - 1234 in field type Number, then it will look like - 1234.00. After that, when you create an Excel data source with the **Number** field type in Denodo Platform then it will be changed to a **'double'** data type. When you change the data type to text in Virtual DataPort, the data will not be changed as a whole integer and it will appear as the same value which is present in the underlying Excel source. For your use case, you would use the **CAST** function in Virtual DataPort in order to get a whole number for a particular column. For more information about the CAST function, you could refer to the [CAST](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/conversion_functions/conversion_functions#cast) section of the Virtual DataPort VQL Guide. Hope this helps!!
Denodo Team
18-04-2022 08:57:25 -0400
Hi Denodo team - In my case, the Excel data source field contains number values but it is defined as TEXT (not as NUMBER) but Denodo is converting it to DOUBLE data type. Do you know why it would be doing that?
user
18-04-2022 09:58:01 -0400
Hi, I have also come across a similar behavior while creating a base view for an excel file that contains numeric values in the Text data type. In order to read the data in the Text type, I used the [Cast](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/conversion_functions/conversion_functions#cast) function, and I was able to get the excepted result. In case, if you still need further assistance for this use case and you have an active support account, I would recommend opening a support ticket to further explore the issue. Hope this helps!
Denodo Team
21-04-2022 09:43:34 -0400
I checked the Stream Tuples checkbox on the data source connection screen and that resolved the issue that I was seeing. The Excel text fields that contained a number were defined as **Integer** instead of **double** once Stream Tuples was checked. All of the data is displaying correctly now.
user
21-04-2022 15:31:40 -0400
Hi, I am glad that you were able to resolve the issue!
Denodo Team
22-04-2022 09:20:44 -0400
You must sign in to add an answer. If you do not have an account, you can register here