Batch convert CSV to XLSX keeping subfolder structure in python

Question

can someone please advise how can I batch convert 100+ CSV files all with different schemas to individual XLSX files keeping the original subfolder structure using Python? In nutshell, what i am need to get:

Folder:
  Subfolder 1:
  file.csv
  Subfolder 2:
  file2.csv
  ...
  Subfolder N:
  fileN.csv

Aiming for:

Folder:
  Subfolder 1:
  file.csv
  file.xlsx
  Subfolder 2:
  file2.csv
  file2.xlsx
  ...
  Subfolder N:
  fileN.csv
  fileN.xlsx

File names are not uniform.

I am trying the below code but it is only converting the file in the root folder.

for root, dirs, files in os.walk('.'):
    for file in files:
        for file in glob.glob(os.path.join('.', '*.csv')):
            wb = openpyxl.Workbook()
            ws = wb.active
            with open(os.path.join(file), 'r') as f:
                reader = csv.reader(f)
                for r, row in enumerate(reader, start=1):
                    for c, val in enumerate(row, start=1):
                        ws.cell(row=r, column=c).value = val
            wb.save(csvfile + '.xlsx')

Does this answer your question? [How to use glob() to find files recursively?](https://stackoverflow.com/questions/2186525/how-to-use-glob-to-find-files-recursively) — Maurice Meyer, Feb 27 '20 at 16:13
@umidjon Finding a solution is great, but it’s not the only thing that matters, right? — AMC, Feb 28 '20 at 15:11
@AMC, as mentioned in the question, the problem was that code was not converting CSVs in the subfolders, but only in the root folder. Thanks for your time. — umidjon, Mar 01 '20 at 20:32

score 0 · Accepted Answer · answered Feb 27 '20 at 18:11

tailoring the code, i was able to get it work. Not the neatest one, but works:

for root, dirs, files in os.walk('.'):
    for file in files:
        if os.path.splitext(file)[-1] == ".csv":
            file_path = os.path.join(root, file)
            wb = openpyxl.Workbook()
            ws = wb.active
            with open(os.path.join(file_path), 'r') as f:
                reader = csv.reader(f)
                for r, row in enumerate(reader, start=1):
                    for c, val in enumerate(row, start=1):
                        ws.cell(row=r, column=c).value = val
            wb.save(file_path + '.xlsx')

Batch convert CSV to XLSX keeping subfolder structure in python

1 Answers1