Upload An Excel File

  1. Prerequisites
  2. Excel Spreadsheets
  3. Woosmap Data API

In this tutorial you will learn how to convert and import your Assets from an Excel File.

Prerequisites

To run this tutorial, you’ll need:

Excel Spreadsheets

There are many python packages available to work with Excel files that will run on any Python platform and that do not require either Windows or Excel to be used. They are fast, reliable and open source. For this tutorial, we’re using Openpyxl. It’s the recommended package for reading and writing Excel 2010 files (ie: .xlsx/.xlsm/.xltx/.xltm files). This library does not support the old .xls file format. You can use use xlrd, or convert it to the more recent .xlsx file format.

To install it using pip, simply run this command in your terminal of choice:

Shell
        $ pip install openpyxl

    

Once installed, you can open the workbook and read the data like this:

Python
        from openpyxl import load_workbook

class ExcelFile:
    """A simple wrapper around the needed openpyxl functions for this script"""

    def __init__(self, filename, worksheet_name=''):
        self.filename = filename
        self.workbook = load_workbook(self.filename)
        self.worksheet_name = worksheet_name if worksheet_name else self.get_first_worksheet_name()
        self.worksheet = self.workbook.get_sheet_by_name(self.worksheet_name)

    def get_first_worksheet_name(self):
        return self.workbook.get_sheet_names()[0]

    def iter_rows(self):
        for row in self.worksheet.iter_rows():
            yield [cell.value for cell in row]

def main():
    INPUT_EXCEL_FILE = 'foodmarkets.xlsx'
    excel_file = ExcelFile(INPUT_EXCEL_FILE)
    sheet_data = list(excel_file.iter_rows())
    header = sheet_data.pop(0)
    assets_as_dict = [dict(zip(header, item)) for item in sheet_data]

    

We preferred to iterate over the data rows using a list of Dict that looks like: [{'field1': 'valueA'; 'field2':'valueB'},{'field1': 'valueC'; 'field2':'valueD'}]. It takes advantage of the header row with the column names and gives you the opportunity to grab it cell value by its name.

Woosmap Data API

The Woosmap Data API is a RESTful API with endpoint https://api.woosmap.com/stores. It takes a mandatory parameter : your private key. Therefore the url you call should look like this: htps://api.woosmap.com/stores?private_key=YOUR_PRIVATE_KEY.

We use Requests to call the Woosmap API. Install it using pip:

Shell
        $ pip install requests

    

The conversion of this flat data to a Woosmap required structure is already explained in another tutorial: CSV to Woosmap.

Related help articles