2

I know this is asking a lot, but I have researched quite a few ways on looking up a column or looking up a row using python within an excel document.

However, these solutions require you to import different packages, when I do not have access to them. My primary motive for this is to utilize a pre-made excel doc with shaders listed within the document and extract them based upon a brief search.

So, has anyone successfully used python to read an excel doc and, if so, have you been able to match named rows with named columns?

EDIT: Answer picked because it's the closest solution, though it doesn't necessarily answer my question. It's will, however, still provide a very reasonable solution given the context that the user, and his/her excel document, will utilize the same columns and rows to get a usable database.

TL;DR:

No easy way to compare columns to rows to get a data set without using an external package. It's easier to just extract data as an array and specify which "column" is which.

SirJames
  • 387
  • 8
  • 27
  • Could you provide a sceenshot of the excel doc? Just to see how data is formatted, in which cells etc... Then I'll give you an answer. I worked some times with Excel&Python and never found a way to natively read an Excel file without importing any package. If I had to recommend between some packages (xlwings, openpyxl and win32api), I would go with [openpyxl](https://openpyxl.readthedocs.org/en/latest/) – DrHaze Apr 16 '15 at 08:56
  • That's the deal, I cannot use other packages. A screen shot of the excel will not work, but I can make up a mock version so you understand what I'm going for - I'll have it posted later tonight. – SirJames Apr 16 '15 at 20:34
  • I've posted an excel doc for your viewing here below, under the csv module answer. – SirJames Apr 18 '15 at 00:36

4 Answers4

3

You can also read CSV documents using the built-in csv module to read CSV files, so don't need to install any external modules.

import csv
with open('names.csv') as csvfile:
     reader = csv.DictReader(csvfile)
     for row in reader:
         print(row['first_name'], row['last_name'])

If you just grab the whole contents of the reader and stuff them into a list, the lists will contain all the rows and each row will be a dictionary using the headers in the first row. Getting to a particular cell would be something like:

records = []
with open('names.csv') as csvfile:
     reader = csv.reader(csvfile)
     for row in reader:
         records.append(row)

# cel "A4" would be
records[0][3]
theodox
  • 12,028
  • 3
  • 23
  • 36
  • Yeah, I was checking that out a while back. It works really well, except that I need to cross check a few variables or get a specific set of information that varies between a few cells. – SirJames Apr 17 '15 at 00:23
  • So what's stopping you from achieving that using CSV module. Can you give a specific use case and an example data and what you are trying to achieve. – kartikg3 Apr 17 '15 at 10:17
  • http://i.imgur.com/jAynP3A.png Sorry for the lame format, but excel is excel... And this is the best I could do... quickly. M1-M5 (standing for materials) need to be cross checked with that of serial or code numbers. Since CSV module only reads rows, I have no way of being able to cross check the actual material against the "M-#'s". The idea is that I can utilize a cross checking system within Maya to pull the correct material names. I hope that this makes a little more sense... Please ask away if need-be. – SirJames Apr 18 '15 at 00:28
  • Let me reiterate, M1-M5 might not always be listed under cells H through L. Thus doing what is stated above would be a difficult task to do. – SirJames Apr 18 '15 at 00:38
  • Seems like what you'd have to do is create a file-specific lookup table. First collect all the data, then get the one that looks like the actual header row, then make a table with has header row contents as keys and their physical location as values. Then just use that lookup table to search in future rows. Or, just find that row and re-export it and subesequent to a second CSV so the real header is the first line – theodox Apr 18 '15 at 18:54
1

I save my excel files as a .csv file, and import the data with numpy.genfromtxt and use the delimiter=',' argument to parse the test file.

For example:

import numpy
data = numpy.genfromtxt("my_file.csv", delimiter=",")
# do stuff with data
Matthew
  • 672
  • 4
  • 11
0

the module xlrd is already in the python package and really easy to use :

import xlrd
xlsFile = xlrd.open_workbook(path)
sheetName = xlsFile.sheets()[0].name

for s in xlsFile.sheets():
    for row in range(s.nrows):
        print s.cell(row, 1)
DrWeeny
  • 2,487
  • 1
  • 14
  • 17
-1

1. Installation

Maya uses Python version 2.7.11 on all supported platforms. The standalone Python shell for Maya is named mayapy.exe on Windows and is located at D:\Program Files\Autodesk\Maya2017\bin\mayapy.exe. Put it to the system environment path.

If pip.exe is not found, go to https://pip.pypa.io/en/stable/installing/ and download get-pip.py.

From a console:

C:\Users\JM-FF>python get-pip.py

After success installed pip, the pip.exe is at D:\Program Files\Autodesk\Maya2017\Python\Scripts. Put this to the system environment path.

Install XlsxWriter:

C:\Users\JM-FF>pip install XlsxWriter

2. step

Now open your Maya in ScriptEditor, write that:


import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(11,3,"hello")
workbook.close()

Now you can read and write data to Excel from Maya using Python.

And the hello.xlsx is at D:\Program Files\Autodesk\Maya2017\bin.

Damien Flament
  • 1,465
  • 15
  • 27
jiayi fang
  • 1