import os
import csv
import openpyxl
from natsort import natsorted


#Arbitrary folder name created on the desktop

folder_name = "folder1"
folder_2_name = "folder2"


#constant
desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
folder_path = os.path.join(desktop_path, folder_name)
folder_2_path = os.path.join(desktop_path, folder_2_name)


#Get Excel file name list in natural order
files = natsorted(os.listdir(folder_path))

#Build the absolute path of each file by turning the file name list with a for statement
for filename in files:
    filepath = os.path.join(folder_path, filename)
    
    #Access xlsx file → Get the object of the first sheet
    wb = openpyxl.load_workbook(filepath)
    ws_name = wb.sheetnames[0]
    ws = wb[ws_name]
 
    #Convert to csv and save in folder2
    savecsv_path = os.path.join(folder_2_path, filename.rstrip(".xlsx")+".csv")
    with open(savecsv_path, 'w', newline="") as csvfile:
        writer = csv.writer(csvfile)
        for row in ws.rows:
            writer.writerow([cell.value for cell in row])
    

Recommended Posts