Supporting Modules

Created on Sep 7, 2011

@author: vieglais

Import a Excel worksheet as tables in an SQLite database.

class utils.xls2sql.Xls2Sql(db=None)

Utility class that will import worksheets from an Excel workbook as tables in an SQL database.

coerceType(val, typ)

Try and coerce the supplied value to the indicated type.

Parameters:

• val – The value to coerce
• typ – The target type

createTables(meta)

Create a new table in the database using the column names contained in meta.

meta is structured like:

meta = {name: {'start_row':
               'columns': [{'name': column name, 
                            'id': zero offset from left, 
                            'type': sqlite type}, 
                           ...],
              },
      }

getBookMeta(workbook, meta_row=0)

Load a meta structure from the workbook.

Parameters:

• workbook – An openpyxl workbook object.
• meta_row – Which row to read metadata (column names) from.

getSheetMeta(worksheet, meta_row=0)

Attempt to read column names for sheet from the specified row.

Parameters:

• worksheet – Worksheet being examined
• meta_row – Row of worksheet to be looked at.

guessColumnType(row=1, col=0)

Place holder for code that will guess the type of the column. This is not really necessary for SQLite databases, as it figures stuff out on the fly.

load(workbook, meta=None)

Load the supplied workbook into the database that was provided with the constructor.

Parameters:

• workbook – Path to an Excel file or an openpyxl workbook object
• meta – Optional metadata structure to use instead of auto generated.