How to convert .XLSX to .CSV using Python?

General Discussions
, ,
1

I am looking for a Python library or guidance on how to correctly convert .XLSX files to .CSV files. Could you please provide assistance on how to Convert xlsx to csv Python?

2

I’ve been working with Excel files for a while now, and one way I find useful to convert xlsx to csv python is by using the xlrd and csv modules. It’s a straightforward approach, especially if you’re handling older .xlsx files. First, make sure you have xlrd installed:

pip install xlrd

Then, here’s the Python script to do the job:

import xlrd
import csv

def csv_from_excel():
    wb = xlrd.open_workbook('excel.xlsx')
    sh = wb.sheet_by_name('Sheet1')
    with open('your_csv_file.csv', 'w', newline='') as your_csv_file:
        wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)
        for rownum in range(sh.nrows):
            wr.writerow(sh.row_values(rownum))

# runs the csv_from_excel function:
csv_from_excel()

This will open the .xlsx file and write its contents to a .csv file. It’s simple, and works well for me!

3

Ah, yes, @sam.aarun, that’s a good method! But if you’re working with .xlsx files that have more complex data or newer formatting, I’d suggest using openpyxl. I’ve been using it for quite some time to convert xlsx to csv python, and it handles the modern .xlsx format perfectly. Here’s what you need to do:

First, install openpyxl:

pip install openpyxl

Then, use this script:

import openpyxl
import csv

def convert_xlsx_to_csv():
    wb = openpyxl.load_workbook('excel.xlsx')
    sheet = wb.active
    with open('your_csv_file.csv', 'w', newline='') as f:
        writer = csv.writer(f)
        for row in sheet.iter_rows(values_only=True):
            writer.writerow(row)

# Runs the conversion function
convert_xlsx_to_csv()

This method reads the .xlsx file and then writes its contents to a .csv. You’ll find this approach a bit more robust for handling newer Excel files!

4

Great points, @sam.aarun! I’ve had similar experiences with both of those methods, but I personally prefer using pandas for converting xlsx to csv python. It makes the process super simple and you get a lot of flexibility with your data too. It’s like the go-to solution for me when dealing with dataframes, especially with larger files.

Here’s how I do it:

First, make sure pandas is installed:

pip install pandas

Then, you can use this script:

import pandas as pd

def convert_xlsx_to_csv():
    df = pd.read_excel('excel.xlsx', sheet_name='Sheet1')
    df.to_csv('your_csv_file.csv', index=False)

# Runs the conversion function
convert_xlsx_to_csv()

This script loads the .xlsx file into a dataframe and writes it to .csv without the hassle of iterating over rows. It’s fast and very efficient for any .xlsx to .csv conversion task.