Excel - Paste Import

sometimes we just want to import Excel data by pasting it (it pastes as \n separated rows, where the fields are separated by \t

Ranges



In [1]:

    
data_string = """
1	2	3	4
11	12	13	14
21	22	23	24
31	32	33	34
41	42	43	44
51	52	53	54
"""
data_string = data_string.strip()



In [2]:

    
data = [line.split('\t') for line in data_string.split('\n')]
data









    Out[2]:





[['1', '2', '3', '4'],
 ['11', '12', '13', '14'],
 ['21', '22', '23', '24'],
 ['31', '32', '33', '34'],
 ['41', '42', '43', '44'],
 ['51', '52', '53', '54']]



In [3]:

    
data_f = [list(map(float,line.split('\t'))) for line in data_string.split('\n')]
data_f









    Out[3]:





[[1.0, 2.0, 3.0, 4.0],
 [11.0, 12.0, 13.0, 14.0],
 [21.0, 22.0, 23.0, 24.0],
 [31.0, 32.0, 33.0, 34.0],
 [41.0, 42.0, 43.0, 44.0],
 [51.0, 52.0, 53.0, 54.0]]



In [4]:

    
data_i = [list(map(int,line.split('\t'))) for line in data_string.split('\n')]
data_i









    Out[4]:





[[1, 2, 3, 4],
 [11, 12, 13, 14],
 [21, 22, 23, 24],
 [31, 32, 33, 34],
 [41, 42, 43, 44],
 [51, 52, 53, 54]]



In [5]:

    
data_i = [list(map(int, row)) for row in data]
data_i









    Out[5]:





[[1, 2, 3, 4],
 [11, 12, 13, 14],
 [21, 22, 23, 24],
 [31, 32, 33, 34],
 [41, 42, 43, 44],
 [51, 52, 53, 54]]

Export

the table below can be pasted into Excel; then apply Text to Columns... from the Data menu to expand it into a full range (must choose delimited and comma for this to work)



In [6]:

    
str0 = "\n".join(",".join(map(str, data_row)) for data_row in data_i)
print (str0)









    



1,2,3,4
11,12,13,14
21,22,23,24
31,32,33,34
41,42,43,44
51,52,53,54

Rows or Colums



In [7]:

    
data_string = """
1
11
21
31
41
51
"""
data_string = data_string.strip()



In [8]:

    
#data_string = """
#1	2	3	4
#"""
#data_string = data_string.strip()



In [9]:

    
import re
data = re.split("\t|\n", data_string)
data









    Out[9]:





['1', '11', '21', '31', '41', '51']



In [10]:

    
data_f = list(map(float,re.split("\t|\n", data_string)))
data_f









    Out[10]:





[1.0, 11.0, 21.0, 31.0, 41.0, 51.0]



In [11]:

    
data_i = list(map(int,re.split("\t|\n", data_string)))
data_i









    Out[11]:





[1, 11, 21, 31, 41, 51]

Export

the first emits rows that paste directly into Excel; for the second, like about Text to Columns... has to be used



In [12]:

    
str0 = "\n".join(map(str, data_i))
print (str0)



In [13]:

    
str0 = ",".join(map(str, data_i))
print (str0)









    



1,11,21,31,41,51