(PYTHON) to_excel - Ignoring URL... 255 characters since it exceeds Excel's limit for URLS

Question

I am using the following code to copy a dataframe into an Excel document:

dfPol.to_excel(writer, 'Influence on Policy', columns=colsPol)

The problem is that I am getting the following error as the URLs are too long:

Ignoring URL .... 255 characters since it exceeds Excel's limit for URLS

So, I found a solution which is to use:

dfPol.to_excel(writer, 'Influence on Policy', columns=colsPol, options={'strings_to_urls': False})

But I am getting an error of:

TypeError: to_excel() got an unexpected keyword argument 'options'

As obviously options does not work with to_excel, which is what I am using for a lot of other code in my script (i.e. I want to stick with 'to_excel')

Are there any solutions to my problem?

This is not a duplicate of: How to save in *.xlsx long URL in cell using Pandas as that is not about to_excel (it is about excelwriter)

Possible duplicate of [How to save in \*.xlsx long URL in cell using Pandas](https://stackoverflow.com/questions/35440528/how-to-save-in-xlsx-long-url-in-cell-using-pandas) — Mykola Shchetinin, Oct 15 '17 at 14:49
options is an argument to a writer, not to a `to_excel` function — Mykola Shchetinin, Oct 15 '17 at 14:50
with a function? I am not sure how to even begin to do that for this particular task — Nicholas, Oct 15 '17 at 14:50
Thank you Mykola Shchetinin, so are you saying it is not possible with to_excel? — Nicholas, Oct 15 '17 at 14:52
writer = pd.ExcelWriter('C:/Users/{}/Desktop/RF Data.xlsx'.format(staName2)) — Nicholas, Oct 15 '17 at 14:54
So, pass the `options={'strings_to_urls': False}` to the `pd.ExcelWriter` call — Mykola Shchetinin, Oct 15 '17 at 14:55
Perfect, thank you Mykola. If you want to give a formal answer, I will accept your answer :) — Nicholas, Oct 15 '17 at 14:57
It actually IS a duplicate question to that other link, because using an Excelwriter was not what the other question was about, but the solution to the problem. — RufusVS, Sep 12 '18 at 18:32

score 16 · Accepted Answer · answered Oct 15 '17 at 15:00

16

Pass the argument options={'strings_to_urls': False} to the pd.ExcelWriter call when creating your writer. It will look something like this:

writer = pd.ExcelWriter('C:/Users/{}/Desktop/RF Data.xlsx'.format(staName2),options={'strings_to_urls': False})
dfPol.to_excel(writer, 'Influence on Policy', columns=colsPol)

answered Oct 15 '17 at 15:00

Mykola Shchetinin

737
6
16

3

The code above didn't work for me until I included `writer.close()`. – alpenmilch411 Jul 15 '19 at 11:05

score 5 · Answer 2 · answered Mar 06 '20 at 22:07

5

you can just use:

 with pd.ExcelWriter(file_name, options={'strings_to_urls': False}) as writer:
        df.to_excel(writer, 'Influence on Policy', columns=colsPol)

Then you don't have to worry about closing your writer file

answered Mar 06 '20 at 22:07

turtle_in_mind

986
1
18
36

(PYTHON) to_excel - Ignoring URL... 255 characters since it exceeds Excel's limit for URLS

2 Answers2