4

I am trying to write my dataframe to gcs bucket from cloud function, I am able to write csv but i am not able write excelfile from cloud function. Here is my code:

requirements.txt

# Function dependencies, for example:
# package>=version
google-cloud-storage==1.30.0
gcsfs==0.6.2
pandas==1.1.0
pandas-gbq==0.15.0
openpyxl==3.0.5
XlsxWriter==1.3.7

Main.py

import xlsxwriter
import openpyxl
import numpy as np
import pandas as pd
from pandas.io import gbq
from google.cloud import storage

# Defining Output Folder path.
output_folder = "gs://samples/results/output/"

def testing(request):
  # initialize list of lists
  data = [['tom', 10], ['nick', 15], ['juli', 14]]
  
  # Create the pandas DataFrame
  df = pd.DataFrame(data, columns = ['Name', 'Age'])
  df.to_csv(output_folder + "test_csv.csv", index=False) #able to write csv
  df.to_excel(output_folder + "test_excel.xlsx", index=False) #Error
  return None

Error: FileNotFoundError: [Errno 2] No such file or directory

Full Error:

Traceback (most recent call last): File "/env/local/lib/python3.7/site-packages/xlsxwriter/workbook.py", 
line 320, in close self._store_workbook() File "/env/local/lib/python3.7/site-packages/xlsxwriter/workbook.py",
 line 638, in _store_workbook raise e File "/env/local/lib/python3.7/site-packages/xlsxwriter/workbook.py", 
line 636, in _store_workbook allowZip64=self.allow_zip64) File "/opt/python3.7/lib/python3.7/zipfile.py", 
line 1240, in __init__ self.fp = io.open(file, filemode) 
FileNotFoundError: [Errno 2] No such file or directory: 'gs://samples/results/output/test_excel.xlsx' 
During handling of the above exception, another exception occurred: Traceback (most recent call last):
 File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py",
 line 402, in run_http_function result = _function_handler.invoke_user_function(flask.request) File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py", line 268, in invoke_user_function return call_user_function(request_or_event)
 File "/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py", line 261, in call_user_function return self._user_function(request_or_event) File "/user_code/main.py", line 24, in testing df.to_excel(output_folder + "test_excel.xlsx", index=False) File "/env/local/lib/python3.7/site-packages/pandas/core/generic.py", line 2029, in to_excel engine=engine, 
File "/env/local/lib/python3.7/site-packages/pandas/io/formats/excel.py", 
line 742, in write writer.save() File "/env/local/lib/python3.7/site-packages/pandas/io/excel/_xlsxwriter.py",
 line 193, in save return self.book.close() File "/env/local/lib/python3.7/site-packages/xlsxwriter/workbook.py", 
line 322, in close raise FileCreateError(e) xlsxwriter.exceptions.
FileCreateError: [Errno 2] No such file or directory: 'gs://samples/results/output/test_excel.xlsx'
James Lin
  • 153
  • 2
  • 10
  • 1
    Does it work if you write in the output_folder `/tmp/`? If so, I assume that the GCS file output isn't implemented for the to_excel method and you need 2 steps: Write in `/tmp/` then upload manually to GCS. – guillaume blaquiere Aug 31 '21 at 10:09
  • Could you add the full trace so we can see what part of the code raises the exception, and for which file. – jmcnamara Aug 31 '21 at 11:40
  • @jmcnamara df.to_excel(output_folder + "test_excel.xlsx", index=False) #Error the error is coming in this line because i am able to write csv but i am not able to write excel. – James Lin Aug 31 '21 at 14:52
  • It would still help to see the full trace. – jmcnamara Aug 31 '21 at 15:08
  • @jmcnamara i have updated the question with the full trace back, please look into it. – James Lin Sep 01 '21 at 07:46
  • I agree with @guillaumeblaquiere that you wouldn't be able to use `to_excel()` to write directly to GCS. You can skip the tempfile step and go via memory though - see e.g. https://stackoverflow.com/a/56976741/1021819 – jtlz2 Sep 01 '21 at 07:49

0 Answers0