Using PostgreSQL to Massage Data for Siebel EIM

Recently, I had a Siebel Clinical Trial Management System (CTMS) client requirement to load data into the database, clearly the purview of Siebel Enterprise Integration Manager (EIM). The client sent me the data in a spreadsheet, and it looked something like this:

In order to use SQL Loader to get the data into the EIM tables (in an Oracle database), I needed to massage the data into a form that looks like this:

But, how do I do this? Enter open source PostgreSQL 10!

The first step is to save the Excel file as a CSV file.

The second step is issuing this statement in PostgreSQL:

CREATE FOREIGN TABLE aft_staging

(site_number text,

Contact_id text,

Accelerate Clinical Trials With AI and Real-Time Data Collaboration

Discover a unified clinical data review and collaboration solution that optimizes the integration and review of complex, ever-changing clinical data.

Get the Guide

Role text)

Server pglog

OPTIONS (filename ‘/Users/reneschmidt/inputfile.csv’, format ‘csv’, header ‘true’);

This allows me to issue SQL statements from PostgreSQL using data that is an external file. So, for example, I can issue this command; select * from customer_staging:

The next step is to use the super handy unnext function to transform the data:

select contact_id,person_role,ltrim(unnest(string_to_array(site_number, ‘,’))) site from customer_staging;

The final step is to export the data. I did it by issuing this command:

\copy (select contact_id,person_role,ltrim(unnest(string_to_array(site_number, ‘,’))) site from customer_staging) TO ‘/Users/reneschmidt/outputfile.csv’ WITH (FORMAT CSV);

Opening the file shows this: