Skip to main content
Question

Rename Worksheet name of an excel

  • November 24, 2022
  • 4 replies
  • 58 views

Can we rename a worksheet of an excel using StreamSets?

4 replies

AkshayJadhav
StreamSets Employee
Forum|alt.badge.img
  • StreamSets Employee
  • 101 replies
  • November 24, 2022

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


  • Author
  • Fan
  • 3 replies
  • November 28, 2022

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. 

 


AkshayJadhav
StreamSets Employee
Forum|alt.badge.img
  • StreamSets Employee
  • 101 replies
  • November 29, 2022

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


Bikram
Headliner
Forum|alt.badge.img+1
  • Headliner
  • 486 replies
  • November 29, 2022

 

 

@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