Dan

A Web Development Professional with experience in entrepreneurship, web applications and digital marketing.

Using Python to Manipulate Excel Files (Beginner)

The Goal

To add commas to the end of the values in column A.

Step 1: Setup an Excel file with your data.

Open the Excel workbook you want to modify. Make sure you know the filename and sheet names. For example, Filename: “adding_commas.xlsx” and Sheet name: “randomnums”

Step 3: Write the barebones of your Python script

Open up the Python script in the text editor and copy/paste the code below.

import openpyxl

#Insert the name of your Excel file where is says 'adding_commas.xlsx'
wb = openpyxl.load_workbook('adding_commas.xlsx') 
#Insert the name of the Sheet 
sheet = wb.get_sheet_by_name('randomnums')
#Type the name of whatever you want the new sheet to be called
output_sheet = wb.create_sheet('randomnums_with_commas')

##########
#All of the code to manipulate the data goes in here!!!!!!
##########

#Re-type the workbook you want to save.
#If use a different name than what you are working in, it will save a new workbook instead with that new name
wb.save("adding_commas.xlsx")
  • Import Openpyxl: Tells Python that you want to use Openpyxl, which is the package that helps us open Excel documents. By Importing it into our script, we can use all of the functions and data that someone else has written to easily manage Excel documents.
  • wb = openpyxl.load_workbook(‘adding_commas.xlsx’): This means set a variable called ‘wb’ to an open version of the workbook you want to manipulate. Think of this line like opening the workbook in the code so you have access to the stuff inside of it.
  • sheet = wb.get_sheet_by_name(‘randomnums’): Set a variable called ‘sheet’ equal to the values on whatever is on the sheet named ‘randomnums’
  • output_sheet = wb.create_sheet(‘randomnums_with_commas’): This is where I define ‘output_sheet’ variable which is where all the values we want to output will go into. The sheet will be named ‘randomnums_with_commas’.
  • wb.save(“adding_commas.xlsx”): Means save all the changes you just made into ‘adding_commas.xlsx’. If you put a differently named .xlsx file in the ” “, then Openpyxl would create a new workbook and save the changes in the newly named workbook.

Step 4: Write code to manipulate the file

This step is where you can finally write unique Python code to do whatever you want to do. In our case, we want to add commas to the end of every value in the ‘randomnums’ sheet. So we want to write a For loop, that says loop through every number in a particular column, and append a comma to it. To refer to the value of a particular cell, we use our ‘sheet’ variable, the cell() module and .value at the end,
like this sheet.cell(row=1, column=1).value. That would give us the value of the cell in row 1 and column 1 on the ‘randomnums’ sheet. For each value inside the ‘sheet’ we want to take that value, add a comma, then store those in the ‘output_sheet’ cell values, which is what the following code does. Click here for information on what you can do with Openpyxl.

import openpyxl

wb = openpyxl.load_workbook('adding_commas.xlsx')
sheet = wb.get_sheet_by_name('randomnums')
output_sheet = wb.create_sheet('randomnums_with_commas')

#Add commas to the values from sheet, then store them in the output_sheet
for i in range(1, 200):
    output_sheet.cell(row=i, column=1).value = str(sheet.cell(row=i, column=1).value) + ', '

wb.save("adding_commas.xlsx")

Step 5: Execute the code

To actually run this code you need Python installed on your computer. Open up Terminal or Command Prompt and change directory into your folder. If you don’t know what this means, then type dir or ls. If you see ‘Desktop’ then type cd Desktop, enter, then cd ‘Name of your new folder’, enter. Then type ‘python pyfilename.py’ and enter. If you have Python installed (all Macs do), and do not see any errors, then you can open up the Excel file and it should have a new sheet with commas added! (In my example I had to cd or change directory into a few extra folders than just one off my desktop)

Conclusion

Working with Excel in Python can be pretty powerful. The use-cases are limitless and can be combined with all other Python packages and modules. For example, I also built a script to grab updated stock prices from Yahoo Finance and put them into an Excel workbook showing how much money I lost or made between days. To learn more about Python and its applications – I recommend this Udemy course and the book Automate the Boring Stuff with Python. Feel free to copy/paste the code from below to play around with what I did in this post.


import openpyxl
wb = openpyxl.load_workbook('adding_commas.xlsx')
sheet = wb.get_sheet_by_name('randomnums')
output_sheet = wb.create_sheet('randomnums_with_commas')

#Add commas to the values from sheet, then store them in the output_sheet
for i in range(1, 200):
    output_sheet.cell(row=i, column=1).value = str(sheet.cell(row=i, column=1).value) + ', '

wb.save("adding_commas.xlsx")

Leave a Reply

Your email address will not be published. Required fields are marked *