Importing LOINC into a Database

This section last updated: 2024-04-18 (7 months ago)

About these scripts

These are very rudimentary scripts for importing the LOINC codes into a various databases. These scripts are very basic and should not be used in a production environment. They are provided as a starting point to show one method that can be used to load the LOINC data.

It is highly recommended that you consult with your database administrator before attempting to run these scripts or load these data.

These scripts can be used to import the Loinc.csv, MapTo.csv, and SourceOrganization.csv files into your database.

MySQL

Run the following to make the table structure needed to import the data.

MySqlDdl.sql

DROP TABLE IF EXISTS SourceOrganization;
CREATE TABLE SourceOrganization (
  id  integer default null,
  copyright_id varchar(255) not null,
  name varchar(255) default null,
  copyright text,
  terms_of_use text,
  url text default null,
  primary key (copyright_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS Loinc;
CREATE TABLE Loinc (
  loinc_num varchar(10) not null,
  component varchar(255) default null,
  property varchar(255) default null,
  time_aspct varchar(255) default null,
  `system` varchar(255) default null,
  scale_typ varchar(255) default null,
  method_typ varchar(255) default null,
  class varchar(255) default null,
  VersionLastChanged varchar(255) default null,
  chng_type varchar(255) default null,
  DefinitionDescription text,
  status varchar(255) default null,
  consumer_name varchar(255) default null,
  classtype int(11) default null,
  formula text,
  exmpl_answers text,
  survey_quest_text text,
  survey_quest_src varchar(50) default null,
  unitsrequired varchar(1) default null,
  relatednames2 text,
  shortname varchar(255) default null,
  order_obs varchar(15) default null,
  hl7_field_subfield_id varchar(50) default null,
  external_copyright_notice text,
  example_units varchar(255) default null,
  long_common_name varchar(255) default null,
  example_ucum_units varchar(255) default null,
  status_reason varchar(9) default null,
  status_text text,
  change_reason_public text,
  common_test_rank integer default null,
  common_order_rank integer default null,
  hl7_attachment_structure varchar(15) default null,
  ExternalCopyrightLink varchar(255) default null,
  PanelType varchar(50) default null,
  AskAtOrderEntry varchar(255) default null,
  AssociatedObservations varchar(255) default null,
  VersionFirstReleased varchar(255) default null,
  ValidHL7AttachmentRequest varchar(50) default null,
  DisplayName varchar(255) default null,
  
  primary key (loinc_num)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS MapTo;
CREATE TABLE MapTo (
  loinc varchar(10) NOT NULL,
  map_to varchar(10) NOT NULL,
  comment text,
  primary key (loinc, map_to)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Run the following to perform the import. The script assumes you are using the loinc schema to import the data, if not change the USE statement at the top to use the appropriate schema. Be sure to update the file paths to the correct location of the .csv files before running.

Refer to MySQL's documentation regarding the LOAD DATA statement. You may need to adjust the server's security settings or place the files in a privileged location (and omit LOCAL below) in order to the statement to work.

MySqlLoader.sql

Windows Batch Script

Provided is an basic batch script you can use to automate the execution of the above queries. It is assumed that you are working in a Windows environment and have the MySQL software installed on your PC. Follow these steps to create and run the scripts:

Note: Perform these steps in the directory that contain the .csv files to be imported.

  1. Create a file called MySqlDdl.sql and copy the code under the MySqlDdl.sql filename into that file and save it.
  2. Create a file called MySqlLoader.sql and copy the code under the MySqlLoader.sql filename into that file and save it.
    1. Be sure to update the paths to the .csv files where necessary.
  3. Create a file called LoadMySql.bat and copy the code below into that file and save it.
  4. Update the parameters in the LoadMySql.bat to the values appropriate for your environment.
  5. Run the script using command line or powershell.

LoadMySql.bat

REM
REM 
REM Please edit this script and provide values for the variables below.
REM

SET MYSQL_HOME=
SET USERNAME=
SET HOST_NAME=
SET DB_NAME=

ECHO Creating tables ... 

%MYSQL_HOME%\bin\mysql -u %USERNAME%  -h %HOST_NAME% -p %DB_NAME% < MySQLDDL.sql

ECHO Loading tables ... 

%MYSQL_HOME%\bin\mysql -u %USERNAME%  -h %HOST_NAME% -p %DB_NAME% < MySQLLoader.sql

echo
echo ----------------------------------------
echo Finished
echo ----------------------------------------

Oracle

The scripts below require the Oracle SQL*Loader utility.

Run the following to make the table structure needed to import the data.

OracleDdl.sql

CREATE TABLE Loinc
(
  LOINC_NUM                  VARCHAR2(10),
  COMPONENT                  VARCHAR2(255),
  PROPERTY                   VARCHAR2(255),
  TIME_ASPCT                 VARCHAR2(255),
  SYSTEM                     VARCHAR2(255),
  SCALE_TYP                  VARCHAR2(255),
  METHOD_TYP                 VARCHAR2(255),
  CLASS                      VARCHAR2(255),
  VersionLastChanged         VARCHAR2(255),
  CHNG_TYPE                  VARCHAR2(255),
  DEFINITIONDESCRIPTION      CLOB,
  STATUS                     VARCHAR2(255),
  CONSUMER_NAME              VARCHAR2(255),
  CLASSTYPE                  VARCHAR(2),
  FORMULA                    CLOB,
  EXMPL_ANSWERS              CLOB,
  SURVEY_QUEST_TEXT          CLOB,
  SURVEY_QUEST_SRC           VARCHAR2(50),
  UNITSREQUIRED              VARCHAR2(1),
  RELATEDNAMES2              CLOB,
  SHORTNAME                  VARCHAR2(255),
  ORDER_OBS                  VARCHAR2(15),
  HL7_FIELD_SUBFIELD_ID      VARCHAR2(50),
  EXTERNAL_COPYRIGHT_NOTICE  CLOB,
  EXAMPLE_UNITS              VARCHAR2(255),
  LONG_COMMON_NAME           VARCHAR2(255),
  EXAMPLE_UCUM_UNITS         VARCHAR2(255),
  STATUS_REASON              VARCHAR2(9),
  STATUS_TEXT                CLOB,
  CHANGE_REASON_PUBLIC       CLOB,
  COMMON_TEST_RANK           VARCHAR2(20),
  COMMON_ORDER_RANK          VARCHAR2(20),
  HL7_ATTACHMENT_STRUCTURE   VARCHAR2(15),
  EXTERNAL_COPYRIGHT_LINK    VARCHAR2(255),
  PANELTYPE                  VARCHAR2(50),
  ASKATORDERENTRY            VARCHAR2(255),
  ASSOCIATEDOBSERVATIONS     VARCHAR2(255),
  VersionFirstReleased       VARCHAR2(255),
  ValidHL7AttachmentRequest  VARCHAR2(50),
  DisplayName                VARCHAR2(255)
);

CREATE TABLE MapTo
(
  LOINC      VARCHAR2(10),
  MAP_TO     VARCHAR2(10),
  "COMMENT"  CLOB
);

CREATE TABLE SourceOrganization
(
  ID            NUMBER,
  COPYRIGHT_ID  VARCHAR2(255),
  NAME          VARCHAR2(255),
  COPYRIGHT     CLOB,
  TERMS_OF_USE  CLOB,
  URL           VARCHAR2(4000)
);

EXIT;

Run the following commands to perform the import. Be sure to update the file paths to the correct location of the .csv files before running.

Loinc.ctl

OPTIONS (errors=0, SKIP=1, SILENT=(FEEDBACK))
LOAD DATA 
CHARACTERSET UTF8  
INFILE '../Loinc.csv'  
BADFILE 'Loinc.bad'
DISCARDFILE 'Loinc.dsc'                                                           
INTO TABLE Loinc                                                                
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'                                                       
TRAILING NULLCOLS                                                               
(                                                                               
   LOINC_NUM                        CHAR NULLIF (LOINC_NUM=BLANKS)              
 , COMPONENT                        CHAR NULLIF (COMPONENT=BLANKS)              
 , PROPERTY                         CHAR NULLIF (PROPERTY=BLANKS)               
 , TIME_ASPCT                       CHAR NULLIF (TIME_ASPCT=BLANKS)             
 , SYSTEM                           CHAR NULLIF (SYSTEM=BLANKS)                 
 , SCALE_TYP                        CHAR NULLIF (SCALE_TYP=BLANKS)              
 , METHOD_TYP                       CHAR NULLIF (METHOD_TYP=BLANKS)             
 , CLASS                            CHAR NULLIF (CLASS=BLANKS)                  
 , VersionLastChanged               CHAR NULLIF (VersionLastChanged=BLANKS)                                                            
 , CHNG_TYPE                        CHAR NULLIF (CHNG_TYPE=BLANKS)              
 , DEFINITIONDESCRIPTION            CHAR(150000) NULLIF (DEFINITIONDESCRIPTION=BLANKS)                                               
 , STATUS                           CHAR NULLIF (STATUS=BLANKS)                 
 , CONSUMER_NAME                    CHAR NULLIF (CONSUMER_NAME=BLANKS)          
 , CLASSTYPE                        CHAR NULLIF (CLASSTYPE=BLANKS)           
 , FORMULA                          CHAR(150000) NULLIF (FORMULA=BLANKS)                
 , EXMPL_ANSWERS                    CHAR(150000) NULLIF (EXMPL_ANSWERS=BLANKS)                                            
 , SURVEY_QUEST_TEXT                CHAR(150000) NULLIF (SURVEY_QUEST_TEXT =BLANKS)                                             
 , SURVEY_QUEST_SRC                 CHAR NULLIF (SURVEY_QUEST_SRC=BLANKS)       
 , UNITSREQUIRED                    CHAR NULLIF (UNITSREQUIRED=BLANKS)          
 , RELATEDNAMES2                    CHAR(150000) NULLIF (RELATEDNAMES2=BLANKS)                                            
 , SHORTNAME                        CHAR NULLIF (SHORTNAME=BLANKS)              
 , ORDER_OBS                        CHAR NULLIF (ORDER_OBS=BLANKS)              
 , HL7_FIELD_SUBFIELD_ID            CHAR NULLIF (HL7_FIELD_SUBFIELD_ID=BLANKS)  
 , EXTERNAL_COPYRIGHT_NOTICE        CHAR(150000) NULLIF (EXTERNAL_COPYRIGHT_NOTICE=BLANKS)                                            
 , EXAMPLE_UNITS                    CHAR NULLIF (EXAMPLE_UNITS=BLANKS)          
 , LONG_COMMON_NAME                 CHAR NULLIF (LONG_COMMON_NAME=BLANKS)       
 , EXAMPLE_UCUM_UNITS               CHAR NULLIF (EXAMPLE_UCUM_UNITS=BLANKS)     
 , STATUS_REASON                    CHAR NULLIF (STATUS_REASON=BLANKS)          
 , STATUS_TEXT                      CHAR(150000) NULLIF (STATUS_TEXT=BLANKS)                                            
 , CHANGE_REASON_PUBLIC             CHAR(150000) NULLIF (CHANGE_REASON_PUBLIC=BLANKS)                                            
 , COMMON_TEST_RANK                 CHAR NULLIF (COMMON_TEST_RANK=BLANKS)       
 , COMMON_ORDER_RANK                CHAR NULLIF (COMMON_ORDER_RANK=BLANKS)      
 , HL7_ATTACHMENT_STRUCTURE         CHAR NULLIF (HL7_ATTACHMENT_STRUCTURE=BLANKS)    
 , EXTERNAL_COPYRIGHT_LINK          CHAR NULLIF (EXTERNAL_COPYRIGHT_LINK=BLANKS) 
 , PANELTYPE                        CHAR NULLIF (PANELTYPE=BLANKS)
 , ASKATORDERENTRY                  CHAR NULLIF (ASKATORDERENTRY=BLANKS)
 , ASSOCIATEDOBSERVATIONS           CHAR NULLIF (ASSOCIATEDOBSERVATIONS=BLANKS)
 , VersionFirstReleased             CHAR NULLIF (VersionFirstReleased=BLANKS)                                               
 , ValidHL7AttachmentRequest        CHAR NULLIF (ValidHL7AttachmentRequest=BLANKS)                                               
 , DisplayName                      CHAR NULLIF (DisplayName=BLANKS)       
)

MapTo.ctl

OPTIONS (errors=0, SKIP=1, SILENT=(FEEDBACK))
LOAD DATA 
CHARACTERSET UTF8                                                                      
INFILE '../MapTo.csv'  
BADFILE 'MapTo.bad'
DISCARDFILE 'MapTo.dsc'                                                           
INTO TABLE MapTo                                                                
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'                                                       
TRAILING NULLCOLS                                                               
(                                                                               
   LOINC          CHAR NULLIF (LOINC=BLANKS)              
 , MAP_TO         CHAR NULLIF (MAP_TO=BLANKS)              
 , "COMMENT"      CHAR(150000) NULLIF ("COMMENT"=BLANKS)               
)

SourceOrganization.ctl

OPTIONS (errors=999, SKIP=1, SILENT=(FEEDBACK))
LOAD DATA 
CHARACTERSET UTF8                                                                      
INFILE '../SourceOrganization.csv'  
BADFILE 'SourceOrganization.bad'
DISCARDFILE 'SourceOrganization.dsc'                                                           
INTO TABLE SourceOrganization                                                                
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'                                                       
TRAILING NULLCOLS                                                               
( 
   ID              INTEGER EXTERNAL
 , COPYRIGHT_ID    CHAR NULLIF (COPYRIGHT_ID=BLANKS)                                                                             
 , NAME            CHAR NULLIF (NAME=BLANKS)              
 , COPYRIGHT       CHAR(150000) NULLIF (COPYRIGHT=BLANKS)              
 , TERMS_OF_USE    CHAR(150000) NULLIF (TERMS_OF_USE=BLANKS)               
 , URL             CHAR(150000) NULLIF (URL=BLANKS)             
)

Windows Batch Script

Provided is an basic batch script you can use to automate the execution of the above queries. It is assumed that you are working in a Windows environment and have the Oracle client software installed on your PC. Follow these steps to create and run the scripts:

Note: Perform these steps in the directory that contain the .csv files to be imported.

  1. Create a file called OracleDdl.sql and copy the code under the OracleDdl.sql filename into that file and save it.
  2. Create a file called Loinc.ctl and copy the code under the Loinc.ctl filename into that file and save it.
  3. Create a file called MapTo.ctl and copy the code under the MapTo.ctl filename into that file and save it.
  4. Create a file called SourceOrganization.ctl and copy the code under the SourceOrganization.ctl filename into that file and save it.
  5. Update the paths in the .ctl files to the correct location of the .csv files where necessary.
  6. Create a file called LoadOracle.bat and copy the code below into that file and save it.
  7. Update the parameters in the LoadOracle.bat to the values appropriate for your environment.
  8. Run the script using command line or powershell.

LoadOracle.bat

REM
REM 
REM Please edit this script and provide values for the variables below.
REM
SET ORACLE_HOME=
SET USERNAME=
SET PASSWORD=
SET TNS_NAME=

ECHO Creating tables ... 
 
%ORACLE_HOME%\bin\sqlplus %USERNAME%/%PASSWORD%@%TNS_NAME% @OracleDDL.SQL > OracleDDL.LOG

ECHO Loading tables ... 

%ORACLE_HOME%\bin\sqlldr %USERNAME%/%PASSWORD%@%TNS_NAME% control="SourceOrganization.ctl"
%ORACLE_HOME%\bin\sqlldr %USERNAME%/%PASSWORD%@%TNS_NAME% control="MapTo.ctl"
%ORACLE_HOME%\bin\sqlldr %USERNAME%/%PASSWORD%@%TNS_NAME% control="Loinc.ctl"

ECHO Loading tables has completed.

PostgreSQL

Run the following to make the table structure needed to import the data.

DROP TABLE IF EXISTS sourceorganization;
CREATE TABLE sourceorganization (
  id  integer,
  copyright_id varchar(255) not null,
  name varchar(255),
  copyright text,
  terms_of_use text,
  url text,
  primary key (copyright_id)
);

DROP TABLE IF EXISTS loinc;
CREATE TABLE loinc (
  loinc_num varchar(10) not null,
  component varchar(255),
  property varchar(255),
  time_aspct varchar(255),
  system varchar(255),
  scale_typ varchar(255),
  method_typ varchar(255),
  class varchar(255),
  VersionLastChanged varchar(255),
  chng_type varchar(255),
  DefinitionDescription text,
  status varchar(255),
  consumer_name varchar(255),
  classtype integer,
  formula text,
  exmpl_answers text,
  survey_quest_text text,
  survey_quest_src varchar(50),
  unitsrequired varchar(1),
  relatednames2 text,
  shortname varchar(255),
  order_obs varchar(15),
  hl7_field_subfield_id varchar(50),
  external_copyright_notice text,
  example_units varchar(255),
  long_common_name varchar(255),
  example_ucum_units varchar(255),
  status_reason varchar(9),
  status_text text,
  change_reason_public text,
  common_test_rank integer,
  common_order_rank integer,
  hl7_attachment_structure varchar(15),
  ExternalCopyrightLink varchar(255),
  PanelType varchar(50),
  AskAtOrderEntry varchar(255),
  AssociatedObservations varchar(255),
  VersionFirstReleased varchar(255),
  ValidHL7AttachmentRequest varchar(50),
  DisplayName varchar(255),
  
  primary key (loinc_num)

);

DROP TABLE IF EXISTS mapto;
CREATE TABLE mapto (
  loinc varchar(10) NOT NULL,
  map_to varchar(10) NOT NULL,
  comment text,
  primary key (loinc, map_to)

);

Run the following to perform the import. Be sure to update the file paths to the correct location of the .csv files before running.

\copy loinc (loinc_num, component, property, time_aspct, system, scale_typ, method_typ, class, VersionLastChanged, chng_type, DefinitionDescription, status, consumer_name, classtype, formula, exmpl_answers, survey_quest_text, survey_quest_src, unitsrequired, relatednames2, shortname, order_obs, hl7_field_subfield_id, external_copyright_notice, example_units, long_common_name, example_ucum_units, status_reason, status_text, change_reason_public, common_test_rank, common_order_rank, hl7_attachment_structure, ExternalCopyrightLink, PanelType, AskAtOrderEntry, AssociatedObservations, VersionFirstReleased, ValidHL7AttachmentRequest, DisplayName ) FROM '../Loinc.csv' CSV HEADER

\copy mapto (loinc, map_to, comment) FROM '../MapTo.csv' CSV HEADER

\copy sourceorganization (id, copyright_id, name, copyright, terms_of_use, url) FROM '../SourceOrganization.csv' CSV HEADER

select count(*) from loinc;
select count(*) from mapto;
select count(*) from sourceorganization;

Access Database

LOINC is no longer published in a *.mdb (Microsoft Access) format. These instructions show you how to load the available CSV files into Access thereby creating your own Loinc.mdb file. Follow the following steps to import Loinc.csv, MapTo.csv, SourceOrganization.csv into Loinc.mdb.

  1. Create a new Loinc Access database (use the latest version installed in your system)
  2. Delete any auto-created table(s), e.g., “Table1”.
  3. Import Loinc.csv
    • Click on “External Data” from the menu.
    • Click on “New Data Source”, then “From File” then “Text File”.
      • This will open the “Get External Data” dialog box.
    • Click Browse then open Loinc.csv, e.g., [root folder of Loinc_x.xx.zip].csv.
    • In the “Specify how and where you want to store the data…”, click on “Import the source data into a new table in the current database”. Then hit OK.
      • This will open the “Import Text Wizard” dialog box.
    • From the “Import Text Wizard” dialog, choose “Delimited” and accept the default, then click Next.
    • Choose “Comma”, double quote as Text Qualifier, and click on “First Row Contains Field Names”, then hit Next.
    • From the Field Options box, set the Data Type to “Long Text” for the following fields:
      1. Component
      2. DefinitionDescription -- should be Long Text by default.
      3. Formula
      4. Exmpl_Answers
      5. Survey_Quest_Text
      6. RelatedNames2
      7. External_Copyright_Notice
      8. LongCommonName
      9. Status_Text
      10. Change_Reason_Public
      11. DisplayName
    • From the primary key screen, click “Choose my own primary key” then choose “LOINC_NUM” from the dropdown.
    • Leave “Loinc” as the “Import to Table” value then hit Finish and click Close in the “Save Import Steps” screen.
  4. Import MapTo.csv
    • Follow the first 6 bullet items from “Import Loinc.csv”, open the MapTo.csv instead.
    • From the Field Options box, set the Data Type to “Long Text” for the following fields:
      1. Comment
    • From the primary key screen, click “No primary key”.
    • Leave “MapTo” as the “Import to Table” value then hit Finish and click Close in the “Save Import Steps” screen.
  5. Import SourceOrganization.csv
    • Follow the first 6 bullet items from “Import Loinc.csv”, open the SourceOrganization.csv instead.
    • From the Field Options box, set the Data Type to “Long Text” for the following fields:
      1. Name
      2. Copyright
      3. TermsOfUse
    • From the primary key screen, click “Choose my own primary key” then choose “ID” from the dropdown.
    • Leave “SourceOrganization” as the “Import to Table” value then hit Finish and click Close in the “Save Import Steps” screen.
  6. Open the imported tables. Take note of the number of rows:
    • Loinc
    • MapTo
    • SourceOrganization
  7. Quick validation
    • There must not be any ImportErrors table.
      • Ensure the fields above are set to LongText to resolve any truncation errors.
    • Open the CSV files (Loinc.csv, MapTo.csv, SourceOrganization.csv) and compare the imported records count versus the number of lines in the CSV.
      • The count must match (excluding the header row in the CSV files)

Knowledge Base

Users’ Guide

Search Syntax

RELMA

FAQ