1

I have an excel file with 234 rows and 5 columns. I want to create an array for each column so that when I can read each column separately in xlrd. Does anyone can help please?

Improve this question
5
  • 3
    duplicate: stackoverflow.com/questions/10020413/… stackoverflow.com/questions/14931906/… Commented Jul 21, 2015 at 13:31
  • see the python stdlib csv module.. Commented Jul 21, 2015 at 13:32
  • Where does the OP say that this is CSV?? Commented Jul 21, 2015 at 13:34
  • You may be able to convert your Excel document to a Comma Separated Values document and then use the csv module which is part of Python's standard library. Commented Jul 21, 2015 at 13:35
  • No I have to use it in exactly the same manner as it is in excel but by using python I have to read it and then perform certain other operations on it like I have few customers and their data. I have to report about their missing logs manually right now but I want this to happen automatically by using python. Commented Jul 21, 2015 at 13:42

3 Answers 3

Reset to default
0

I might, but as a former user of the excellent xlrd package, I really would recommend switching to pyopenxl. Quite apart from other benefits, each worksheet has a columns attribute that is a list of columns, each column being a list of cells. (There is also a rows) attribute.

Converting your code would be relatively painless as long as there isn't too much and it's reasonably well-written. I believe I've never had do do anything other than pip install pyopenxl to add it to a virtual environment.

I observe that there's no code in your question, and it's harder (and more time-consuming) to write examples than point out required changes in code, so since you are an xlrd user I'm going to assume that you can take it from here. If you need help, edit the question and add your problem code. If you get through to what you want, submit it as an answer and mark it correct.

Suffice to say I recently wrote some code to extract Pandas datasets from UK government health statistics, and pyopenxl was amazingly helpful in my investigations and easy to use.

Since it appears from the comments this is not a new question I'll leave it at that.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

This should do it

import csv

file = csv.reader(open('file.csv', 'rb'), delimiter=",", quotechar='|')
columns = [[] for x in xrange(5)]

for row in data:
    i = 0
    for col in columns:
       col.append(row[i])
       i += 1
Improve this answer

Comments

0

See the below code, I have added comments wherever necessary.
import xlrd

filename = ''  #give the location of your filename
workRead = xlrd.open_workbook(filename)
first_sheet = workRead.sheet_by_index(0) #this will be your first sheet

print first_sheet.nrows, first_sheet.ncols #Print out the rows and cols

fields = {key: [] for key in range(first_sheet.ncols)} #create arrays based on the number of columns

for i in range(first_sheet.ncols):  #here first_sheet.ncols is 5
    for j in range(first_sheet.nrows):
        fields[i].append(first_sheet.cell_value(j, i))

        # fields[0], fields[1], fields[2], fields[3], fields[4] will be your 5 arrays consisting of all the columns value
Improve this answer

Comments