Importing and exporting data from Excel with PANDAS

Importing Data from CSV

If in csv format use pd.read_csv() as described in PANDAS documentation.

Importing Data from .xlsm and .xlsx

If there is only one tab on you excel workbook then you can just save the file as .csv and use pd.read_csv() described above. If not then you will need to use pd.ExcelFile()

example_xl = pd.ExcelFile(file_path+'example_excel.xlsx') #import excel object
sheet_names = list(example_xl.columns) #extract all sheets names 

#turning sheets into dataframes works in a similar way to .to_csv()
df = example_xl.parse('sheet 1',header=0) #turn sheet 1 into data frame 
df = example_xl.parse(sheet_names[0],header=0) #when you have lots of sheets I find this a lot easier

#make dictionary of all sheets if you want 
data_dict = {}
for sheet in sheet_names:
    data_dict[sheet] = example_xl.parse(sheet,header=0).copy()

Exporting Data to .xlsm and .xlsx

Using pd.read_excel()

Can get xlrd related error. xlrd is older and now seems deprecated, and doesn't have support for *.xlsx. Should instead be using openpyxl, which needs to be installed. Sometimes read_excel needs to be told to use the openpyxl library, using "engine='openpyxl'".

Basic DataFrames export

To export 1 DataFrame to 1 sheet of an excel workbook use .to_excel method on df. This will replace any workbook on the specified path. You can add multiple DataFrames to a workbook provided they are are addded to distinct sheets. You do this by using ExcelWriter object with a 'with' clause.

#State the path of the excel workbook
path_excel = r'/output/Python_output/test.xlsx' #the r signals that this is window path
#export single data frame
df.to_excel(path_excel,sheet_name='example df') 

#write to multiple sheets
with pd.ExcelWriter(path_excel, mode='a') as writer: #mode='a' => alters excel on path instead of replacing it 
     df_1.to_excel(writer,sheet_name='df_1 sheet')
     df_2.to_excel(writer,sheet_name='df_2 sheet')
print('finished exporting to excel') 

See documentation for full functionality of .to_excel(). If it is not working try using "engine='openpyxl'" in .to_excel() as the old engine xlrd is depreciated so sometimes throws errors. If you can't import openpyxl then it needs installing on your environment.

Adding Data to existing Workbook

The code below shows how you can export a DataFrame to an already existing excel sheet. It is a very manual process exporting each value of the DataFrame individually as you iterate through it. This will only work for up to 26 columns after which you would need to update the letters list (Only includes A- Z).

import string
#preamble
include_columns_excel = True 
letters_list = list(string.ascii_uppercase) #make list of letter so you convert numbers in to excel column letters


#open workbook to alter
xfile = openpyxl.load_workbook(path_excel, read_only=False, keep_vba=True)


############################### sheet 1 ##################################### 
sheet = xfile['Sheet Name To Alter 1'] #specify which sheet you want to alter
row_index = 2 #starting row index 
column_index = 2 #starting column index e.g this would correspond to column C

#add column names
if include_columns_excel:
    for x in list(df_1.columns):
        sheet[str(letters_list[column_index]+str(row_index))] = x
        column_index+=1
    row_index+=1 
#iterate through data adding each entry to excel file  
for index, row in df_1.iterrows():
    for x in row:
        if not pd.isna(x): #won't export null values 
            sheet[str(letters_list[column_index]+str(row_index))] = x
        column_index+=1
    row_index+=1

print("df_1 added")

############################### sheet 2 ##################################### 
sheet = xfile['Sheet Name To Alter 2'] #specify which sheet you want to alter
row_index = 2 #starting row index 
column_index = 2 #starting column index e.g this would correspond to column C

#add column names
if include_columns_excel:
    for x in list(df_2.columns):
        sheet[str(letters_list[column_index]+str(row_index))] = x
        column_index+=1
    row_index+=1 
#iterate through data adding each entry to excel file  
for index, row in df_2.iterrows():
    for x in row:
        if not pd.isna(x): #won't export null values 
            sheet[str(letters_list[column_index]+str(row_index))] = x
        column_index+=1
    row_index+=1

print("df_2 added")

xfile.save(path_excel) #dave the changes you have made