How to import Excel file in Python

Data storing can take place in many format in Microsoft Excel. One of the well known format is XLS or XLSX format, popularly know as Excel format. It is easier to import CSV file in python as discussed in the page. For Excel it becomes sometime a bit complex, as data can be present in multiple sheets. Hence, its important to know the various elements of import function to read an Excel file. To illustrate, the below article discusses step by step process to import Excel file in Python.

Emma has data for employees of her company. In one of the Excel sheet she has data with basic column like Employee Name, Gender. In the second sheet she has information like City & Salary. She wants to import both the sheets into python.

Below are the key steps for Emma to follow:

• Step 1: Firstly import Pandas module into Python.

              Import pandas as pd

• Step 2: Then from Pandas module use read_excel function, the first parameter will have file name and location.

              df1 = pd.read_excel("C:\Website\LearnEasySteps\Python\Excel_File_Data.xlsx",...

• Step 3: Also, second important parameter from read_excel function is sheet_name. This tells Python which sheet contains data for importing purpose. First we are importing data from “Data_sheet1” so the same will be the input. Press enter and data will be available as Pandas Dataframe.

      df1 = pd.read_excel("C:\Website\LearnEasySteps\Python\Excel_File_Data.xlsx",sheet_name="Data_Sheet1")

• Step 4: Finally to check if data looks ok in Python, the below code helps. This generated top few rows of the file.

               df1.head()
            

Importing the second data sheet:

• Step 1: Firstly, to import the second sheet as well, similar code line works. The parameter requiring a change is sheet_name. Here the sheet name will be “Data_sheet2”. Hit enter to run the code and data will be available as Pandas dataframe named as df2.

df2 = pd.read_excel("C:\Website\LearnEasySteps\Python\Excel_File_Data.xlsx",sheet_name="Data_Sheet2")

• Step 2: Then to check if the data has been imported correctly, use the below line of code to check first 5 rows of the file.

              df2.head()

Thus, Emma is able to create single Dataframe from Excel file as per her requirement in Python.

To get top certifications in Python and build your resume visit here. Also, you can read books listed here to build strong knowledge around Python.

Visit below to see the video tutorial as well:

Looking to practice more with this example? Drop us a note, we will email you the Code file: