+1 514-978-9422 •

Repository

Import Excel & CSV files with Python into a dataframe

Pandas Import CSV Excel Aug 28, 2020

When working with data in Python, you will definitely need to import spreadsheets at some point. In this post, we will provide Microsoft Windows Users with several options for importing Excel spreadsheets and CSV files into Pandas dataframes.

Import files from the current working directory (CWD)

The easiest way to import an Excel spreadsheet or CSV file to a dataframe is to place the file in the same directory as where your .py script is located. Therefore, you can refer to the file in your script just by its name provided that the file and the script are in the same directory.

import pandas as pd

# Import a CSV file
df = pd.read_csv('data.csv')

# Import an XLSX file
df = pd.read_excel('data.xlsx')

Referencing the full path

Although Windows file path uses the backslash "\", it is recommended to use the slash "/" instead. If you decide to use the backslash, you will have to escape each instance with "\\".

import pandas as pd

# Import a CSV file
df = pd.read_csv('C:/Users/Username/data.csv')

# Import an XLSX file
df = pd.read_excel('C:/Users/Username/data.xlsx')

# Using the backslash
df = pd.read_excel('C:\\Users\\Username\\data.xlsx')

Changing the current working directory (CWD)

If you plan to work on several Excel spreadsheets or CSV files all located in another directory, you might save some time by changing your working directory. By changing the working directory, you will only need to refer to the file name.

import os
import pandas as pd

# The current working directory (CWD) : 'C:/Users/Username'

# Changing CWD to a sub-directory : 'C:/Users/Username/my_script/my_excel-files'
os.chdir('my_data/my_excel-files')

# Changing the CWD at the root : 'C:/Users/Username/my_data/my_excel-files'
os.chdir(r'C:/Users/Username/my_script/my_excel-files')
# Absolute path, using the prefix r

# Once you have changed the CWD, you can simply reference the XLSX file
df = pd.read_excel('data.xlsx')

Based on the Windows user's username

Sometimes, like when using Python scripts in Power BI Desktop, you might want your script to work for multiple users, allowing them to import data from their local drive. In this case, you will need to first retrieve the username and then inject it into the file path.

import os
import pandas as pd

# Get the Windows username
user = os.environ.get('USERNAME')

# Import the spreadsheet
df = pd.read_excel(r'C:/Users/' + user + 'Username/data.xlsx')


Tags: Pandas Import CSV Excel