How to Convert Excel to CSV in Python
In this article, we will show you how to convert an excel file to the CSV File (Comma Separated Values) using python.
Assume we have taken an excel file with the name sampleTutorialsPoint.xlsx containing some random text. We will return a CSV File after converting the given excel file into a CSV file.
sampleTutorialsPoint.xlsx
| Player Name | Age | Type | Country | Team | Runs | Wickets |
|---|---|---|---|---|---|---|
| Virat Kohli | 33 | Batsman | India | Royal Challengers Bangalore | 6300 | 20 |
| Bhuvaneshwar Kumar | 34 | Batsman | India | Sun Risers Hyderabad | 333 | 140 |
| Mahendra Singh Dhoni | 39 | Batsman | India | Chennai Super Kings | 4500 | 0 |
| Rashid Khan | 28 | Bowler | Afghanistan | Gujarat Titans | 500 | 130 |
| Hardik Pandya | 29 | All rounder | India | Gujarat Titans | 2400 | 85 |
| David Warner | 34 | Batsman | Australia | Delhi Capitals | 5500 | 12 |
| Kieron Pollard | 35 | All rounder | West Indies | Mumbai Indians | 3000 | 67 |
| Rohit Sharma | 33 | Batsman | India | Mumbai Indians | 5456 | 20 |
| Kane Williamson | 33 | Batsman | New Zealand | Sun Risers Hyderabad | 3222 | 5 |
| Kagiso Rabada | 29 | Bowler | South Africa | Lucknow Capitals | 335 | 111 |
Method 1: Converting Excel to CSV using Pandas Module
Algorithm (Steps)
Following are the Algorithm/steps to be followed to perform the desired task −
Import the pandas module (Pandas is a Python open-source data manipulation and analysis package)
Create a variable to store the path of the input excel file.
Read the given excel file content using the pandas read_excel() function(reads an excel file object into a data frame object).
Convert the excel file into a CSV file using the to_csv() function(converts object into a CSV file) by passing the output excel file name, index as None, and header as true as arguments.
Read the output CSV file with the read_csv() function(loads a CSV file as a pandas data frame) and convert it to a data frame object with the pandas module’s DataFrame() function.
Show/display the data frame object.
Example
The following program converts an excel file into a CSV file and returns a new CSV file
Output
On executing, the above program will generate the following output −
In this program, we use the pandas read_excel() function to read an excel file containing some random dummy data, and then we use the to csv() function to convert the excel file to csv. If we pass the index as a false argument, the final CSV file does not contain the index row at the beginning. Then we converted the CSV to a data frame to see if the values from the excel file were copied into the CSV file.
Method 2: Converting Excel to CSV using openpyxl and CSV Modules
Algorithm (Steps)
Following are the Algorithm/steps to be followed to perform the desired task −
Use the import keyword, to import the openpyxl(Openpyxl is a Python package for interacting with and managing Excel files. Excel 2010 and later files with the xlsx/xlsm/xltx/xltm extensions are supported. Data scientists use Openpyxl for data analysis, data copying, data mining, drawing charts, styling sheets, formula addition, and other operations) and CSV modules.
Create a variable to store the path of the input excel file.
To create/load a workbook object, pass the input excel file to the openpyxl module’s load_workbook() function (loads a workbook).
Opening an output CSV file in write mode with open() and writer() functions to convert an input excel file into a CSV file.
Using the for loop, traverse each row of the worksheet.
Use the writerow() function, to write cell data of the excel file into the result CSV file row-by-row.
Example
The following program converts an excel file into a CSV file and returns a new CSV file −
Output
On executing, the above program a new CSV file (ResultCsvFile.csv) will be created with data of Excel.
In this program, we have an excel file with some random dummy data, which we load as an openpyxl work and set to use using the active attribute. Then we made a new CSV file and opened it in writing mode, then we went through the excel file row by row and copied the data into the newly created CSV file.
Conclusion
In this tutorial, we learned how to read an excel file and convert it to an openpyxl workbook, then how to convert it to a CSV file and remove the index, and finally how to convert the CSV file to a pandas data frame.
Extracting Data from Excel Files
When people save data in the JSON or CSV format, they’re intending for that data to be accessed programmatically. But much of the world’s data is stored in spreadsheet files, and many of those files are in the Excel format. Excel is used because people can manipulate it easily, and it’s a powerful tool in its own right. However, there is a lot of automation that can be done by extracting data from a spreadsheet, and this process also allows you to bring data from multiple kinds of sources into one program.
We’ll first take a quick look at how to save an Excel file as a CSV file. This is sometimes the quickest and easiest way to extract data. But it’s a manual process, so you’d have to open the file in Excel and save it as a CSV again every time the file is updated. It’s much better in many situations to just extract the data from Excel directly.
The example we’ll use is the data you can download from Mapping Police Violence. If you can’t download this file from the site for some reason, you can also find a snapshot of this spreadsheet from 6/19/20 in the beyond_pcc/social_justice_datasets/ directory of the online resources for Python Crash Course.
Converting an Excel File to CSV
You can create a CSV file from any single worksheet in an Excel workbook. To do this, first click on the tab for the worksheet you want to focus on. Then choose File > Save As, and in the File Format dropdown choose CSV UTF-8 (Comma-delimited) (.csv). You’ll get a message that the entire workbook can’t be saved in this format, but if you click OK you’ll get a copy of the current worksheet in CSV format.
To look at the file and make sure it contains the data you expect it to, locate the new CSV file in a file browser and open it with a text editor. If you open the file with a spreadsheet application like Excel, it won’t look any different than a regular Excel file.
Installing openpyxl
We’ll be using the openpyxl library to access the data in an Excel file. You can install this library with pip:
Opening an Excel File
To follow along with this tutorial, make a folder somewhere on your system called extracting_from_excel. Make a data folder inside this directory; it’s a good idea to keep your data files in their own directory. I saved the file mapping_police_violence_snapshot_061920.xlsx in my data directory; you can work with this file, or any .xls or .xlsx file you’re interested in.
The following code will open the Excel file and print the names of all worksheets in the file:
First we import the load_workbook() function, and assign the path to the data file to data_file . Then we call load_workbook() with the correct path, and assign the returned object, representing the entire workbook, to wb . You’ll see this convention in the documentation for openpyxl.
The names of all worksheets in the file are stored in the sheetnames attribute. Here’s the output for this data file:
Accessing Data in a Worksheet
We want to access the actual data in a specific worksheet. To do this we grab the worksheet we’re interested in, and then extract the data from all rows in the worksheet:
Worksheets are accessed by name through the workbook object. Here we assign a worksheet to ws . Once you have a worksheet object, you can access all the rows through the ws.rows attribute. This attribute is a generator, a Python object that efficiently returns one item at a time from a collection. We can convert this to the more familar list using the list() function. Here we create a list of all the rows in the workbook. We then print a message about how many rows were found, and print the first few rows of data:
In this worksheet, we found 55 rows of data. Each row of data is made up of a series of cell objects.
Accessing Data from Cells
So far we have accessed the Excel file, an individual worksheet, and a series of rows. Now we can access the actual data in the cells.
To begin with, we’ll look at just the data in the first row:
We loop through all cells in the row, and print the value of each cell. This is accessed through the value attribute of the cell object.
Extracting Data from Specific Cells
The previous example is helpful, perhaps, when looking at a list of headings for a worksheet over a remote connection. But usually when we’re analyzing the data from a spreadsheet we can just open the file in Excel, look for the information we want, and then write code to extract that information. We usually aren’t interested in every single cell in a row, though. We’re often interested in selected cells in every row in the sheet.
The following example pulls data from three specific columns in each row in the file containing the data we’re interested in:
Here we loop through the all of the rows that contain the states’ data. For each row, we pull the values at index 0, 3, and 4, and assign each of these to an appropriate variable name. We then print a statement summarizing what these values mean.
The output isn’t quite what we expect:
The values in these cells are actually formulas. If we want the values computed from these formulas, we need to pass the data_only=True flag when we load the workbook:
Now we see output that’s much more like what we were expecting:
Data analysis almost always involves some degree of reformatting. For this output, we’ll round the percentages to two decimal places, and turn them into neatly-formatted integers for display:
Here’s the cleaner output:
Be careful about rounding data during the processing phase. If you were going to pass this data to a plotting library, you probably want to do the rounding in the plotting code. This can affect your visualization. For example if two percentages round to the same value in two decimal places but they’re different in the third decimal place, you’ll lose the ability to sort items precisely. In this situation, it’s important to ask whether the third decimal place is meaningful or not.
Also, note that you will often need to identify the specific rows that need to be looped over. Spreadsheets are nice and structured, but people are also free to write anything they want in any cell. Many spreadsheets have some notes in a few cells after all the rows of data. These can be notes about sources of the raw data, dates of data collection, authors, and more. You will probably need to exclude these rows, either by looping over a slice as shown here, or using a try/except block to only extract data if the operation for each row is successful.
Finally, you should be aware that people can modify the hard-coded values in a spreadsheet without updating the values derived from formulas that use those values. If you have any doubt about whether the spreadhseet you’re working from has been updated, you should re-run the formulas yourself before using the data_only=True flag when loading a workbook.
Refactoring
That’s probably enough to get you started working with data that’s stored in Excel files, but it’s worth showing a bit of refactoring on the program we’ve been using in this tutorial. Here’s what the code looks like at this point:
If all we wanted to do was generate a text summary of this data, this code would probably be fine. But we’re probably going to do some visualization work, and maybe we want to bring in some additional data from another file. If we’re going to do anything further, it’s worth breaking this into a couple functions. Here’s how we might organize this code:
We organize the code into two functions, one for retrieving data and one for summarizing data. The function get_all_rows() can be used to load all the rows from any worksheet in any data file. The function summarize_data() is specific to this context, and would probably have a more specific name in a more complete project.
Further Reading
There’s a lot more you can do with Excel files in your Python programs. For example, you can modify data in an existing Excel file, or you can extract the data you’re interested in and generate an entirely new Excel file. To learn more about these possibilities, see the openpyxl documentation. You can also extract the data from Excel and rewrite it in any other data format such as JSON or CSV.
Convert XLSX to CSV in Python
XLSX is a file extension for Microsoft Excel spreadsheets, while CSV is a Comma-Separated Value file.
This article discusses using Python to convert XLSX into CSV using two methods.
- Method 1: Using the pandas package and,
- Method 2: Using openpyxl and csv modules.
We will use the employees.xlsx Excel with two worksheets – names and roles. See the Figure below.
The objective is to learn how to use the two methods stated above to convert any or all of the sheets in the XLSX file into CSV.
Method 1: Using pandas Package
This method involves reading the XLSX file into pandas DataFrame using pandas.read_excel() function and then write the DataFrame into a CSV file using DataFrame.to_csv().
For this method, you may need to install pandas and openpyxl packages using pip as follows:
Sheet: Data conversion¶
Suppose you want to process the following excel data :
Here are the example code:
How to save an python array as an excel file¶
Suppose you have the following array:
And here is the code to save it as an excel file
How to save an python array as a csv file with special delimiter¶
Suppose you have the following array:
And here is the code to save it as an excel file
How to get a dictionary from an excel sheet¶
Suppose you have a csv, xls, xlsx file as the following:
The following code will give you data series in a dictionary:
Please note that my_dict is an OrderedDict.
How to obtain a dictionary from a multiple sheet book¶
Suppose you have a multiple sheet book as the following:
- Sheet 1
- Sheet 2
- Sheet 3
Here is the code to obtain those sheets as a single dictionary:
How to save a dictionary of two dimensional array as an excel file¶
Suppose you want to save the below dictionary to an excel file
Here is the code:
If you want to preserve the order of sheets in your dictionary, you have to pass on an ordered dictionary to the function itself. For example:
Let’s verify its order:
Please notice that “Sheet 2” is the first item in the book_dict, meaning the order of sheets are preserved.
How to import an excel sheet to a database using SQLAlchemy¶
You can find the complete code of this example in examples folder on github
Before going ahead, let’s import the needed components and initialize sql engine and table base:
Let’s suppose we have the following database model:
Let’s create the table:
Now here is a sample excel file to be saved to the table:
Here is the code to import it:
Done it. It is that simple. Let’s verify what has been imported to make sure.
How to open an xls file and save it as csv¶
Suppose we want to save previous used example ‘birth.xls’ as a csv file
Again it is really simple. Let’s verify what we have gotten:
Please note that csv(comma separate value) file is pure text file. Formula, charts, images and formatting in xls file will disappear no matter which transcoding tool you use. Hence, pyexcel is a quick alternative for this transcoding job.
How to open an xls file and save it as xlsx¶
Formula, charts, images and formatting in xls file will disappear as pyexcel does not support Formula, charts, images and formatting.
Let use previous example and save it as ods instead
Again let’s verify what we have gotten:
How to open a xls multiple sheet excel book and save it as csv¶
Well, you write similar codes as before but you will need to use save_book_as() function.