How to write an excel file in a folder with python where folder use sharepoint plugin

PhilippeDulac

Hi,

Python script is not working when I want to write an excel file to a folder associated to sharepoint plugin.

Python fails to retrieve path needed to excelwriter.
path seems to not be part of folder_info structure of folder when using sharepoint plugin while it is on server's filesystem folder.

Here the code used :

# -*- coding: utf-8 -*-
import dataiku
import pandas as pd, numpy as np
from dataiku import pandasutils as pdu
import os

print ("FOLDER_INFO")
print (folder_info)
f_path = os.path.join(folder_info['path'] , file_name)
writer = pd.ExcelWriter(f_path,engine='xlsxwriter')

...

error is the following :

Job failed: Error in Python process: At line 24: <class 'KeyError'>: path

LOGS (using sharepoint plugin):

[2021/09/27-10:50:53.849] [null-out-64] [INFO] [dku.utils]  - FOLDER_INFO

[2021/09/27-10:50:53.850] [null-out-64] [INFO] [dku.utils]  - {'projectKey': 'LPC_PROD_OUT_REF_PREV', 'directoryBasedPartitioning': False, 'name': 'OUTPUT_FOLDER', 'id': 'yrztMCMF', 'accessInfo': {}, 'type': 'fsprovider_sharepoint-server_sharepoint-server_shared-documents'}

LOGS when it's working with server's filesystem folder:

[2021/09/23-15:29:38.739] [null-out-67] [INFO] [dku.utils]  - FOLDER_INFO
[2021/09/23-15:29:38.740] [null-out-67] [INFO] [dku.utils]  - {'path': '/data/dataiku/dss_design/managed_folders/LPC_PROD_OUT_REF_PREV/ivaCaknz', 'projectKey': 'LPC_PROD_OUT_REF_PREV', 'directoryBasedPartitioning': False, 'name': 'OUT', 'id': 'ivaCaknz', 'accessInfo': {'root': '/data/dataiku/dss_design/managed_folders/LPC_PROD_OUT_REF_PREV/ivaCaknz'}, 'type': 'Filesystem'}

Tell me if there's another way to export an excel file on a sharepoint folder ?

Thanks in advance for your help

Philippe

AlexB

Hi Philippe,

Assuming a simple flow with a python recipe taking a dataset as an input and a folder as output

you can use the following code: 

# -*- coding: utf-8 -*-
import dataiku
import pandas as pd
from io import BytesIO
import openpyxl

# Read recipe inputs
source = dataiku.Dataset("Source")
source_df = source.get_dataframe()

# First, save the data as excel format into a bytes string
stream = BytesIO()
excel_writer = pd.ExcelWriter(stream, engine='openpyxl')
source_df.to_excel(excel_writer, sheet_name='Sheet1')
excel_writer.save()

# Rewind to the begining of the bytes string
stream.seek(0)

# Write recipe outputs, with the Dataiku API in case the folder is remote
# (note: written this way, the recipe will also work if the target folder is local)
target_folder = dataiku.Folder("<<put your folder reference here>>")
with target_folder.get_writer("excelfile.xlsx") as writer:
    writer.write(stream.read())

Best regards,

Alex

AlexB

Hi Philippe,

When accessing the filesystem folder, you are using a local filesystem. This is why you can obtain the files path and interact directly with it.

However, in the case of the SharePoint folder, you are interacting with a remote folder. This is done via a DSS plugin, and because of that you need to use the DSS API to do so. You can find more information here.

Note that the DSS API allows you to interact with both type of filesystems.

Alex

PhilippeDulac

Thanks Alex for this answer.

Documentation links are confirming your message and it is clear to me we can not proceed remote folder the same way we proceed with local folder.

Then it doesn't seems easy to export an excel to a remote folder. It would be very usefull to have a sample ...

Best regards,

Philippe

PhilippeDulac

Hi Alex,

Sorry for the delay of my feedback : I was not able to adapt my script before.
Script needed many adaptations but your sample was very usefull.

At last it is working as expected.

Thanks a lot for your help,

Philippe

deanofmean

how would this work with multiple excel files to be written to a folder?