Copying Data from Multiple XLS Files to a Cumulative Master File

[Martijn]

I'm facing a challenge with the following: I have weekly files of sports results that I want to consolidate into a master file each week. The source is an Excel template (XLS format) that I download for each match. The source format has fixed tabs and each spreadsheet has a fixed format.

My goal is to place the new templates in a fixed folder, and have certain source data copied to my master file. Each time, the data should be copied into an empty cell below the previous match data.

What I want seems fairly basic, but the strange thing is that the Python code I wrote (with the help of Copilot) somehow cannot copy the first cells. For example, when I try to get the data from cell B1, Python strangely gets the info from B3. In total, I want to copy 4 fields, but everything shifts by one cell. I want to extract the following from the source file: Cell B1, B3, B4, and B6 These are vertical in the source file but I want to place them horizontally in my master file so that I can, for example, create an Excel Pivot table. However, it doesn't work to get the data from the first cell, B1, into my master file. Note, the info is vertical in the source file wed_cumula, but I want it horizontally in my master file.

Another challenge is that it doesn't work to read an XLS file; only when I convert the source file to an XLSX file can the Python script read and copy the file, but then it doesn't get the first cell B1. However, opening and saving the files as XLSX instead of XLS each time is not an option because I want to download many files in bulk and convert them to the master file.

I have put the Python script in a WordPad document attached. The source files are named wed_cumula (always XLS files when I download them) and I want to get the cells in the following order in my master data sheet horizontally: B2, B4, B1, B3, B6 And my master file is named BNXT_202425. I have included an example of how it should be and the cells that refer to the correct cell from the source file. In red, how I get it now, without cell B1, but also based on the source file as XLSX instead of XLS.

So the errors I notice are as follows: My script cannot read the XLS file, only XLSX and I miss the first cell, namely B1.

Who can help me get the correct script?

Thanks, Martijn

[Pedroski55]

Using xlrd you can read the xls files, then use openpyxl to save as xlsx files.

import xlrd
import openpyxl

import xlrd
from openpyxl import Workbook

path2xl = '/home/pedro/Python_Virtual_Environments/openpyxl/xl_files/'
xls_file = 'wed_cumula_2.xls'
xlsx_file = 'wed_cumula_2.xlsx'

# Reading .xls file using xlrd
wb_xls = xlrd.open_workbook(path2xl + xls_file)
# Get the number of sheets
#num_sheets = wb.nsheets # returns 6 for this file
# Get the sheet names, more convenient
sheet_names = wb_xls.sheet_names()

# create an xlsx file in openpyxl
wb_xlsx = Workbook()

# make sheets in the xlsx file the same as the xls
for sheet in sheet_names:
    wb_xlsx.create_sheet(sheet)

# when wb_xlsx is created it has 1 sheet called Sheet.
# get rid of Sheet
wb_xlsx.remove(wb_xlsx['Sheet'])

# save the new xlsx file
wb_xlsx.save(path2xl + xlsx_file)

Now you have an xlsx file with the same sheet names as your xls file. What do you want to do now? Copy everything? Or what?