Parsing Data in Excel Sheets with Python to Create a Custom CSV File
01/03/2021
Today, I will be explaining how to use openpyxl to get specific data from excel sheets and compile it into a CSV file.
First, you need to install openpyxl by going into your command prompt and typing pip install openpyxl.
Next, we will go over how to access the cells of the excel file. To begin, import load_workbook. Use load_workbook to load the excel file. Access a sheet of the file similar to a dictionary by using the sheet name. Similarly, do that with the sheet to access cells, and do .value to get the actual value of the cell instead of the cell object.
Next, to actually write the values to a CSV file, you need to first open a file to write to. When you write to the file, you can use the values you accessed from the excel file. Ensure you use escape characters to have quotation marks around the strings and end each line with \n. Finally, ensure you close the file.
Furthermore, if you are writing to the CSV file from several excel files that have the same structure, you can easily use a for loop to go through the files and compile the data.
