unable to update the column to the excel file which is checkout from perforce

Question

i have a code where i was reading the Excel file from Perforce and storing it to the local.
then doing some other work like:
-- read all sheets
-- search for particular columns and extract that column data.
-- and from that data extract the other info from JIRA.
Till here its working fine so once we got all the data we will create a dataframe and then search for the column "STATUS" if there update the column with same data otherwise create a column in the same sheet and write the data to the column.
Code:

import os
import pandas as pd
from jira import JIRA
from pandas import ExcelWriter
from openpyxl import load_workbook


def getStatus(issueID):
    jiraURL='http://in-jira-test:0000' #Test server
    options = {'server': jiraURL}

    jira = JIRA(options, basic_auth=(userName, password))

    """Getting the status for the particular issueID"""
    issue = jira.issue(issueID)
    status = issue.fields.status
    return status

def getFileFromPerforce():
    """
        Getting the file from perforce
    """
    p4File = ' "//depot/Planning/Configurations.xlsx" '
    p4Localfile = "C:/depot/Planning/Configurations.xlsx"
    global p4runcmd
    p4runcmd = p4Cmd + " sync -f " + p4File
    stream = os.popen(p4runcmd)
    output = stream.read()
    print(output)

    return p4File, p4Localfile

def excelReader():

    # function call to get the filepath
    p4FileLocation, filePath = getFileFromPerforce()

    xls=pd.ExcelFile(filePath)

    # gets the all sheets names in a list
    sheetNameList = xls.sheet_names

    for sheets in sheetNameList:
        data=pd.read_excel(filePath,sheet_name=sheets)

        # Checking the Jira column availability in all sheets
        if any("Jira" in columnName for columnName in data.columns):
            Value = data['Jira']
            colValue=Value.to_frame()

            # Getting the status of particular jira issue and updating to the dataframe
            for row,rowlen in zip(colValue.iterrows(), range(len(colValue))):
                stringData=row[1].to_string()

                # getting the issueID from the jira issue url
                issueID = stringData.partition('/')[2].rsplit('/')[3]
                status = getStatus(issueID)

                # data.set_value(k, 'Status', status) #---> deprecated
                data.at[rowlen, "Status"]=status

            # writting the data to the same excel sheet
            print("filePath-",filePath)
            excelBook = load_workbook(filePath)
            with ExcelWriter(filePath, engine='openpyxl') as writer:
                # Save the file workbook as base
                writer.book = excelBook
                writer.sheets = dict((ws.title, ws) for ws in excelBook.worksheets)

                # Creating the new column Status and writing to the sheet which having jira column
                data.to_excel(writer, sheets, index=False)

                # Save the file
                writer.save()

        else:
            continue

if __name__ == '__main__':
    # read userName and passwrod from account file
    f = open("account.txt", "r")
    lines = f.readlines()
    userName = str(lines[0].rstrip())
    password = str(lines[1].rstrip())
    AdminUser = str(lines[2].rstrip())
    AdminPassword = str(lines[3].rstrip())
    p4Cmd = 'p4 -c snehil_tool -p indperforce:1444 -u %s -P %s '%(AdminUser,AdminPassword)
    f.close
    excelReader()

In this code i'm not able to write the data inside the file which i have checkout from perforce i was getting the error :

Traceback (most recent call last):
  File "C:/Users/snsingh/PycharmProjects/DemoProgram/JiraStatusUpdate/updateStatusInOpticalFile.py", line 105, in <module>
    excelReader()
  File "C:/Users/snsingh/PycharmProjects/DemoProgram/JiraStatusUpdate/updateStatusInOpticalFile.py", line 88, in excelReader
    writer.save()
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel\_base.py", line 779, in __exit__
    self.close()
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel\_base.py", line 783, in close
    return self.save()
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel\_openpyxl.py", line 44, in save
    return self.book.save(self.path)
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\writer\excel.py", line 291, in save_workbook
    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
  File "C:\Users\snsingh\AppData\Local\Programs\Python\Python37\lib\zipfile.py", line 1204, in __init__
    self.fp = io.open(file, filemode)
PermissionError: [Errno 13] Permission denied: 'C:/depot/Planning/Configurations.xlsx'

This piece of code is not working from the above code:

# writting the data to the same excel sheet
print("filePath-",filePath)
excelBook = load_workbook(filePath)
with ExcelWriter(filePath, engine='openpyxl') as writer:
# Save the file workbook as base
writer.book = excelBook
writer.sheets = dict((ws.title, ws) for ws in excelBook.worksheets)

# Creating the new column Status and writing to the sheet which having jira column
data.to_excel(writer, sheets, index=False)

# Save the file
writer.save()

NOTE:
This code works fine with local file contain the same data and its able to write perfectly. but its only happens when i readed the file from perforce.
Even i have given all the permission to the folder and tried with different folder path but i got the same error .Please tell me where i'm making mistake any help would be grate or any questions please fill free to write in comment.
thanks

score 0 · Answer 1 · answered Jun 03 '20 at 13:10

0

Three things:

When you get the file from Perforce, use p4 sync instead of p4 sync -f.
After you p4 sync the file, p4 edit it. That makes it writable so that you can edit it.
After you save your edits to the file, p4 submit it. That puts your changes in the depot.

answered Jun 03 '20 at 13:10

Samwise

68,105
3
30
44

thanks for reply but i have some doubt : if see my code i have stored that file in my local which is given as "p4Localfile " so why can we not directly use the file like that . not sure about p4 edit Is it allow me to the same operation like update or create new columns like same i'm doing in my code or i need to update that part of code – snehil singh Jun 03 '20 at 13:51
1

You're getting the "permission denied" error because the local file is not writable. The `p4 edit` makes the local file writable. What part of that do you have doubts about? :) – Samwise Jun 03 '20 at 14:27
plz can tell me diff. b/w p4 sync and p4 sync -f ?. Can u tell me where should i add p4 edit in my code not sure where to add that – snehil singh Jun 04 '20 at 05:11
Put the `p4 edit` any time after the `p4 sync` and before you attempt to write to the local file. If you don't know what a command flag does, don't use it! The default behavior is usually the correct one; most optional flags to p4 commands enable behavior that is more complex and/or less safe. – Samwise Jun 04 '20 at 13:31

unable to update the column to the excel file which is checkout from perforce

1 Answers1