to_clipboard wrong linebreaks on Windows

[Winand]

Copying dataframe with to_clipboard and Qt gives different results: when pasted to Excel there's one more empty line. It may be unexpected and overwrite important data.

When looking into clipboard with Free Clipboard Viewer we can see that Qt uses CR+LF linebreaks:

and pandas uses LF:

Maybe this is the source of problem.

(This isn't fixed by #9693)

[jreback]

you might need to specifier the separator, this is using read_csv under the hood. Pls investigate what would make this work.

[Winand]

This helps:

pd.DataFrame(["Hello world"]).to_clipboard(line_terminator="\r\n")

P.S. you mean to_csv? i think \r\n should be default terminator, cause it concerns Excel=True argument

[jreback]

sorry, meant to_csv. excel=True already implies sep='\t', so not sure what you think should change here.

[Winand]

What? I do not understand.) It also implies line_terminator to be '\r\n', cause we're going to paste data in Excel. But it is '\n' by default.

[jreback]

@Winand sorry, was distracted a bit.....yes it is whatever the default line_terminator is in to_csv, which is \n. This works with excel now, so are you suggesting this should default (on windows) to \n\r?

[Winand]

@jreback i mean:
if we say Excel=True, we assume that data in clipboard may be pasted into Excel. BUT. If line terminators are '\n' (by default) an unwanted empty line is pasted. So i suggest to set line_terminator to '\r\n' by default on Windows.

[jreback]

but this DOES work on windows. can you give a copy-pastable example where this does not work?

I am not sure how QT interacts with things in any event.

[Winand]

Right now i have pandas 0.16.2 patched with #9693 (works the same as 0.17.0 with #9693)

import pandas as pd
pd.DataFrame(["Привет, мир"]).to_clipboard()

Ctrl+V in Excel:

Empty line pasted, u see? And now with \r\n:

pd.DataFrame(["Привет, мир"]).to_clipboard(line_terminator='\r\n')

Ctrl+V in Excel:

[jreback]

I see that. Hmm, must have always been like that.

@sinhrks @TomAugspurger ever seen this? does it matter?

[TomAugspurger]

Haven't tried to_clipboard -> excel before, but just tested it on windows and I see the extra line.

Not sure what if anything this could break if we change the default. Presumably people on Windows would expect the default to be \r\n?

[sinhrks]

Confirmed the issue on my Windows also. And changing line sep sounds valid. I think we can use os.linesep (\r\n on windows) as default?

[jreback]

this is actually tricky because say you do to_clipboard, we don't know which platform its going to be pasted, though I suppose we can assume that its the SAME as the current platform.

Though I suppose non-windows platforms can handle the os.linesep correctly. can someone test this out?

e.g.

do these produce differing results on non-windows? (these work for me on macosx)

for term in ['\n','\r\n']:
    df.to_clipboard(line_terminator=term)

so is soln just to default line_terminator='\r\n'?

[jreback]

anyone want to do a PR for this?

[Winand]

here it is #12131

[jreback]

4a1a330
updated the vendored pyperclip. does this resolve?