Question

Rename Worksheet name of an excel

  • 24 November 2022
  • 4 replies
  • 54 views

Can we rename a worksheet of an excel using StreamSets?


4 replies

Userlevel 4
Badge

Hi @Subha  - Thank you for reaching out to StreamSets Community. We do not have a FileRename stage in a data collector that can help you rename the files.​​​​​​

Could you please confirm how may files would like to rename and what would be the destination file name expression? Also confirm those file are available on local file system or object storage S3?

 

Thank you - AkshayJ

Hi @AkshayJadhav , 

Thanks for your response

I have a requirement to rename the worksheet inside an excel sheet . I am able to rename the Excel sheet as a whole using the shell stage. But this is also renaming the Sheet inside the excel . I want to rename the sheet inside the excel to a different name. I am not able to find any stage or options to do the same . 

Also this excel file is available in the local file system. 

Example : 

My destination excel file name should be “Sample_Nov22.xls”.

Whereas the Sheet inside should named as “ReferenceData”.

Currently i am using  a shell script stage after the local FS stage as a event and I am able to rename the excel as “Sample_Nov22.xls”. But the Sheet inside the excel is also getting renamed as “Sample_Nov22”.Whereas I want it to be named as “ReferenceData”. 

Any help is appreciated. Thanks in advance. 

 

Userlevel 4
Badge

Hello @Subha  - Thank you for sharing the details. I’m afraid that we do not have any stage to rename the worksheet inside an excel sheet as per your requirement.

As per my understanding and past experiences, this can be done with the help of the shell scripting.

 

Thank you - Akshay Jadhav

Userlevel 5
Badge +1

 

 

@Subha 

This can be done using script processors in SDC . Like given below. As I don’t have openpyxl library , couldn't  provide the actual code which can help you on your issues.

 

Can you please try with below code snippet and check if it works.

import openpyxl
ss=openpyxl.load_workbook("file.xlsx")
#printing the sheet names
ss_sheet = ss['Sheet']
ss_sheet.title = 'Fruit'
ss.save("file.xlsx")

Reply