How to read excel file and use as coordinates in python code?

Question

Currently this is the excel file:

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?

Answer 1

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.

Answer 2

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

Answer 3

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 :

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 :

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 :

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 :