Import Scripts: Reading a file

An Import Script reads a file by declaring a file setting for it, and the person running the script chooses the file. The examples on this page read a spreadsheet setting declared as form_list and a document setting declared as study_config; use the names you gave yours.

What the file offers depends on the kind of setting it is: an Excel File setting has sheets, and a JSON File or YAML File setting has a document.

Reaching for the wrong one stops the run and tells you which kind of file you have, so a script written for the wrong kind says so on the first line that reads the file rather than part way through.

Reading an Excel file 

Start by looking at what the importer provides:

importer.log(f"The file is called {importer.settings['form_list'].name}")
importer.log(f"It has these sheets: {', '.join(importer.settings['form_list'].sheet_names)}")

Pick a sheet by name, then walk its rows:

sheet = importer.settings['form_list'].sheet("Forms")
importer.log(f"{sheet.name} has {len(sheet)} rows under {', '.join(sheet.columns)}")

for row in sheet.rows():
    importer.log(f"row {row.number}: {row['Form OID']} is called {row['Form Name']}")

For a sheet with Form OID and Form Name columns and two rows of data, that logs:

Forms has 2 rows under Form OID, Form Name
row 2: DM is called Demographics
row 3: AE is called Adverse Events

Some things worth knowing about that:

The first row is the column headings, so the first row of data is row 2. row.number is the row number as you would see it in Excel, which is what makes it useful in a message.
len(sheet) counts the rows of data, not the heading.
Every value is text, even when the cell looked like a number or a date. If you need a number, convert it yourself with int(...) or float(...).
If your headings are not on the first row, say so: importer.settings['form_list'].sheet("Forms", header_row=3).

Columns you do not know in advance 

sheet.columns is the headings in sheet order, and row.items() gives each heading with this row's value under it:

sheet = importer.settings['form_list'].sheet("Forms")
importer.log(f"columns: {', '.join(sheet.columns)}")

for row in sheet.rows():
    for heading, value in row.items():
        importer.log(f"{heading} = {value}")

which logs:

columns: Form OID, Form Name, Notes
Form OID = DM
Form Name = Demographics
Notes = core

importer.settings['form_list'].sheet_names does the same one level up, for a workbook whose sheets you do not know. row.values() gives the values on their own, in the same order as sheet.columns.

Sheets that are not tables 

Everything above treats a sheet as a table: a row of headings, then data under it. Plenty of spreadsheets are not tables. A study identifier might sit on its own in B2, above everything else. A sheet of settings might be keys down column A and values down column B, with no headings at all.

For those, read the sheet by cell instead of by row. sheet.cells is the same worksheet read as values at addresses:

sheet = importer.settings['form_list'].sheet("Study", header_row=5)

importer.log(f"Study {sheet.cells.at('B2')}, version {sheet.cells.at('B3')}")

which logs:

Study ACME-001, version 2.5

B2 and B3 are above the headings, so the rows never reach them. sheet.cells reads the whole sheet, whatever header_row you chose.

There are two ways to name a cell, because there are two ways you arrive at one:

sheet.cells.at("B2") takes the address you would see in Excel. Use it when you know where the value lives, which is usually because someone told you or a specification says so. Lower case and the $ of an absolute reference both work, so at("b2") and at("$B$2") are the same cell.
sheet.cells.get(row=2, column=2) takes numbers, both starting at 1. Use it when you are working the numbers out as you go, in a loop. You have to name them, because get(2, 2) would not say which was which.

A cell that is empty reads as "", and so does a cell outside the sheet. Reading Z99 of a small sheet is not an error, and neither is get(row=0, column=1), so you do not have to know where a sheet stops before you look. That is what lets you compare a row with the one above it without a special case for the first one.

If a sheet is not a table at all, you can go straight to its cells without naming a header row:

cells = importer.settings['form_list'].cells("Study")
importer.log(cells.at("B2"))

Use that for a sheet too wide or too irregular to read as rows. importer.settings['form_list'].sheet(...) reads the same cells, so either way in works.

Keys in one column and values in another

If a sheet has no headings at all, say so with header_row=None. Then no row is taken for headings and every row is data:

settings = {}

for row in importer.settings['form_list'].sheet("Settings", header_row=None).rows():
    settings[row.values()[0]] = row.values()[1]

importer.log(f"version is {settings['version']}, site is {settings['site']}")

which, for a sheet holding version and 2.5 on the first row and site and London on the second, logs:

version is 2.5, site is London

Without header_row=None that sheet loses its first setting, because version and 2.5 would be read as the names of the two columns.

How far the content goes

sheet.cells.last_row and sheet.cells.last_column are the last row and column holding anything, so a loop can stop where the content does:

cells = importer.settings['form_list'].cells("Sites")

for number in range(1, cells.last_row + 1):
    if not cells.get(row=number, column=2):
        importer.log(f"{cells.column_letter(2)}{number} has no contact")

For a Sites sheet holding a site name in column A on every row and a contact in column B on all but the second, that logs:

B2 has no contact

cells.column_letter(2) turns the column number back into the letter you see in Excel, so the message names a cell the reader can go and find. Reporting "row 2, column 2" would leave them counting columns.

Cells holding a formula

A script reads the result a formula last worked out, not the formula itself. Excel stores that result in the file alongside the formula, so for a file that has been opened and saved in Excel you get the number you would see on screen.

A file that was produced by a program and never opened in Excel may have no result stored. There is nothing to read in that case, so the cell reads as empty, the same as a blank one. If a value you can see in Excel comes back empty, open the file in Excel, save it, and try again.

Merged cells

A merged block holds its value in its top left cell and nothing in the rest. That is how the file stores it, not something a script chooses, so a heading merged across three columns gives one column name and not three, and reading the other two by address gives "".

What happens next depends on how wide the merge is, and neither outcome is what you would guess.

A heading merged across two columns leaves one heading with no name. The sheet still reads as a table, but the second column's values sit under that nameless heading, so asking for the merged heading by name gives you only the first of them. For Form, Visit merged across two columns, then Notes, the columns read as:

('Form', 'Visit', '', 'Notes')

and row["Visit"] gives the value from the first of the two merged columns only. The other is reachable by position, through row.values(), or by cell.

A heading merged across three or more columns, or two merged headings in one row, leaves more than one nameless heading. Those are treated as a repeated heading, and the run stops with:

Sheet 'Forms' has more than one column called ''.

That message is about the blanks the merge left behind, not about a heading you typed twice.

Read a sheet with merged headings by cell, using importer.settings['form_list'].cells("Forms"), and take the values from the addresses you can see in Excel.

Counting rows, two ways

Two numbers on the same sheet answer different questions, and it is worth keeping them apart:

len(sheet) is how many rows of data sit under the headings.
sheet.cells.last_row is how far down the sheet anything goes at all, headings and everything above them included.

For a sheet with a title on row 1, headings on row 3 and two rows of data, those are 2 and 5. For the Forms sheet above, with headings on row 1 and two rows of data, they are 2 and 3.

Columns that might not be there 

row["Form OID"] stops the run if there is no such column, and tells you which columns the sheet does have. That is usually what you want: a missing column means the file is not the one the script was written for.

When a column is genuinely optional, ask first:

sheet = importer.settings['form_list'].sheet("Forms")

if not sheet.has_column("Notes"):
    importer.warn("This file has no Notes column, so notes will be left empty.")

for row in sheet.rows():
    importer.log(f"{row['Form OID']}: {row.get('Notes', 'no note')}")

Warning

row.get("Notes", "no note") gives you "no note" only when the sheet has no Notes column at all. A row whose Notes cell is simply empty gives you an empty string, because the column is there. To treat an empty cell as missing, test the value:

note = row.get("Notes") or "no note"

Checking before you read 

The same applies to sheets. importer.settings['form_list'].sheet("Fields") stops the run if there is no such sheet, so check when it is optional:

