1
$\begingroup$

Currently this is the excel file: enter image description here

How do I use python to import the file and get it to read it so that it effectively reads it as:

x, y, z, l, m, n, p, q, r = 5000, 300, 2700, 0.2, -1, 0.1, 0, 0, 0

And is it possible to read more than one line as shown in the image and create another set of coordinates?

$\endgroup$
0
1
$\begingroup$

The simplest way for this kind of data would be to convert to a .csv (comma-separated-values) file. You can do it easily in Excel with Save As > csv. Then simply access the lines with open(path).

This is my input file :

enter image description here

import bpy

for line_number, line in enumerate(open(r'C:\YourFolder\YourFile.csv')):
    columns = line.rstrip().split(';')  # Replace by a comma ',' if the separator is a comma
    if line_number == 0:  # Populate the headers
        headers = columns
    else:
        print(f'Line {line_number}:')
        for i, column in enumerate(columns):  
            print(f'{headers[i]} = {column}')

Result :

enter image description here

In your specific case, you can skip the first column and skip lines easily. Keep in mind in python and most programming languages, the first element of a list has an index of 0. There are a few ways to do it but take this for example.

The file :

enter image description here

New code :

import bpy

from mathutils import Vector

vectors = []

for line_number, line in enumerate(open(r'C:\YourFolder\YourFile.csv')):
    columns = line.rstrip().split(';')  # Replace by a comma ',' if the separator is a comma
    if line_number == 0:  # Populate the headers
        headers = columns
    elif line_number % 2 == 0:  #  Keep only odd lines (which have a pair index since it's zero-based)
        vectors.append(Vector((float(c) for c in columns[1:4])))
        print(f'Line {line_number + 1}:')
        for i, column in enumerate(columns[1::]):  # Skip the first column
            print(f'{headers[i + 1]} = {column}')

print(vectors)

Result :

enter image description here

$\endgroup$
6
  • 2
    $\begingroup$ Consider that there are hundreds of posts and pretty good answers about how to import csv data here already... no need to repeat that (IMHO): blender.stackexchange.com/search?tab=votes&q=import%20csv $\endgroup$
    – brockmann
    May 4 '20 at 11:27
  • $\begingroup$ Thanks @Gorgious, how would I be able to skip rows, eg choosing row 3 and 5 and dismissing the first column for names? Is it also possible to say x= (a specific cell in excel), y= (another specific cell) etc- I want to be able to use these values creating vectors/matrices $\endgroup$
    – newblender
    May 4 '20 at 11:44
  • 1
    $\begingroup$ @newblender I've updated my answer. If you want to access specific cells in the excel sheet, you can do it with csv but you will have less trouble with the pandas module as stated in the other answer. The csv method is more of a linear parse-and-convert approach. $\endgroup$
    – Gorgious
    May 4 '20 at 12:00
  • $\begingroup$ I seem to get an error 'line_number is not defined' what would I need to define it as? And rather than printing the values, am I able to equate x=.. and so on? $\endgroup$
    – newblender
    May 4 '20 at 13:08
  • 1
    $\begingroup$ Sorry that was a typo ! should be good now. If you want to set variables to the value of some column, you can just use for example instead of the second print statement, if i == 0: x = column. Note that I added a vectors list (which get printed at the end) which gets the x, y, and z value in each line. $\endgroup$
    – Gorgious
    May 4 '20 at 13:25
4
$\begingroup$

Alternatively, you could use the pandas library. (How to install - see here: Using 3rd party Python modules)

With pandas, you can then use the pandas.read_excel() to import your worksheet into a DataFrame, which is basically an array but with named columns to make indexing easier.

$\endgroup$
3
$\begingroup$

Personally, if it's an option, I would export as a CSV and read it in with something like https://docs.python.org/3/library/csv.html

$\endgroup$

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .