<a id="import-scripts-reading-a-file"></a>
<a id="import-script-reading-files"></a>
# Import Scripts: Reading a file

An [Import Script](../import_scripts.html.md#organization-import-scripts) 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.

<a id="reading-an-excel-file"></a>
<a id="import-script-excel"></a>
## Reading an Excel file

Start by looking at what the importer provides:

```python
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:

```python
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)`.

<a id="columns-you-do-not-know-in-advance"></a>
### 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:

```python
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`.

<a id="sheets-that-are-not-tables"></a>
### 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:

```python
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:

```python
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.

<a id="keys-in-one-column-and-values-in-another"></a>
#### 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:

```python
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.

<a id="how-far-the-content-goes"></a>
#### 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:

```python
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.

<a id="cells-holding-a-formula"></a>
#### 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.

<a id="merged-cells"></a>
#### 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.

<a id="counting-rows-two-ways"></a>
#### 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.

<a id="columns-that-might-not-be-there"></a>
### 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:

```python
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"
> ```

<a id="checking-before-you-read"></a>
### 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:

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

<a id="skipping-a-row-rather-than-stopping"></a>
### 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:

```python
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}")
```

<a id="reading-a-json-or-yaml-file"></a>
<a id="import-script-document"></a>
## 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:

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

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

<a id="walking-down"></a>
### Walking down

`root` behaves like python nested dictionaries and lists:

```python
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
```

<a id="accessing-dictionary-and-list-like-data"></a>
### 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:

```python
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:

```python
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
```

<a id="when-the-document-is-not-what-you-expected"></a>
### 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].
```