CODEWITHSUNDEEP
pythonexcelnumberscopy-pasteexcel-2016
mRyan
mRyan

Reputation: 390

\t for excel pasting into new cells

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

Upvotes: 2

Views: 2971

Answers (1)

alxwrd
alxwrd

Reputation: 2470

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)

Upvotes: 6

Related Questions