I have a list of files and a workbook template which will be used to produce additional workbooks with a specific content.

I want to reference the value of the cell N2 of every file in the for loop, on the 14th column of the template file.

My code currently throws the error :

SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 5-6: truncated \UXXXXXXXX escape.

In the past I used the raw string format, but due to the f string formatting and the formula syntax, it throws the above error.

wb = load_workbook(os.path.join(r"C:\Users\pallist\AROTRON_OUT","template.xlsx"))
sheet = wb.worksheets[0]
files = glob.glob(".xlsx")

for f in files :
    for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=14, max_col=14):
        for cell in row :
            partner    = f"=r'C:\Users\pallist\AROTRON_IN\{f}Sheet1'!$N$2)"
            cell.value = partner

CodePudding user response：

I think it's the r' that's not being recognized inside the f-string that's causing the problem. Try the following code, see if it helps

vrb = pathlib.Path(r'C:\Users\pallist\AROTRON_IN')
for f in files:
    new_vrb = pathlib.Path.joinpath(vrb, f"{f}Sheet1!$N$2)")
    entry = "="   str(new_vrb)
    print(entry)

CodePudding user response：

I read through your question several times. Based on what I understand, below is what you are trying to do. Please correct if requirement is something else...

1. You have multiple input XLSX files in a folder C:\Users\pallist\AROTRON_IN\
2. You want to EACH xlsx file there and read the contents of Sheet1-Cell N2
3. You have another file C:\Users\pallist\AROTRON_OUT\template.xlsx - worksheet[0]. The N2 data read in above step (for each input file) should be added to this file in cell N2, N3, N4.... the number of rows that are updated will be equal to the number of rows in the input file.
4. Save the template file as a new file in C:\Users\pallist\AROTRON_OUT\ folder with name equal to the input file name.
5. Repeat steps 2 - 4 for each excel file in the AROTRON_IN folder

If this is the requirement, below code will do this for you.

## Your template file into which data will be written
wb = load_workbook(os.path.join("C:\Users\pallist\AROTRON_OUT", "template.xlsx"))
sheet = wb.worksheets[0]

## You input files from which data will be read
files = glob.glob(os.path.join("C:\Users\pallist\AROTRON_IN", "*.xlsx"))

i=2 ## Counter to increment as you write to the template file

for f in files :  ## For each input file
    fwb=load_workbook(f)  ## Open each input file
    fsheet=fwb['Sheet1']  ## Open Sheet1
    sheet.cell(row=i,column=14).value = fsheet.cell(row=2,column=14).value ## Copy
    i=i 1 ## Move to next row of template file

wb.save("C:\Users\pallist\AROTRON_OUT\template.xlsx")