Using Excel Sheet name

Denes

Hi,
I have an Excel with different sheets and I need the name of the sheet as a value in DSS.

Is one of the visual recipes or the Data-Upload settings able to do this?

Thx

StanG

Hi,
To do what you want without coding, you can upload the excel file in a DSS folder and use the excel sheet importer plugin (https://www.dataiku.com/product/plugins/excel-sheet-importer/) to create one dataset per excel sheet. Then you can stack these datasets and retrieve the sheet name in a column using the Origin column parameter of the visual stack recipe.
Or this can be done in only one step with a python recipe using the answer from the post.

VitaliyD

Hello,

It is not clear what you mean by saying, "I need the name of the sheet as a value in DSS", please provide your use case for a better understanding of the requirement.

If you want to import Sheets into different datasets, then you can use the plugin suggested by StanG in this thread.

If you want to store sheet names as values in a dataset or as project variables, then there no visual recipe that can do it in DSS.

Also, there is always an option to use a custom Python recipe. Using the example below, you can get all spreadsheets from a folder, get sheet names, and then, depending on your requirements, do whatever you need with the names (add them to project variables, create a dataset, etc.).

import glob
import openpyxl

path = '<absolute_path_to_files>*.xlsx'
files = glob.glob(path)

for file in files:
    wb = openpyxl.load_workbook(file)
    print(wb.sheetnames)

I hope this help.
Regards,
Vitaliy

Denes

Hi, thanks for the replies and sorry for the imprecise question.

I have an Excel file named 'city.xlsx' with the sheets 'Austin', 'Bangkok', 'Chicago',....
When I process the sheet 'Bangkok', I need the name of the city in the variable 'City_Name' with delivered. What I do not need is 'city.xlsx' as the value of my variable each time.

I guess I have to work with a custom Python recipe.