def doImport(self, params): conn = sqlite3.connect(params.filename) cursor = conn.cursor() query = params.field_results.get('query') cursor.execute(query) # We can only iterate through the cursor once (standard forward-only # semantics) so here we convert the query result to a list for further # processing data = [row for row in cursor] # Transpose the data from a list of row-tuples to a list of columns data = [ [row[i] for row in data] for i, col in enumerate(cursor.description)] # Figure out the dataset name and type for each column names = sanitize_names(col[0] for col in cursor.description) classes = [ ImportDatasetText if any(isinstance(value, basestring) for value in column) else ImportDataset1D for column in data] result = [] for (name, cls, column) in zip(names, classes, data): if cls is ImportDataset1D: result.append(ImportDataset1D( name, data=[ # Import NULL values as NaN in numeric datasets float('NaN') if value is None else float(value) for value in column])) else: result.append(ImportDatasetText( name, data=[ # Import NULL values as blank strings in text datasets '' if value is None else value for value in column])) return result
def doImport(self, params): workbook = xlrd.open_workbook(params.filename) sheet = params.field_results.get('sheet', '') if not sheet: sheet = '0' if sheet.isdigit(): # If the name is entirely numeric, treat it as a zero-based sheet # index instead of a name sheet = workbook.sheet_by_index(int(sheet)) else: sheet = workbook.sheet_by_name(sheet) ref = params.field_results.get('range', '') if not ref: ref = used_range(sheet) (first_row, first_col), (last_row, last_col) = parse_range(ref) # Clamp the selected range to the used cells on the sheet last_row = max(sheet.nrows - 1, last_row) last_col = max(sheet.ncols - 1, last_col) if params.field_results.get('direction', 'Columns') == 'Columns': if params.field_results.get('header', False): names = [ unicode(cell.value) for cell in sheet.row(first_row)[first_col:last_col + 1]] first_row += 1 else: names = [ 'col{}'.format(xlrd.colname(i)) for i in range(first_col, last_col + 1)] data = [ [cell for cell in sheet.col(col)[first_row:last_row + 1]] for col in range(first_col, last_col + 1)] else: if params.field_results.get('header', False): names = [ unicode(cell.value) for cell in sheet.col(first_col)[first_row:last_row + 1]] first_col += 1 else: names = [ 'row{}'.format(i) for i in range(first_row, last_row + 1)] data = [ [cell for cell in sheet.row(row)[first_col:last_col + 1]] for row in range(first_row, last_row + 1)] names = sanitize_names(names) classes = [ ImportDatasetText if any(cell.ctype == xlrd.XL_CELL_TEXT for cell in col) else ImportDataset1D for col in data] result = [] for (name, cls, column) in zip(names, classes, data): if cls is ImportDataset1D: result.append(ImportDataset1D( name, data=[ # Import non-numeric cells as NaN float(cell.value) if cell.ctype == xlrd.XL_CELL_NUMBER else float('NaN') for cell in column])) else: result.append(ImportDatasetText( name, data=[ cell.value if cell.ctype == xlrd.XL_CELL_TEXT else '' for cell in column])) return result