UCRex

Owned by Paul Macey (admin)
Last updated: Oct 03, 2018
2 min read

MariaDB

Available from mariadb.org, choose the Windows 64 msi download.

Set up as service, use a root password, otherwise defaults.

Download the java connector.

GUI: dbForge Studio

This tool can import the UCRex csv files without problem (Database => Import Data).

All tables

Set the separator to be a comma. Ideally, text is in quotes.

DEMOGRAPHICS => patient

The STUDY_ID field is the key, so edit the type to set to primary key. However, it is in hex format. The type should be changed to VARCHAR with length 32. The default type is BLOB, but this can't be used as a primary key (see why).

DIAGNOSES => diagnosis

Set the STUDY_ID field to not allow nulls, and to type VARCHAR length 32:

ENCOUNTER_ID to BIGINT.

ICD_CODE should be set to a string: VARCHAR, length 32 should be fine.

ICD_DESC should be set to 255 length; it may default to 150, which is not long enough.

ENCOUNTERS => encounter

Comma as separator

Set STUDY_ID to VARCHAR length 32, do not allow nulls.

ENCOUNTER_ID to BIGINT, do not allow nulls.

PROCEDURES => procedures (procedure is a reserved word)

First, add a header for "PROC_CODE" as the second-to-last field:

"STUDY_ID","ENCOUNTER_ID","PROC_DATE","PROC_TYPE","PROC_CODE","PROC_NAME"

Comma as separator

Set STUDY_ID to VARCHAR length 32, do not allow nulls.

ENCOUNTER_ID to BIGINT, do not allow nulls.

Access (obsolete for larger datasets)

Access has a fatal flaw of a 2GB limit, which when combined with other problems means it's not usable.

Importing into Access

The raw csv format tables can be imported as "Text File" (External Data tab).

Load PATIENT_ID as Short Text, since Long Integer is too short, and double has problems (at least 10 selected PATIENT_ID's would not match when used to select using the same ID). 

Demographics

Import this as a table Patients. Let Access create a primary key and rename that to SubjectID.

Diagnoses

Set ENCOUNTER_ID to Short Text.

Encounters:

set PAT_ENC_CSN_ID to double

For Date, change name to DATE, and make sure it's month/day/year format ("Advanced" button at bottom-left.)

Vitals

STUDY_ID: VARCHAR(32), deselect allow nulls

ENCOUNTER_ID: BIGINT, deselect allow nulls

Change 3 types VARCHAR(50) to VARCHAR(255)