Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser
Created October 24, 2019 10:14
Show Gist options
  • Save BirgittaHauser/53bf0ca980e44daeb764bd8b828be2d2 to your computer and use it in GitHub Desktop.
Save BirgittaHauser/53bf0ca980e44daeb764bd8b828be2d2 to your computer and use it in GitHub Desktop.
Read *.Csv file and split the content into rows and columns directly with SQL (on Db2 for i)
-- Read *.csv file located within the IFS directly with SQL
----------------------------------------------------------------------------------
-- Birgitta Hauser - 2019-10-25
-- 1. *.csv file located in the IFS: /home/Hauser/Employee1.csv
-- with the following content (including the column description)
-- "EMPLOYEENO","NAME","FIRSTNAME","ADDRESS","ZIPCODE","CITY","COUNTRY"
-- 10,"Meier und Sohn","","Industriestr. 3-13","80333","Muenchen","DE"
-- 20,"Bauer","Herrmann","Wald-und-Wiesen-Weg. 4","63128","Dietzenbach","DE"
-- 40,"Hauser","Birgitta","Koenigsteiner Allee 59","63128","Dietzenbach","DE"
-- 60,"Lehmann","Maria","Schwarzwaldstr. 26","77880","Sasbach","DE"
-- 50,"Burger","Emil","Nelkenweg 21","86916","Kaufering","DE"
-- 80,"Miller","Katrin","Am Lech 35","86916","Kaufering","DE"
-- Line break is performed on each row with CRLF (Hex-Value x'0D25')
-- Columns are separated by a comma
-- character values are embedded in double quotes
-- 2. Access on the ifs file with the GET_CLOB_FROM_FILE scalar function (must be performed under commitment control!)
-- Address Information
Values(Get_Clob_From_File('/home/Hauser/Employee1.csv'));
-- 3. Read the *.csv file
-- 3.1. with GET_CLOB_FROM_FILE
-- 3.2. Split the content of the *.csv file into rows (at CRLF)
-- with the SPLIT User defined table function (UDTF) located within the SYSTOOLS library
-- 3.3. Split the rows of the *.csv file into columns (at Comma)
-- and remove the leading and trailing double quotes
-- 3.4. accululate the split values the row level
With x as (-- Split the *.Csv file into rows
Select Ordinal_Position as RowKey, Element as RowInfo
from Table(SysTools.Split(Get_Clob_From_File('/home/Hauser/Employee1.csv'), x'0D25')) a
Where Trim(Element) > ''),
y as (-- Split the rows into columns
Select x.*, Ordinal_Position ColKey,
Trim(B '"' from Element) as ColInfo
from x cross join Table(SysTools.Split(RowInfo, ',')) a)
-- Accumulate the row and column values into rows and ignore the column labels
Select RowKey,
Min(Case When ColKey = 1 Then ColInfo End) EmployeeNo,
Min(Case When ColKey = 2 Then ColInfo End) Name,
Min(Case When ColKey = 3 Then ColInfo End) FirstName,
Min(Case When ColKey = 4 Then ColInfo End) Address,
Min(Case When ColKey = 5 Then ColInfo End) Country,
Min(Case When ColKey = 6 Then ColInfo End) ZipCode,
Min(Case When ColKey = 7 Then ColInfo End) City
From y
Where RowKey > 1
Group By RowKey;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment