Question

Save records as Excel file in local directory

  • 16 February 2023
  • 5 replies
  • 94 views

How to save records pulled from JDBC consumer as excel file using local FS destination processor.


5 replies

Userlevel 5
Badge +1

@Santosh 

 

After reading data from JDBC consumer , by using jython evaluator , we can create an excel file and send it to AWS bucket .

In the second pipeline , you can fetch the file as whole file and send to local FS as whole file.

If there are issue while performing the above steps then  I can give a try with some dummy data and provide you the pipeline for it.

 

Thanks & Regards

Bikram_

Hi @Bikram

Can you please let me know if there is a way to directly write to local storage using jython in excel format?

Userlevel 5
Badge +1

@lex03 

 

In that case we need to write the script to store excel files in local storage but i am not sure if your jython version supports xl libraries or not.

 

I tried but its giving error for openpyxl library , i need to deploy the library in my sdc.

 

Please give a try.

Sample code 

 

import openpyxl
from openpyxl import Workbook

# Create a new Excel workbook
workbook = Workbook()

# Create a new worksheet
sheet = workbook.active

# Example data to write to Excel
data = [
['Name', 'Age', 'Country'],
['John', 30, 'USA'],
['Alice', 25, 'Canada']
]

# Write data to the Excel sheet
for row in data:
sheet.append(row)

# Save the workbook to a local file
excel_file_path = 'output.xlsx'
workbook.save(excel_file_path)

 

@Bikram 

I just checked, but I don’t have openpyxl library installed.

Is there any other way this can be done?

 

@Bikram 

I tried the code that you provided in a streamsets pipeline.

try:

  sdc.importLock()

  import sys

  sys.path.append('/home/sdc/.local/lib/python3.8/site-packages')

  import openpyxl

  from openpyxl import Workbook

finally:

  sdc.importUnlock()

wb = Workbook()

# grab the active worksheet

ws = wb.active

# Rows can also be appended

ws.append([1, 2, 3])

wb.save("sample.xlsx")


This is the error I get: 

Script error while processing batch: javax.script.ScriptException: SyntaxError: no viable alternative at input '"Row numbers must be between 1 and 1048576. Row number supplied was {row}"' in /home/sdc/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py at line number 258 at column number 30

 

But when I run the same code locally on my system it works fine.

Do you know what might be causing this?

Reply