CODEWITHSUNDEEP
pythonexcelpython-2.7
Dominik Bayer
Dominik Bayer

Reputation: 5

Import Excel file in to Python as a list

I want to import one column with 10 rows in to Python as a list.

So I have in excel for example: One, Two, Three, Four,..., Ten Everything written in column A over row 1-10.

Now I want to import these cells into Python, so that my result is:

list = ['One', 'Two', 'Three', 'Four', ..., 'Ten']

Since I am a total noob in programming, I have no clue how to do it. So please tell me the most easiest way. All tutorials I have found, did't got me the result I want. Thank you

I am using Python 2.7

Upvotes: 0

Views: 22124

Answers (3)

Jojo
Jojo

Reputation: 447

I recommend installing pandas.

pip install pandas

and

import pandas
df = pandas.read_excel('path/to/data.xlsx') # The options of that method are quite neat; Stores to a pandas.DataFrame object
print df.head() # show a preview of the loaded data
idx_of_column = 5-1 # in case the column of interest is the 5th in Excel
print list(df.iloc[:,idx_of_column]) # access via index
print list(df.loc[['my_row_1','my_row_2'],['my_column_1','my_column_2']]) # access certain elements via row and column names
print list(df['my_column_1']) # straight forward access via column name

(checkout pandas doc) or 

pip install xlrd

code

from xlrd import open_workbook
wb = open_workbook('simple.xls')
for s in wb.sheets():
  print 'Sheet:',s.name
  for row in range(s.nrows):
    values = []
    for col in range(s.ncols):
       values.append(s.cell(row,col).value)
    print ','.join(values)

(example from https://github.com/python-excel/tutorial/raw/master/python-excel.pdf)

Upvotes: 0

John Y
John Y

Reputation: 14519

Even though pandas is a great library, for your simple task you can just use xlrd:

import xlrd

wb = xlrd.open_workbook(path_to_my_workbook)
ws = wb.sheet_by_index(0)
mylist = ws.col_values(0)

Note that list is not a good name for a variable in Python, because that is the name of a built-in function.

Upvotes: 4

user3908027
user3908027

Reputation: 36

I am unsure if your data is in xlsx form or CSV form. If XLSX, use this Python Excel tutorial. If CSV, it is much easier, and you can follow the code snippet below. If you don't want to use pandas, you can use the numpylibrary. Use the example code snippet below for taking the top row of a CSV file:

import numpy as np
csv_file = np.genfromtxt('filepath/relative/to/your/script.csv', 
                          delimiter=',', dtype=str)
top_row = csv_file[:].tolist()

This will work for a file that has only one column of text. If you have more columns, use the following snippet to just get the first column. The '0' indicates the first column.

top_row = csv_file[:,0].tolist()

Upvotes: 1

Related Questions