Importing Delimited Device Files into a MySQL Database using a MySQL Load Data Script

Importing Delimited Device Files into a MySQL Database using a MySQL Load Data Script

The Pipe-Delimited | device files provide a relational view of the device record data. These files can be imported into a database for easier reading and interaction.

Notice

Be aware of the file size and row limitations of the software you are attempting to import the files into. For information on limitations, look at the software's official documentation

Directions

The following directions are for the MySQL CMD Line tool, but similar actions can be taken in other SQL tools and database types.
  1. Extract the Import Scripts to your workarea.
  2. Place your Delimited Device Files into the data directory.
  3. Update and uncomment the runner script with the appropriate parameters for your environment.
    • If you are using a Linux computer to run these scripts, update the mysql/mysql_populate_ag_tables.sh file.
    • If you are using a Windows computer to run these scripts, update the mysql/mysql_populate_ag_tables.bat file.
    The variables that need to be updated are as follows:
    Parameter Description
    MYSQL_EXECUTABLE The location of the mysql cmd line executable (ex: ~/MySQL/bin/mysql)
    user The user name to use to connect to your database schema
    password The password to use to connect to your database schema
    dbserver The IP or url of the server
    db_name The name of your database schema
  4. Execute the runner script
    • If you are using a Linux computer, run the mysql/mysql_populate_ag_tables.sh file.
    • If you are using a Windows computer, run the mysql/mysql_populate_ag_tables.bat file.
  5. Check the mysql_ag.log file generated in the mysql directory for a generated report.

Download Import Scripts

You can download the Import Scripts here. The following files can be found in the download:
Directory/File Name Description
oracle/populate_ag_tables.sh Shell Script runner for all ctl and sql files in a Linux Environment.
Note: You must update the parameters ORACLE_HOME, user, password, and tns_name to connect to your database and use your SQL Loader and SQL Plus applications
oracle/populate_ag_tables.bat Batch runner for all ctl and sql files in a Windows Environment.
Note: You must update the parameters ORACLE_HOME, user, password, and tns_name to connect to your database and use your SQL Loader and SQL Plus applications
oracle/ag_build_tables.sql Drops and Creates new tables that the ctl scripts will populate
oracle/ag_device_sizes.ctl Control File for the Delimited Device File data/deviceSizes.txt
Populates the AG_DEVICE_SIZES table
oracle/ag_contacts.ctl Control File for the Delimited Device File data/contacts.txt
Populates the AG_CONTACTS table
oracle/ag_sterilization_methods.ctl Control File for the Delimited Device File data/sterilizationMethodTypes.txt
Populates the AG_STERILIZATION_METHODS table
oracle/ag_identifiers.ctl Control File for the Delimited Device File data/identifiers.txt
Populates the AG_IDENTIFIERS table
oracle/ag_product_codes.ctl Control File for the Delimited Device File data/productCodes.txt
Populates the AG_PRODUCT_CODES table
oracle/ag_gmdn_terms.ctl Control File for the Delimited Device File data/gmdnTerms.txt
Populates the AG_GMDN_TERMS table
oracle/ag_environmental_conditions.ctl Control File for the Delimited Device File data/environmentalConditions.txt
Populates the AG_ENVIRONMENTAL_CONDITIONS table
oracle/ag_device.ctl Control File for the Delimited Device File data/device.txt
Populates the AG_DEVICE table
data/* This directory will contain the Delimited Device Files you will process.
bad/* This directory will contain the .bad files that get generated any time a bad record is read during processing
discard/* This directory will contain the .dsc files that get generated any time record is discarded during processing