How to save records pulled from JDBC consumer as excel file using local FS destination processor.
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
Can you please let me know if there is a way to directly write to local storage using jython in excel format?
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 = a
/'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)
I just checked, but I don’t have openpyxl library installed.
Is there any other way this can be done?
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(a1, 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.