Reading data in excel in python

Question

I basically have a text file with data as:

Neighbor        V    State/Pf
10.230.2.91     4        1178
10.229.5.239    4        1177
10.229.6.239    4        1173

I want to read the values under neighbor column and put in excel. How can I achieve this? I am new to python, so please suggest me ways of doing this.

Try looking at some other examples: http://stackoverflow.com/questions/13437727/python-write-to-excel-spreadsheet — Sean Wang, Aug 12 '16 at 18:54
@SeanWang I tried but in my case the entire data is a part of one list element, so I can't figure out a way for it. — Anjali, Aug 12 '16 at 18:56
Yeah temp[0] was a long string, but I have pdated the question now. Probably I was going in the wrong direction. — Anjali, Aug 12 '16 at 19:22

ChE · Answer 1 · 2016-08-12T19:01:51.757

0

Pandas has methods to transform data frames into Excel files (pd.DataFrame.to_excel), but I agree with bernie: It is not very clear how your data look like

UPDATE

So it would look like

l = list(["Neighbor","10.230.2.91","10.229.5.239","10.229.6.239"])
df = pd.DataFrame(l[1:],columns=[l[0]])
df.to_excel("data.xls","data") # 2nd argument is the sheet name

You need to install the xlwt module which is not installed per default with Pandas

edited Aug 12 '16 at 19:01

answered Aug 12 '16 at 18:56

ChE

88
7

the data is in a list called temp. The temp[0] contains the data I want to put in excel. – Anjali Aug 12 '16 at 18:58

Sean Wang · Answer 2 · 2016-08-12T19:23:18.263

0

You'll want something simple that can output to a excel file.

You'll want to split your line into separate lines and only extract the parts that you want. In your case the .split() method would be very helpful. .split() takes your string and splits it based on the delimiter given (default is space). For example string.split('\n') will split the string by lines.

Something like this should be what you want:

import xlwt

book = xlwt.Workbook(encoding="utf-8")

sheet1 = book.add_sheet("Sheet 1")

temp[0].split('\n')

i = 0
for dataLine in temp[0]:
    sheet1.write(i, 0, dataLine.split()[0])
    i += 1

book.save("excel.xls")

Note: If you do not want the Neighbors heading you can skip that line and start i at 1.

edited Aug 12 '16 at 19:23

answered Aug 12 '16 at 18:57

Sean Wang

778
5
14

@Anjali Oh so it is a space delimited file. And you only want the IP address looking 'Neighbor' column? You'll want to do some sort of loop for every third space delimited element or something to that effect. Also you may want to edit your question with that information. – Sean Wang Aug 12 '16 at 19:14
@Anjali Updated my answer to reflect your changes. You need to process your long string to get the parts that you want. – Sean Wang Aug 12 '16 at 19:25
Its still showing the same result. Is there any way I can read Neighbor column in array and then put in excel? – Anjali Aug 12 '16 at 19:43

Reading data in excel in python

2 Answers2