How to import an xls file to Python?

Asked

Viewed 11,102 times

7

I’m starting my studies in Python and would like to import a database that is on .xls for Python. Someone could help me?

NOTE: Python version 3.4.3.

python python-3.x
  • 1

    I suggest you take a look at the package xlrd. Some time ago another user of the site asked a number of questions on this subject, if you take a look at them you will find some examples of code.

2 answers

4


You will need external libraries like the xlrd to do this, take a look at www.python-excel.org

In any case I suggest saving the file as csv from excel and using the python csv module, will save you a lot of time and headache

Example I copied in the face of the website officer:

import csv
with open('eggs.csv', 'wb') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                        quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

See more examples here.

1

A real example using xlrd:

import xlrd

def xls_parser(arquivo):
    tabela = xlrd.open_workbook(arquivo).sheet_by_index(0)
    qtd_linhas = tabela.nrows
    linhas = []
    for i in range(1, qtd_linhas):
        linhas.append(
            {
                'linha': i + 1,
                'valor_coluna_1': tabela.row(i)[0].value,
                'valor_coluna_2': tabela.row(i)[1].value,
            }
        )
    return linhas

In the above example, the function takes the file path and positions the cursor at the beginning of the file. From this search the total of lines of the file.

Having the total of lines, makes a for using the range and transfers this information to a list of dictionaries, facilitating manipulation.

The script also stores in dictionaries the line in which the XLS content was removed. But you can take that part out if that information isn’t relevant.

Browser other questions tagged python python-3.x

You are not signed in. Login or sign up in order to post.