Upload An Excel File
In this tutorial you will learn how to convert and import your Assets from an Excel File.
- Sample XLSX file : foodmarkets.xlsx
- Python Script : excel_to_woosmap.py
Prerequisites
To run this tutorial, you’ll need:
- Python 2.7 or greater.
- The pip package management tool.
- Access to the internet.
- A Woosmap account.
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:
$ pip install openpyxl
Once installed, you can open the workbook and read the data like this:
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:
$ pip install requests
The conversion of this flat data to a Woosmap required structure is already explained in another tutorial: CSV to Woosmap.
