💡 Challenge: Given a CSV file. How to convert it to an excel file in Python?
We create a folder with two files, the file
my_file.csv. We want to convert the CSV file to an excel file so that after running the script
csv_to_excel.py, we obtain the third file
my_file.csv in our folder like so:
All methods discussed in this tutorial show different code snippets to put into
csv_to_excel.py so that it converts the CSV to XLSX in Python.
Method 1: 5 Easy Steps in Pandas
The most pythonic way to convert a
.csv to an
.xlsx (Excel) in Python is to use the Pandas library.
- Install the
pip install pandas
- Install the
openpyxllibrary that is used internally by pandas with
pip install openpyxl
- Import the
import pandas as pd
- Read the CSV file into a DataFrame
dfby using the expression
df = pd.read_csv('my_file.csv')
- Store the DataFrame in an Excel file by calling
df.to_excel('my_file.xlsx', index=None, header=True)
import pandas as pd df = pd.read_csv('my_file.csv') df.to_excel('my_file.xlsx', index=None, header=True)
Note that there are many ways to customize the
to_excel() function in case
- you don’t need a header line,
- you want to fix the first line in the Excel file,
- you want to format the cells as numbers instead of strings, or
- you have an index column in the original CSV and want to consider it in the Excel file too.
If you want to do any of those, feel free to read our full guide on the Finxter blog here:
🌍 Tutorial: Pandas
DataFrame.to_excel() – An Unofficial Guide to Saving Data to Excel
Also, we’ve recorded a video on the ins and outs of this method here:
Let’s have a look at an alternative to converting a CSV to an Excel file in Python:
Method 2: Modules csv and openpyxl
To convert a CSV to an Excel file, you can also use the following approach:
- Import the
- Import the
- Read the CSV file into a list of lists, one inner list per row, by using the
- Write the list of lists to the Excel file by using the workbook representation of the
- Get the active worksheet by calling
- Write to the worksheet by calling
worksheet.append(row)and append one list of values, one value per cell.
The following function converts a given CSV to an Excel file:
import csv import openpyxl def csv_to_excel(csv_filename, excel_filename): # Read CSV file csv_data =  with open(csv_filename) as f: csv_data = [row for row in csv.reader(f)] # Write to Excel file workbook = openpyxl.workbook.Workbook() worksheet = workbook.active for row in csv_data: worksheet.append(row) workbook.save(excel_filename) if __name__ == "__main__": csv_to_excel("my_file.csv", "my_file.xlsx")
This is a bit more fine-granular approach and it allows you to modify each row in the code or even write additional details into the Excel worksheet.
More Python CSV Conversions
🐍 Learn More: I have compiled an “ultimate guide” on the Finxter blog that shows you the best method, respectively, to convert a CSV file to JSON, Excel, dictionary, Parquet, list, list of lists, list of tuples, text file, DataFrame, XML, NumPy array, and list of dictionaries.
Where to Go From Here?
Enough theory. Let’s get some practice!
Coders get paid six figures and more because they can solve problems more effectively using machine intelligence and automation.
To become more successful in coding, solve more real problems for real people. That’s how you polish the skills you really need in practice. After all, what’s the use of learning theory that nobody ever needs?
You build high-value coding skills by working on practical coding projects!
Do you want to stop learning with toy projects and focus on practical code projects that earn you money and solve real problems for people?
🚀 If your answer is YES!, consider becoming a Python freelance developer! It’s the best way of approaching the task of improving your Python skills—even if you are a complete beginner.
If you just want to learn about the freelancing opportunity, feel free to watch my free webinar “How to Build Your High-Income Skill Python” and learn how I grew my coding business online and how you can, too—from the comfort of your own home.
While working as a researcher in distributed systems, Dr. Christian Mayer found his love for teaching computer science students.
To help students reach higher levels of Python success, he founded the programming education website Finxter.com that has taught exponential skills to millions of coders worldwide. He’s the author of the best-selling programming books Python One-Liners (NoStarch 2020), The Art of Clean Code (NoStarch 2022), and The Book of Dash (NoStarch 2022). Chris also coauthored the Coffee Break Python series of self-published books. He’s a computer science enthusiast, freelancer, and owner of one of the top 10 largest Python blogs worldwide.
His passions are writing, reading, and coding. But his greatest passion is to serve aspiring coders through Finxter and help them to boost their skills. You can join his free email academy here.