ENH: Add `excel_sep_hint` parameter to `to_csv` for Excel compatibility

[EwoutH]

Feature Type

• Adding new functionality to pandas

Problem Description

It would be great if Pandas could generate CSV files that Excel automatically opens with the correct delimiter. When using semicolons or other non-comma separators, Excel often opens CSV files with all data in one column unless a sep= hint is present at the beginning of the file. Currently, users must write custom code to add this hint.

Feature Description

Add a boolean parameter excel_sep_hint to to_csv() that prepends a delimiter hint for Excel:

def to_csv(self, ..., excel_sep_hint=False):
    """
    excel_sep_hint : bool, default False
        If True, prepend 'sep=' line to help Excel detect delimiter
    """

Usage:

df.to_csv('data.csv', sep=';', excel_sep_hint=True)

Output:

sep=;
col1;col2;col3
val1;val2;val3

Alternative Solutions

Users currently write wrapper functions or handle files manually:

with open('file.csv', 'w') as f:
    f.write('sep=;\n')
df.to_csv('file.csv', sep=';', mode='a')

Additional Context

The sep= hint is a standard Excel feature for delimiter detection. This would improve pandas-Excel interoperability, especially useful in European locales where semicolons are common CSV separators.

[sajansshergill]

def to_csv(
self,
path_or_buf=None,
sep=',',
...,
excel_sep_hint=False,
):
"""
Parameters
----------
excel_sep_hint : bool, default False
If True, prepend a 'sep={sep}\n' line to help Microsoft Excel detect the delimiter automatically.
"""

df.to_csv("data.csv", sep=";", excel_sep_hint=True)

• Alternative Solutions (Current Workarounds)
  Since pandas doesn’t support this natively yet, users often do something like:

with open('data.csv', 'w', encoding='utf-8') as f:
f.write('sep=;\n')
df.to_csv(f, sep=';', index=False)

• Or wrap this logic in a helper function:

def to_csv_with_excel_hint(df, filename, sep=';', **kwargs):
with open(filename, 'w', encoding=kwargs.get('encoding', 'utf-8')) as f:
f.write(f'sep={sep}\n')
df.to_csv(f, sep=sep, **kwargs)