You can translate the question and the replies:

Extract all option in Excel without sheet name is possible(Excel has multiple sheets)

Hii, I have a question related to Excel data dource wherein I am trying to create a scenario where I have multiple sheets in one single excel..say demo1.xsls. 1. When I tick (a) Extract Sheet name as column name (b) extract data from all sheets. Then here it works perfectly fine and data is displayed with all sheet numebr in front of there respective data. But when I remove/Untick "(a) Extract Sheet name as column name" option(b) is still ticket and try to achieve the result without sheetname I cannot do it it shows a blank screen. I just wanted to know, if that should in a way where it is okay if sheet name is not there but all data should be there as the data field is common in all pages. 2. When I Untick Stream tupples the data like Order ID and Id are automatically displayed in double feild type(it displays like 1.0, 10.0) and I further cannot change it with cast or by simple way to choose from options. Can you please answer these two questions .
user
17-09-2020 23:40:31 -0400

1 Answer

Hi, I was able to create a base view over the excel datasource and read the data from all the sheets without the sheet name in the output by unchecking the “Extract sheet name as a new column” option. During my testing, I have enabled “Extract data from all sheets”, “Has Headers” and “Stream Tuples” option. For your case, I would suggest you to drop the base view that was created when the “Extract sheet name as a new column” option is selected and recreate the base view after saving the data source changes(i.e unselect the “Extract sheet name as a new column” option). Reading the [Excel Sources](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/excel_sources/excel_sources) of the Virtual DataPort Administration Guide helped me understand the usage of Excel data sources. For your second question, if the “Stream Tuples” option is **cleared**, the Server obtains the type of the field from the **file’s metadata**. But if the “Stream Tuples” option is **selected**, then the Server infers the type of each column from the **first row of the file**. For more information, refer to the [Stream Tuples](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/excel_sources/excel_sources#:~:text=Stream%20tuples:) Section of the Excel Sources documentation. The scenario when the base view is returned the type as double, then I would create a derived view over the base view and convert this double field to int field using the **CAST** function. For e.g. `CAST(‘int’,<double field name>)` For more information, refer to the [CAST](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast) Section of the Type Conversion Functions. If you still need help and if you are a user with valid support access then you can raise a support case in [Denodo Support Site](https://support.denodo.com/) so that our support team can help you. Hope this helps!
Denodo Team
18-09-2020 07:54:04 -0400
You must sign in to add an answer. If you do not have an account, you can register here