2

Basic question I want to paste large lists into excel however they all paste into the same cell how can I get them to paste into different cells. As an example the website https://www.doogal.co.uk/BatchGeocoding.php with the settings:

Tabs(for direct pasting into Excel) on

input addresses off

UK easting and northing off

with the view text

This allows the numbers to be put into different cells. How can I recreate this in python so I can copy the output and paste into the excel sheet. I have tried putting 4 spaces between the output and adding \t between them. e.g 52.660869 1.26202 and 52.660869 \t 1.26202 but they paste into the same cell.

I want this output to directly paste into excel 52.522229, -1.448947, 'vZR6L', 'GTS', 'Owner', 'london', '0', 'x', Like the website does

I have tried

52.522229    -1.448947    vZR6L    GTS    Owner    london    0    x
52.522229 \t -1.448947 \t vZR6L \t GTS \t Owner \t london \t 0 \t x
52.522229\t-1.448947\tvZR6L\tGTS\tOwner\tlondon\t0\tx
mRyan
  • 332
  • 4
  • 18
  • is \t not being recognized as columns delimiter when I paste into Excel 2016? – mRyan May 09 '17 at 15:34
  • what's the relation with python? don't paste into excel, create a tab separated file using csv module and open the file using excel, that'll work. – Jean-François Fabre May 09 '17 at 15:36
  • i just have python running a bunch of tests on the numbers and it outputs the result. I want the outputted text to be pasted directly into excel. – mRyan May 09 '17 at 15:39
  • 1
    Are you sure your tabs aren't getting converted to spaces by the actual thing you're pasting them from? Because if they are, Excel won't recognize that as a column separator. – Shanded May 09 '17 at 15:44
  • it looks like it is being converted to spaces that's why i was trying to use \t instead to divide up the list – mRyan May 09 '17 at 15:54
  • Then all you have to do is change the setting in the outputting thing to not convert tabs to spaces. Or paste it into editor that allows it and change it. But again, it would be better to just output it to a tab separated file instead of manually pasting like the other commenter said. – Shanded May 09 '17 at 15:57

1 Answers1

6

I've done some research and as far as I can tell it is not possible to achieve what you want from a command line. The problem is even though you are specifying \t in your code, the command line is outputting tabs as spaces.

>>>my_string = "THIS\tIS\tA\tTEST"
>>> print(my_string)
THIS....IS......A.......TEST

Where in this example .'s are spaces. Excel cannot parse this.

Option 1

I am assuming you are on windows. If so you could pipe the output into the Windows clip program.

>>>import os
>>>my_string = "THIS\tIS\tA\tTEST"
>>>os.system("echo {} | clip".format(my_string))
0

This will copy the string to your clipboard. In my testing this works: I don't know how well it will for you.

For other operating systems see: Pipe to/from the clipboard

Option 2

Alternatively you could write the output to a file, where the TAB characters will actually be saved:

with open("results.txt") as f:
    f.write(my_string)

But at this point, you might as well comma separate the values and save it as a .csv:

my_string = "THIS, IS, A, TEST"
with open("results.csv") as f:
    f.write(my_string)
Community
  • 1
  • 1
alxwrd
  • 2,320
  • 16
  • 28
  • @Shanded I'm sorry it turned out like that. I wrote this independant of the comments. – alxwrd May 09 '17 at 16:18
  • 1
    Option 3: install the [pyperclip](https://pypi.org/project/pyperclip/) library to copy the formatted string directly to the clipboard. Then you can paste it into Excel/Google Sheets and it will work as you expect. – sql_knievel Jan 22 '21 at 18:49