0

I have a code, which is working properly if I manually insert strings for path, directory and file name, here is the code:

path = r"test//ab3b//ab3b_all_anal.xlsx"
directory = "test"

file1 = "test//ab3b//ab3b80.csv"
df1 = all_calc_80(file1, directory)

file2 = "test//ab3b//ab3b80m.csv"
df2 = all_calc_80m(file2, directory)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = '80')
df2.to_excel(writer, sheet_name = '80m')
writer.close()

Test directory has subdirectories named as ab3b, bg3a, ge3b etc. and in each of subdirectories there are files named in same way: ab3b80.csv, ab3b80m.csv; bg3a80.csv, bg3a80m.csv; ge3b80.csv, ge3b80m.csv.

Each of files based on ending 80.csv or 80m.csv use different function for analysing. The final output is one excel workbook with sheets names after ending of csv files.

Now I am working on iterating through whole directory test, where I just give the name of the directory and everything is proceed automatically from there. So far I have:

import os
import xlsxwriter
rootdir = 'test'

slovar = {}
for subdir, dirs, files in os.walk(rootdir):
        slovar[subdir] = files
    

for key, value in slovar.items():
    if len(key) > 4:               #to get just subdirectories I need
        end = key[-4:]
        path = 'r' + '\'' + key + '\\\\' + end + '_all_anal.xlsx' + '\''
        print(path)
        
    for vrednost in value:        
        if vrednost.endswith('80.csv'):
            file1 = vrednost
            df1 = all_calc_80(file1, rootdir)

        elif vrednost.endswith('80m.csv'):
            file2 = vrednost
            df2 = all_calc_80m(file2, rootdir)
            
    writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
    df1.to_excel(writer, sheet_name = '80')
    df2.to_excel(writer, sheet_name = '80m')
    writer.close()

But I got error message: Invalid extension for engine '<property object at 0x000002123659D0E0>': 'xlsx''.

I think there might be some problems due to /and \ in windows paths or types of object, even though when I print out just keys and values, I get usefull output, also name of the path is written properly.

But I don't really understand why manually everything works and automated not.

nezaslo4
  • 25
  • 5

2 Answers2

1

Invalid extension for engine '<property object at 0x000002123659D0E0>': 'xlsx''

I had the same exact problem and the solution was case sensitivity

when I changed the extension from XLSX to xlsx .. it worked fine

hope this will help someone

stackunderflow
  • 3,811
  • 5
  • 31
  • 43
0

If someone will still search for this answer, I had found a solution. Main discovery was regarding how to append path and file name to the list. It is done with os.path.join(dirpath, filename), if you use os.walk.

Here is the working code:

seznam80 = []
seznam80m = []
seznam120 = []
seznam120m = []
seznam150 = []
seznam150m = []
seznamSMT = []
dirp = []

for dirpath, dirnames, filenames in os.walk(directory): #directory with all folders of participants
        for filename in [f for f in filenames if f.endswith("80.csv")]: #search for all 80 files
            seznam80.append(os.path.join(dirpath, filename))
            dirp.append(dirpath)

for dirpath, dirnames, filenames in os.walk(directory): #directory with all folders of participants
        for filename in [f for f in filenames if f.endswith("80m.csv")]: #search for all 80m files
            seznam80m.append(os.path.join(dirpath, filename))

for vsak80, vsak80m pot in zip(seznam80, seznam80m, dirp):
        path = pot + '_all_anal.xlsx'

        file1 = vsak80
        df1 = all_calc_80(file1, directory)

        file2 = vsak80m
        df2 = all_calc_80m(file2, directory)

        writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
        df1.to_excel(writer, sheet_name = '80')
        df2.to_excel(writer, sheet_name = '80m')
        
        writer.close()
nezaslo4
  • 25
  • 5