How To Import Data Into Excel Using Python

Python has a wide range of libraries and modules that make it easy to import and analyze data in various formats. A particularly popular library for this purpose is Pandas, which provides high-performance, easy-to-use data structures, and data analysis tools.

In this tutorial, we will be using both Pandas and the openpyxl library to load and manipulate data.

Step 1: Installing Required Libraries

Before we begin, ensure that you have Python installed on your machine. Also, you need two major Python packages installed – Pandas for data manipulation and Openpyxl for working with Excel files. If you do not have these packages, you can install them using pip:

Step 2: Loading Data into a Pandas DataFrame

Pandas is built for data manipulation and it represents data in a tabular structure called DataFrame. The first step is to load our external data into a Pandas DataFrame for easy processing. The data can be in different formats like CSV, JSON, or even databases. In this tutorial, we’ll be using a CSV file. Suppose we have a CSV file called ‘sample_data.csv’ with the following content:


We’ll start by loading this data into a Pandas DataFrame. Here’s the code to do so:

The output should look like this:

    Name  Age  Country
0   John   35      USA
1    Kim   28   Canada
2  Boris   30       UK
3   Alia   29    India

Step 3: Creating a New Excel Workbook

Next, we’ll create a new Excel workbook using openpyxl:

Step 4: Writing Pandas DataFrame to Excel Sheet

Now, we’ll transfer the data from our Pandas DataFrame to the Excel sheet:

In this code snippet, we first obtain the active sheet in the workbook (usually the first sheet) and then use dataframe_to_rows method from openpyxl.utils to convert the DataFrame into rows that can be appended to the sheet.

Step 5: Saving the Excel Workbook

Finally, we’ll save the Excel workbook to a file:

The full code for importing data into Excel using python:


In this tutorial, we have demonstrated how to import data into Excel using Python. By leveraging the power of Pandas and Openpyxl, you can now easily import and manipulate data from various sources and save it into Excel format for further analysis. This method is highly customizable and can be extended for more complex scenarios like working with multiple sheets or data sources, applying data transformations or validations, and many more.