Search code examples
pythonpandasdataframeexcel-formulaquoting

Save a Pandas DataFrame to a CSV file without adding extra double quotes

I want to save a Pandas dataframe to a CSV file in such a way that no additional double quotes or any other characters are added to these formulas. Here is my attempt:

import pandas as pd

data = {
    "Column1": [1, 2, 3],
    "Column2": ["A", "B", "C"],
    "Formula": ['"=HYPERLINK(""https://www.yahoo.com"",""See Yahoo"")"', '"=HYPERLINK(""https://www.google.com"",""See Google"")"', '"=HYPERLINK(""https://www.bing.com"",""See Bing"")"']
}

df = pd.DataFrame(data)

# Save the DataFrame to a CSV file without adding extra double quotes
df.to_csv("output.csv", index=False, doublequote=False)

But this throws this error: File "pandas/_libs/writers.pyx", line 75, in pandas._libs.writers.write_csv_rows _csv.Error: need to escape, but no escapechar set

How can I bypass this? I need it so that the hyperlink shows in Excel as a clickable link.

Solution

  • Remove the double quotes "" from the hyperlinks:

    import pandas as pd

data = {
    "Column1": [1, 2, 3],
    "Column2": ["A", "B", "C"],
    "Formula": [
        '=HYPERLINK("https://www.yahoo.com", "See Yahoo")',
        '=HYPERLINK("https://www.google.com", "See Google")',
        '=HYPERLINK("https://www.bing.com", "See Bing")',
    ],
}

df = pd.DataFrame(data)
df.to_csv("out.csv", index=False, quotechar='"')

    Creates out.csv:

    Column1,Column2,Formula
1,A,"=HYPERLINK(""https://www.yahoo.com"", ""See Yahoo"")"
2,B,"=HYPERLINK(""https://www.google.com"", ""See Google"")"
3,C,"=HYPERLINK(""https://www.bing.com"", ""See Bing"")"

    Opening the CSV in LibreOffice (Ctrl+Click on the formula opens a webpage):

    enter image description here