2
\$\begingroup\$

I am currently writing a program to update and copy data from one spreadsheet to another. The code I have written works fine, but it takes way too long for it to be practical. In total, it takes about an hour to perform this task. The spreadsheets are also very large I might add, one is 20,000 rows by 30 columns, and the other is 3,000 rows by 30 columns. The code updates specific rows in the larger spreadsheet and then copies the data from the smaller spreadsheet onto the larger spreadsheet if that data doesn't already exist there. After analyzing what the problem could be, I found that copying and writing the data to the larger spreadsheet took a majority of the time (~ 55min). The write_only option in openpyxl does not support writing to existing files as I need, so I am stuck as to how to speed up this writing process.

# iterate through ticket column of first sheet
for roww in range (2, sheet.max_row+1):
    sheet1_ticket_number = sheet.cell(row=roww, column = 3).value
    # iterate through ticket column of second sheet
    # Ticket number x from sheet 1 compared to all ticket numbers in sheet 2
    for row2 in range(starting_row, (sheet2.max_row+1+sheet.max_row)):
        sheet2_ticket_number = sheet.cell(row = row2, column = 3).value

        # If ticket number matches, check to see if columns match, if not, update
        if (sheet.cell(row=roww, column = 3).value == sheet2.cell(row = row2, column = 3).value):
            check = 'true'
            for i in range(1, sheet.max_column+1):
                if sheet2.cell(row=row2, column = 3+i).value != sheet.cell(row=roww, column =3+i).value and (3+i != 15) and (3+i != 38) and (3+i != 14):
                    sheet2.cell(row=row2, column = 3+i).value = sheet.cell(row=roww, column =3+i).value
                    #print('updated row# ', row2, 'Column#', 3+i, 'ticket#', sheet2.cell(row=row2, column = 3).value,  'to:', sheet2.cell(row=row2, column = 3+i).value)

                if sheet2.cell(row=row2, column = 1).value is None:
                    sheet2.cell(row=row2, column = 1).value = sheet.cell(row=roww, column =1).value
                if sheet2.cell(row=row2, column = 2).value is None:
                    sheet2.cell(row=row2, column = 1).value = sheet.cell(row=roww, column =1).value
            break


        # if ticket number is not in second file/ empty row, add new ticket row w column entries. 
        if (sheet2.cell(row = row2, column = 3).value is None) and (sheet2.cell(row = row2+1, column = 3).value is None):
            sheet2.cell(row=row2, column =3).value = sheet1_ticket_number
            #print('printed new ticket row# ', sheet2.cell(row=row2, column =3).value)
            for j in range(1, sheet.max_column+1):
                if sheet2.cell(row=row2, column = 3+j).value != sheet.cell(row=roww, column =3+j).value:
                    sheet2.cell(row=row2, column = 2).value = sheet.cell(row=roww, column =2).value
                    sheet2.cell(row=row2, column = 1).value = sheet.cell(row=roww, column =1).value
                    sheet2.cell(row=row2, column = 3+j).value = sheet.cell(row=roww, column =3+j).value
            break   
\$\endgroup\$
  • 1
    \$\begingroup\$ It would help if you could post some more code. It is unclear how this snippet is used. Especially since the actual writing of the file (which you say is what is slow) is not actually included. \$\endgroup\$ – Graipher Jan 2 '18 at 13:22
  • 1
    \$\begingroup\$ some sample data might help too, with a minimal input and expected result \$\endgroup\$ – Maarten Fabré Jan 2 '18 at 16:41
  • \$\begingroup\$ and why did you label it pandas when I don't see pandas used anywhere. This looks like a job for pandas and a join or update operation \$\endgroup\$ – Maarten Fabré Jan 2 '18 at 16:43
0
\$\begingroup\$

Perhaps manipulating data in the spreadsheet is not the most efficient way to do this, could you save the spreadsheet as a csv, and then you could read both spreadsheets into a list structure using the python core csv module.

Alternatively, you might use the Pandas library to import the csv files to a dataframe, do your processing and resave to a csv output.

You can write your results out as csv, open this in excel and resave as an xlxs file.

Both of these approaches should take a few seconds to run

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.