Article
Muhammad Waseem · Jan 10 4m read

12 Medical Datasets along with 43 tables dynamically created by using all-new LOAD DATA SQL functionality

 

Application to import 12 Datasets along with 43 tables dynamically by using  LOAD DATA command which loads data from a source into an IRIS SQL table. 
List of Datasets

Features

  • Dynamically creation of table based on the CSV file data.
  • Import dataset by using LOAD DATA functionality by Terminal or by web application.
  • Remove dataset programmatically by terminal or by Web Application.
  • Dynamically view Imported Data from Web Application.
  • Functionality to Import data into CSV, Excel or PDF Format.
  • Responsive web application to view the status of Dataset.
  • Any or All datasets can be install or Remove with single command.
  • Use of %SQL_Diag.Result and %SQL_Diag.Message tables with LOAD DATA

It is recommended to read related documentations LOAD DATA (SQL).

 

How to install or remove any Dataset from Terminal

Use below command to import particular Dataset by passing it's ID or pass 999 to import all DataSets

do ##class(dc.data.medical.utility).ImportDS(1)

Below is the main script to create table dynamically and load data by using LOAD DATA functionality. Please note that table is created dynamically 

//Get file name 
SET filename=tRS.Get("Name")
//Remove .csv from the file name
SET tableName=$REPLACE("dc_data_"_ds_"."_tRS.Get("ItemName"),".csv","") 
//Get columns based on the header row of csv file
Do ##class(dc.data.medical.utility).GetColTypes(filename,.coltype) 
//Dynamically create table based on tablename and column types
SET qry = "CREATE TABLE "_tableName_" ("_coltype_")"
SET rset = ##class(%SQL.Statement).%ExecDirect(,qry)
//Check if table created successfully
IF rset.%SQLCODE
{
   WRITE "ERROR : ",rset.%Message,!    
}
ELSE
{
  //Dynamically construct LOAD DATA statement
  SET qry = "LOAD DATA FROM FILE  '"_filename_"' INTO "_tableName_ " "_"USING {""from"":{""file"":{""header"":""1""}}}"
  SET rset = ##class(%SQL.Statement).%ExecDirect(,qry)
  // Check result set sqlcode, In case of error write resultset message
  IF rset.%SQLCODE
  {
   WRITE "ERROR Table : " _tableName_" IMPORT FAILED: ",rset.%Message,!
  }
  ELSE
  {
  WRITE "SUCCESS table : " _tableName_" created and "_rset.%ROWCOUNT_" Rows Imported Successfully",!
  }
}

Use below command to remove particular Dataset by passing it's ID or pass 999 to remove all DataSets
 

do ##class(dc.data.medical.utility).RemoveDS(1)

 

Below is the main script to remove table dynamically

//Get file name
SET filename=tRS.Get("Name")
//Remove .csv from file name
SET tableName=$REPLACE("dc_data_"_ds_"."_tRS.Get("ItemName"),".csv","")
//Drop table
SET qry = "DROP TABLE "_tableName
SET rset = ##class(%SQL.Statement).%ExecDirect(,qry)
//Check if table deleted successfully
IF rset.%SQLCODE
{
  WRITE "ERROR : ",rset.%Message,!          
}
ELSE
{
  WRITE "Table "_tableName_" deleted successfully",!
}

 

How to install or remove any Dataset from Web Application

Navigate to http://localhost:52773/csp/datasets/index.csp  application dashboard

If Dataset is not installed then install DataSet button will be visible and if Dataset is installed then Remove Dataset button will be visible.

Just press the desire button to install or remove any data set

 

How to view and export data from Dataset

Navigate to http://localhost:52773/csp/datasets/datasets.csp  View DataSets page.

Select particular dataset and then table from the list. Press Excel, CSV, PDF file button to export the data.

 

Thanks

1
0 179
Discussion (2)1
Log in or sign up to continue