if not importer.settings['form_list'].has_sheet("Fields"):
    importer.error("This file has no Fields sheet.")

Skipping a row rather than stopping 

Real files have gaps. A blank row is usually worth a warning and a continue rather than stopping everything:

for row in importer.settings['form_list'].sheet("Forms").rows():
    oid = row["Form OID"].strip()
    if not oid:
        importer.warn(f"row {row.number} has no Form OID, so it was skipped")
        continue

    importer.log(f"would create {oid}")

Reading a JSON or YAML file 

A JSON or YAML File setting does not have sheets. It has a document, and it starts reading at importer.settings['study_config'].root.

The examples below use this file. The two are the same document written two ways, and a script reads either of them identically:

study: ONC-2026
forms:
  - oid: DM
    name: Demographics
    fields:
      - oid: BRTHDTC
        label: Date of Birth
  - oid: AE
    name: Adverse Events

{
  "study": "ONC-2026",
  "forms": [
    {"oid": "DM", "name": "Demographics",
     "fields": [{"oid": "BRTHDTC", "label": "Date of Birth"}]},
    {"oid": "AE", "name": "Adverse Events"}
  ]
}

Walking down 

root behaves like python nested dictionaries and lists:

root = importer.settings['study_config'].root
importer.log(f"study {root['study']}")

for form in root["forms"]:
    importer.log(f"{form['oid']} is called {form['name']}")

which logs:

study ONC-2026
DM is called Demographics
AE is called Adverse Events

Accessing dictionary and list-like data 

This works	and does what you expect
`node["oid"]`, `node[0]`	Mappings by key, lists by position
`"forms" in root`	Membership, against keys for a mapping
`len(node)`	Number of keys, or length of the list
`for key in root`	Keys, like a dict
`for item in root["forms"]`	Items, like a list
`list(root)`	`['study', 'forms']`
`root.keys()`, `root.items()`	As on a dict, though they return a tuple and an iterator rather than views
`root.get("notes")`, `root.get("notes", "none")`	`None` or your default when the key is absent

There are some key differences:

There is no values().
A missing key stops the run rather than raising KeyError for you to catch. Use in or get when a key is optional. has(key) is a synonym for in if you prefer it spelled out.
A leaf is the plain value, not a node. root["forms"][0]["oid"] is the string "DM", so .upper() works on it and .keys() does not. Only mappings and lists come back as nodes, and those are the only things worth asking questions of.
A leaf keeps the type the file gives it. true is True, 3 is the number 3 and an unquoted 2024-01-15 in a YAML file is a date, not text. In a YAML file, a value with a leading zero such as 0123, a time such as 10:30, a code such as 53E09, and words such as Yes and No stay text, exactly as written.
You can pass a number or a date to a command as it is. A number property takes a number as a number, a true/false or date property refuses one, and anything else stores it as text, so a name given as 22.0 is 22. True and False are taken only by a true/false property or column.
Every node answers the same questions, not just root. root["forms"][0].keys() is ('oid', 'name', 'fields').

items() is how you walk a mapping whose keys you do not know:

for key, value in importer.settings['study_config'].root["forms"][0].items():
    importer.log(f"{key} = {value}")

which logs:

oid = DM
name = Demographics
fields = root.forms[0].fields

Note the last line. Printing a branch gives you its position in the document rather than its contents, which is what you want in a log message and is not what you want if you meant the contents. Read into it, or use len(), to say something about what it holds.

An optional branch reads the way you would expect:

for form in importer.settings['study_config'].root["forms"]:
    if "fields" not in form:
        importer.warn(f"{form['oid']} lists no fields")
        continue

    for field in form["fields"]:
        importer.log(f"{form['oid']}.{field['oid']}")

which logs:

DM.BRTHDTC
AE lists no fields

When the document is not what you expected 

Reading something that is not there stops the run and says what was there instead, naming the place in the document so you can go and look:

root has no key 'nope'. Its keys are: study, forms.
root.forms is a list, so it is read by position, not by 'first'.
root.forms has 2 entries, so there is no [9].