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