How to save in *.xlsx long URL in cell using Pandas

Question

For example I read excel file into DataFrame with 2 columns(id and URL). URLs in input file are like text(without hyperlinks):

input_f = pd.read_excel("input.xlsx")

Watch what inside this DataFrame - everything was successfully read, all URLs are ok in input_f. After that when I wan't to save this file to_excel

input_f.to_excel("output.xlsx", index=False)

I got warning.

Path\worksheet.py:836: UserWarning: Ignoring URL 'http:// here long URL' with link or location/anchor > 255 characters since it exceeds Excel's limit for URLS force_unicode(url))

And in output.xlsx cells with long URL were empty, and URLs become hyperlinks.

How to fix this?

Answer 1

You can create an ExcelWriter object with the option not to convert strings to urls:

writer = pandas.ExcelWriter(
    r'file.xlsx', 
    engine='xlsxwriter',
    options={'strings_to_urls': False}
)
df.to_excel(writer)
writer.close()

In more recent versions of Pandas, you need to write

writer = pd.ExcelWriter(
   r'file.xlsx', 
   engine='xlsxwriter',
   engine_kwargs={'options': {'strings_to_urls': False}}
 )