[PYTHON] Batch convert all xlsx files in the folder to CSV files

Background / premise / specifications

A memo for creating an Excel operation tool for work. Derivation of this ↓. Get all xlsx filenames in a folder in a natural sort order and build an absolute path

How to use

  1. Create two new folders on your desktop with the names "folder1" and "folder2".

  2. In "folder1", throw all the xlsx files you want to convert.

  3. Move.

  4. Converted to a csv file and saved in "folder2".

Execution environment

The execution environment and the version of each library are as follows.

Batch convert all xlsx files in the folder to CSV files.


import os
import csv
import openpyxl
from natsort import natsorted

#Arbitrary folder name created on the desktop

folder_name = "folder1"
folder_2_name = "folder2"

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

Batch convert all xlsx files in the folder to CSV files
Read all csv files in the folder
2 ways to read all csv files in a folder
Convert UTF-8 CSV files to read in Excel
How to get the files in the [Python] folder
Batch convert PSD files in directory to PDF
Convert XLSX to CSV on the command line
Output the contents of ~ .xlsx in the folder to HTML with Python
How to combine all CSVs in a folder into one CSV
How to convert csv to tsv in CLI
How to read CSV files in Pandas
Create a command to delete all temporary files generated in a specific folder
I want to batch convert the result of "string" .split () in Python
[Python] Get the files in a folder with Python
Convert FBX files to ASCII <-> BINARY in Python
Convert the image in .zip to PDF with Python
Convert financial information of all listed companies for the past 5 years to CSV file
[Python] Open the csv file in the folder specified by pandas
[R] [Python] Memo to read multiple csv files in multiple zip files
Batch convert image files uploaded to MS Forms / Google Forms to PDF
Process the files in the folder in order with a shell script
How to get all the keys and values in the dictionary
Convert SDF to CSV quickly
Convert only date serial numbers in CSV files with awk
Command to list all files in order of file name
Convert PDF of the progress of the division of labor (trends in insurance dispensing) of the Japan Pharmaceutical Association to CSV
Determine the date and time format in Python and convert to Unixtime
How to get all the possible values in a regular expression
Convert files written in python etc. to pdf with syntax highlighting
After calling the Shell file on Python, convert CSV to Parquet.
How to use the asterisk (*) in Python. Maybe this is all? ..
For the time being, I want to convert files with ffmpeg !!
Regularly upload files to Google Drive using the Google Drive API in Python
Programming to fight in the world ~ 5-1
Programming to fight in the world ~ 5-5,5-6
Transpose CSV files in Python Part 1
Convert markdown to PDF in Python
In the python command python points to python3.8
How to convert 0.5 to 1056964608 in one shot
Compress all the text files below!
Bring files in Windows to WSL
Cython to try in the shortest
Programming to fight in the world ~ 5-2
Convert PDF of available stores of Go To EAT in Kagoshima prefecture to CSV
Convert XML document stored in XML database (BaseX) to CSV format (using Python)
Hit the Rakuten Ranking API to save the ranking of any category in CSV
I want to convert a table converted to PDF in Python back to CSV
Convert the spreadsheet to CSV and upload it to Cloud Storage with Cloud Functions
I made a script in python to convert .md files to Scrapbox format
Convert PDF of Go To EAT member stores in Ishikawa prefecture to CSV
Convert PDF of new corona outbreak case in Aichi prefecture to CSV
[Python] How to convert db file to csv
[Python] Convert csv file delimiters to tab delimiters
Convert psd file to png in Python
In Jupyter, add IPerl to the kernel.
I saved the scraped data in CSV!
Convert PDF files to PNG files with GIMP
Various comments to write in the program
Convert HEIC files to PNG files with Python
How to convert DateTimeField format in Django
Convert from Markdown to HTML in Python