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
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: