Pandas to_excel illegalcharactererror

by

When using the pandas to_excel() function to save a DataFrame as an Excel file, you may encounter the “IllegalCharacterError” if there are any illegal characters in the column names or sheet names.

The “IllegalCharacterError” is raised when the Excel writer encounters a character that is not allowed in the Excel file format. This typically occurs when you have special characters such as slashes (“/” or “\”), colons (“:”), asterisks (“*”), question marks (“?”), square brackets (“[” or “]”), or any character that is not supported by the Excel file format.

To resolve this issue, you need to remove or replace the illegal characters from the column names or sheet names. Here are a few examples:

Example 1: Removing illegal characters from column names

import pandas as pd

# Create a DataFrame with column names containing illegal characters
df = pd.DataFrame({'Column/Name': ['Value 1', 'Value 2', 'Value 3']})

# Replace the illegal characters with underscores
df.columns = df.columns.str.replace('[\/:*?\[\]]', '_')

# Save the DataFrame as an Excel file without any errors
df.to_excel('output.xlsx')

In this example, we create a DataFrame with a column name “Column/Name” that contains a forward slash (“/”), which is an illegal character. We use the str.replace() function to replace the illegal characters with underscores before saving the DataFrame as an Excel file.

Example 2: Removing illegal characters from sheet names

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({'Column 1': [1, 2, 3], 'Column 2': [4, 5, 6]})

# Save the DataFrame as an Excel file with a sheet name containing illegal characters
df.to_excel('output.xlsx', sheet_name='Sheet/Name')

In this example, we create a DataFrame and try to save it as an Excel file with a sheet name “Sheet/Name” that contains a forward slash (“/”), which is an illegal character. This will raise the “IllegalCharacterError”.

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({'Column 1': [1, 2, 3], 'Column 2': [4, 5, 6]})

# Save the DataFrame as an Excel file with a sanitized sheet name
df.to_excel('output.xlsx', sheet_name='Sheet_Name')

To fix this issue, we can modify the sheet name to “Sheet_Name” or any other name without illegal characters to save the DataFrame as an Excel file without any errors.

Leave a comment