Importing Reports

Hiran 16/08/2018 – Independent Age ‘Tracker’ report.

Overview

The report maybe

  • CSV – the first row is the header, ideally – usually it is. We expect to find properly formatted data.
  • Excel report … may be formatted with merged cells, wrap text etc. Not starting at Row 1. Potentially with headers on 2+ rows. Potentially grouped into groups with merged-cell headers. May have blank columns, usually hidden. Leading zeros may have dropped off. There may be padding.

In either case, the report might change. Fieldnames unlikely to change as DBAs are not known to change Fieldnames, but new ones may be added, the column position on the report may change.
In Excel reports, the headers may not (usually don’t) be same as the field names. Being headers, descriptions, they may change.

Governance and advanced training: In some companies the version of Excel may mean Power Query needs to be installed (pre-Excel 2016). This may not be an option as it may be outside the remit of the users, or the IT department who may be reluctant to go outside the standard install of the organisation. In any case, an import solution that does not require advanced technical knowledge may be required.

Example: ‘Tracker.xls’

In Tracker.xls we have the following to deal with:

  • A big range of merged cells across the entire length of headers, just above the headers. So, CurrentRegion returns a rubbish range.
  • An image, a logo. This is not a problem.
  • Some merged pairs of columns.
  • A hidden empty column.
  • Some headers have words in brackets
Why not Get and Transform (PowerQuery)?

Get and Transform will suffice in most cases.

However, in the real world there will be instances where things change and the process needs to be modified. Often, as in cases where columns in the report may change, or some key columns may be absent, there is a need to validate the import on each import. Otherwise we may be importing data into the wrong columns, thus corrupting our data store.

The Tool

Plan 1 – generate a Schema sheet. From this we generate

[SCHEMA SHEET IMAGE]

  • a CREATE TABLE script into a text file. This can be run in the Query Window in Access.
  • an INSERT script. This can be pasted into the PutData procedure. The script will point to the correct columns in Tracker.xls.

Plan 2 – (for later dev) generate a schema for the import. This can be a Text file. For easier debugging and process-tracking we can open the schema into a sheet, and run the import directed by this.

[SCHEMA TEXT FILE IMAGE]

[IMPORT SCHEMA SHEET IMAGE]