Read *.csv File directly with SQL

ReadCsv.SQL

-- Read *csv File from IFS
With x as (-- Split IFS File into Rows (at CRLF)
           Select Ordinal_Position as RowKey, Element as RowInfo
             from Table(SysTools.Split(Get_Clob_From_File('/home/Hauser/Employee.csv'), x'0D25')) a
             Where Trim(Element) > ''),
     y as (-- Split IFS File Rows into Columns (and remove leading/trailing double quotes ")
           Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
             from x cross join Table(SysTools.Split(RowInfo, ',')) a)
-- Return the Result as Table
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 -- Remove header
Group By RowKey;       

and concised! thanks Birgitta for sharing.

It runs relatively slow, because Large Objetcts LOBs are generated. According to the IBM documentation you may and even should retrieve the source code for SPLIT and create a function within your own library. If you generate a new function, you may create an additional function where you convert the LOBs into VARCHAR fields … and use the new function, which then should be faster.

NickLitten just raise your hand, the excuses will line up to find you :)
Every shop has tons of spreadsheets, and we programmers incessantly run the venerable cpyfrmimpf to satisfy the business needs.
I would point out the power of SQL that the tool makes applicable to the analysis of raw user data plus the ability to implement a configurable field mapping and casting as the main reasons to consider this tool.

These statements run in ACS's Run SQL will generate a table function offering an easy to use generic interface to run Birgitta's `code.

CREATE FUNCTION PARSECSV (
@path CHAR(256) , @startwith INTEGER )
RETURNS TABLE (
ROWKEY INTEGER ,
A CHAR(50) ,
B CHAR(50) ,
C CHAR(50) ,
D CHAR(50) ,
E CHAR(50) ,
F CHAR(50) ,
G CHAR(50) ,
H CHAR(50) ,
I CHAR(50) ,
J CHAR(50) ,
K CHAR(50) ,
L CHAR(50) ,
M CHAR(50) ,
N CHAR(50) ,
O CHAR(50) ,
P CHAR(50) ,
Q CHAR(50) ,
R CHAR(50) ,
S CHAR(50) ,
T CHAR(50) ,
U CHAR(50) ,
V CHAR(50) ,
W CHAR(50) ,
X CHAR(50) ,
Y CHAR(50) ,
Z CHAR(50) ,
AA CHAR(50) ,
AB CHAR(50) ,
AC CHAR(50) ,
AD CHAR(50) ,
AE CHAR(50) ,
AF CHAR(50) ,
AG CHAR(50) ,
AH CHAR(50) ,
AI CHAR(50) ,
AJ CHAR(50) ,
AK CHAR(50) ,
AL CHAR(50) ,
AM CHAR(50) ,
AN CHAR(50) ,
AO CHAR(50) ,
AP CHAR(50) ,
AQ CHAR(50) ,
AR CHAR(50) ,
"AS" CHAR(50) ,
"AT" CHAR(50) ,
AU CHAR(50) ,
AV CHAR(50) ,
AW CHAR(50) ,
AX CHAR(50) ,
AY CHAR(50) ,
AZ CHAR(50) ,
BA CHAR(50) ,
BB CHAR(50) ,
BC CHAR(50) ,
BD CHAR(50) ,
BE CHAR(50) ,
BF CHAR(50) ,
BG CHAR(50) ,
BH CHAR(50) ,
BI CHAR(50) ,
BJ CHAR(50) ,
BK CHAR(50) ,
BL CHAR(50) ,
BM CHAR(50) ,
BN CHAR(50) ,
BO CHAR(50) )
LANGUAGE SQL
SPECIFIC PARSECSV
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *CS ,
DECRESULT = (31, 31, 00) ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN
WITH X AS ( -- Split IFS File into Rows (at CRLF)
SELECT ORDINAL_POSITION AS ROWKEY , ELEMENT AS ROWINFO
FROM TABLE ( SYSTOOLS . SPLIT ( GET_CLOB_FROM_FILE ( trim(@path) ) , X'0D25' ) ) A
WHERE TRIM ( ELEMENT ) > '' )
,
-- Split IFS File Rows into Columns (and remove leading/trailing double quotes ")
Y AS (
SELECT X . * , ORDINAL_POSITION COLKEY , TRIM ( B '"' FROM ELEMENT ) AS COLINFO
FROM X CROSS
JOIN TABLE ( SYSTOOLS . SPLIT ( ROWINFO , ',' ) ) A
-- ;
)
SELECT ROWKEY , MIN ( CASE WHEN COLKEY = 1 THEN COLINFO END ) A , MIN ( CASE WHEN COLKEY = 2
THEN COLINFO END ) B , MIN ( CASE WHEN COLKEY = 3 THEN COLINFO END ) C , MIN ( CASE
WHEN COLKEY = 4 THEN COLINFO END ) D , MIN ( CASE WHEN COLKEY = 5 THEN COLINFO END )
E , MIN ( CASE WHEN COLKEY = 6 THEN COLINFO END ) F , MIN ( CASE WHEN COLKEY = 7 THEN
COLINFO END ) G , MIN ( CASE WHEN COLKEY = 8 THEN COLINFO END ) H , MIN ( CASE WHEN
COLKEY = 9 THEN COLINFO END ) I , MIN ( CASE WHEN COLKEY = 10 THEN COLINFO END ) J ,
MIN ( CASE WHEN COLKEY = 11 THEN COLINFO END ) K , MIN ( CASE WHEN COLKEY = 12 THEN
COLINFO END ) L , MIN ( CASE WHEN COLKEY = 13 THEN COLINFO END ) M , MIN ( CASE WHEN
COLKEY = 14 THEN COLINFO END ) N , MIN ( CASE WHEN COLKEY = 15 THEN COLINFO END ) O ,
MIN ( CASE WHEN COLKEY = 16 THEN COLINFO END ) P , MIN ( CASE WHEN COLKEY = 17 THEN
COLINFO END ) Q , MIN ( CASE WHEN COLKEY = 18 THEN COLINFO END ) R , MIN ( CASE WHEN
COLKEY = 19 THEN COLINFO END ) S , MIN ( CASE WHEN COLKEY = 20 THEN COLINFO END ) T ,
MIN ( CASE WHEN COLKEY = 21 THEN COLINFO END ) U , MIN ( CASE WHEN COLKEY = 22 THEN
COLINFO END ) V , MIN ( CASE WHEN COLKEY = 23 THEN COLINFO END ) W , MIN ( CASE WHEN
COLKEY = 24 THEN COLINFO END ) X , MIN ( CASE WHEN COLKEY = 25 THEN COLINFO END )
Y , MIN ( CASE WHEN COLKEY = 26 THEN COLINFO END ) Z , MIN ( CASE WHEN COLKEY
= 27 THEN COLINFO END ) AA , MIN ( CASE WHEN COLKEY = 28 THEN COLINFO END )
AB , MIN ( CASE WHEN COLKEY = 29 THEN COLINFO END ) AC , MIN ( CASE WHEN COLKEY = 30
THEN COLINFO END ) AD , MIN ( CASE WHEN COLKEY = 31 THEN COLINFO END ) AE , MIN ( CASE
WHEN COLKEY = 32 THEN COLINFO END ) AF , MIN ( CASE WHEN COLKEY = 33 THEN COLINFO END )
AG , MIN ( CASE WHEN COLKEY = 34 THEN COLINFO END ) AH , MIN ( CASE WHEN COLKEY = 35
THEN COLINFO END ) AI , MIN ( CASE WHEN COLKEY = 36 THEN COLINFO END ) AJ , MIN ( CASE
WHEN COLKEY = 37 THEN COLINFO END ) AK , MIN ( CASE WHEN COLKEY = 38 THEN COLINFO END )
AL , MIN ( CASE WHEN COLKEY = 39 THEN COLINFO END ) AM , MIN ( CASE WHEN COLKEY = 40
THEN COLINFO END ) AN , MIN ( CASE WHEN COLKEY = 41 THEN COLINFO END ) AO , MIN ( CASE
WHEN COLKEY = 42 THEN COLINFO END ) AP , MIN ( CASE WHEN COLKEY = 43 THEN COLINFO
END ) AQ , MIN ( CASE WHEN COLKEY = 44 THEN COLINFO END ) AR , MIN ( CASE WHEN
COLKEY = 45 THEN COLINFO END ) AS "AS", MIN ( CASE WHEN COLKEY = 46 THEN COLINFO
END ) AT , MIN ( CASE WHEN COLKEY = 47 THEN COLINFO END ) AU , MIN ( CASE WHEN COLKEY
= 48 THEN COLINFO END ) AV , MIN ( CASE WHEN COLKEY = 49 THEN COLINFO END ) AW ,
MIN ( CASE WHEN COLKEY = 50 THEN COLINFO END ) AX , MIN ( CASE WHEN COLKEY = 51 THEN
COLINFO END ) AY , MIN ( CASE WHEN COLKEY = 52 THEN COLINFO END ) AZ ,
MIN ( CASE WHEN COLKEY = 53 THEN COLINFO END ) BA , MIN ( CASE WHEN COLKEY = 54 THEN
COLINFO END ) BB , MIN ( CASE WHEN COLKEY = 55 THEN COLINFO END ) BC , MIN ( CASE
WHEN COLKEY = 56 THEN COLINFO END ) BD , MIN ( CASE WHEN COLKEY = 57 THEN COLINFO
END ) BE , MIN ( CASE WHEN COLKEY = 58 THEN COLINFO END ) BF , MIN ( CASE WHEN
COLKEY = 59 THEN COLINFO END ) BG , MIN ( CASE WHEN COLKEY = 60 THEN COLINFO END )
BH , MIN ( CASE WHEN COLKEY = 61 THEN COLINFO END ) BI , MIN ( CASE WHEN
COLKEY = 62 THEN COLINFO END ) BJ , MIN ( CASE WHEN COLKEY = 63 THEN COLINFO END )
BK , MIN ( CASE WHEN COLKEY = 64 THEN COLINFO END ) BL , MIN ( CASE WHEN COLKEY
= 65 THEN COLINFO END ) BM , MIN ( CASE WHEN COLKEY = 66 THEN COLINFO END ) BN ,
MIN ( CASE WHEN COLKEY = 67 THEN COLINFO END ) BO
FROM Y
WHERE ROWKEY > (@startwith - 1) -- Remove header if necessary
GROUP BY ROWKEY
ORDER BY ROWKEY
;

LABEL ON SPECIFIC FUNCTION PARSECSV
IS 'CSV Parser' ;

--Grant permissions as necessary
--GRANT ALTER , EXECUTE
--ON SPECIFIC FUNCTION PARSECSV
--TO WITH GRANT OPTION ;

For example, the function could be run as

select * from table(parsecsv('/somefolder/somefile.csv',1)) t;

Or, if you don't want to have the header row returned, substitute the second parameter with a 2.

Just as an asside, I’d define the columns/output fields as VarChar instead of Char. I'd also add an addtional parameter for passing the Separator. In some cases a semi colon or other separators may be used. I slighly modified the User Defined Table Function, i.e. 1. Replaced the @ signes with Par 2. I added an additional parameter (ParSeparator), so the Separator can be passed 3. I added default values to the ParStartWithRow and ParSeparator, so the parameters can be explicitly passed or not. If not passed the default value is taken. The UDTF can now be called as follows: Select * from Table(YourSchema.ParseCsv('/YourDir1/YourDir2/YourIFSFile.csv')) x; select * from Table(YourSchema.parseCsv('/YourDir1/YourDir2/YourIFSFile.csv', ',')) x; select * from Table(YourSchema.parseCsv('/YourDir1/YourDir2/YourIFSFile.csv', '', 2)) x; select * from Table(YourSchema.parseCsv('/YourDir1/YourDir2/YourIFSFile.csv', ParStartWithRow => 2)) x; 4. I converted the Character Parameters and Character Output columns into VarChar Data Types. I also changed the maximum length of the output parameters to 256. 5. I renamed the output column names and enhanced it to 100 output columns. (As an asside an UDTF can return up to 8,000 columns) Here the modified SQL Script: Create or Replace Function PARSECSV (ParIFSFile VarChar(256) , ParSeparator VarChar(1) Default ',' , ParStartWithRow Integer Default 1 ) Returns Table (RowKey Integer , Col01 VarChar(256) , Col02 VarChar(256) , Col03 VarChar(256) , Col04 VarChar(256) , Col05 VarChar(256) , Col06 VarChar(256) , Col07 VarChar(256) , Col08 VarChar(256) , Col09 VarChar(256) , Col10 VarChar(256) , Col11 VarChar(256) , Col12 VarChar(256) , Col13 VarChar(256) , Col14 VarChar(256) , Col15 VarChar(256) , Col16 VarChar(256) , Col17 VarChar(256) , Col18 VarChar(256) , Col19 VarChar(256) , Col20 VarChar(256) , Col21 VarChar(256) , Col22 VarChar(256) , Col23 VarChar(256) , Col24 VarChar(256) , Col25 VarChar(256) , Col26 VarChar(256) , Col27 VarChar(256) , Col28 VarChar(256) , Col29 VarChar(256) , Col30 VarChar(256) , Col31 VarChar(256) , Col32 VarChar(256) , Col33 VarChar(256) , Col34 VarChar(256) , Col35 VarChar(256) , Col36 VarChar(256) , Col37 VarChar(256) , Col38 VarChar(256) , Col39 VarChar(256) , Col40 VarChar(256) , Col41 VarChar(256) , Col42 VarChar(256) , Col43 VarChar(256) , Col44 VarChar(256) , Col45 VarChar(256) , Col46 VarChar(256) , Col47 VarChar(256) , Col48 VarChar(256) , Col49 VarChar(256) , Col50 VarChar(256) , Col51 VarChar(256) , Col52 VarChar(256) , Col53 VarChar(256) , Col54 VarChar(256) , Col55 VarChar(256) , Col56 VarChar(256) , Col57 VarChar(256) , Col58 VarChar(256) , Col59 VarChar(256) , Col60 VarChar(256) , Col61 VarChar(256) , Col62 VarChar(256) , Col63 VarChar(256) , Col64 VarChar(256) , Col65 VarChar(256) , Col66 VarChar(256) , Col67 VarChar(256) , Col68 VarChar(256) , Col69 VarChar(256) , Col70 VarChar(256) , Col71 VarChar(256) , Col72 VarChar(256) , Col73 VarChar(256) , Col74 VarChar(256) , Col75 VarChar(256) , Col76 VarChar(256) , Col77 VarChar(256) , Col78 VarChar(256) , Col79 VarChar(256) , Col80 VarChar(256) , Col81 VarChar(256) , Col82 VarChar(256) , Col83 VarChar(256) , Col84 VarChar(256) , Col85 VarChar(256) , Col86 VarChar(256) , Col87 VarChar(256) , Col88 VarChar(256) , Col89 VarChar(256) , Col90 VarChar(256) , Col91 VarChar(256) , Col92 VarChar(256) , Col93 VarChar(256) , Col94 VarChar(256) , Col95 VarChar(256) , Col96 VarChar(256) , Col97 VarChar(256) , Col98 VarChar(256) , Col99 VarChar(256) ) Language SQL Specific ParseCsv Not Deterministic Called On NULL Input Set Option COMMIT = *Chg , DbgView = *Source Return With X as ( -- Split IFS File into Rows (at CRLF) Select Ordinal_Position as RowKey , Element as RowInfo From Table(SYSTOOLS.Split(Get_CLOB_From_File(Trim(ParIFSFile)), X'0D25' )) A Where Trim(Element) > ''), Y as (-- Split IFS File Rows into Columns (and remove leading/trailing double quotes ") Select X.*, Ordinal_Position ColKey, Trim(B '"' From Element ) AS ColInfo From X Cross Join Table(SYSTOOLS.Split(RowInfo, ParSeparator)) A) Select RowKey , Min(Case When ColKey = 1 Then ColInfo End) Col01 , Min(Case When ColKey = 2 Then ColInfo End) Col02 , Min(Case When ColKey = 3 Then ColInfo End) Col03 , Min(Case When ColKey = 4 Then ColInfo End) Col04 , Min(Case When ColKey = 5 Then ColInfo End) Col05 , Min(Case When ColKey = 6 Then ColInfo End) Col06 , Min(Case When ColKey = 7 Then ColInfo End) Col07 , Min(Case When ColKey = 8 Then ColInfo End) Col08 , Min(Case When ColKey = 9 Then ColInfo End) Col09 , Min(Case When ColKey = 10 Then ColInfo End) Col10 , Min(Case When ColKey = 11 Then ColInfo End) Col11 , Min(Case When ColKey = 12 Then ColInfo End) Col12 , Min(Case When ColKey = 13 Then ColInfo End) Col13 , Min(Case When ColKey = 14 Then ColInfo End) Col14 , Min(Case When ColKey = 15 Then ColInfo End) Col15 , Min(Case When ColKey = 16 Then ColInfo End) Col16 , Min(Case When ColKey = 17 Then ColInfo End) Col17 , Min(Case When ColKey = 18 Then ColInfo End) Col18 , Min(Case When ColKey = 19 Then ColInfo End) Col19 , Min(Case When ColKey = 20 Then ColInfo End) Col20 , Min(Case When ColKey = 21 Then ColInfo End) Col21 , Min(Case When ColKey = 22 Then ColInfo End) Col22 , Min(Case When ColKey = 23 Then ColInfo End) Col23 , Min(Case When ColKey = 24 Then ColInfo End) Col24 , Min(Case When ColKey = 25 Then ColInfo End) Col25 , Min(Case When ColKey = 26 Then ColInfo End) Col26 , Min(Case When ColKey = 27 Then ColInfo End) Col27 , Min(Case When ColKey = 28 Then ColInfo End) Col28 , Min(Case When ColKey = 29 Then ColInfo End) Col29 , Min(Case When ColKey = 30 Then ColInfo End) Col30 , Min(Case When ColKey = 31 Then ColInfo End) Col31 , Min(Case When ColKey = 32 Then ColInfo End) Col32 , Min(Case When ColKey = 33 Then ColInfo End) Col33 , Min(Case When ColKey = 34 Then ColInfo End) Col34 , Min(Case When ColKey = 35 Then ColInfo End) Col35 , Min(Case When ColKey = 36 Then ColInfo End) Col36 , Min(Case When ColKey = 37 Then ColInfo End) Col37 , Min(Case When ColKey = 38 Then ColInfo End) Col38 , Min(Case When ColKey = 39 Then ColInfo End) Col39 , Min(Case When ColKey = 40 Then ColInfo End) Col40 , Min(Case When ColKey = 41 Then ColInfo End) Col41 , Min(Case When ColKey = 42 Then ColInfo End) Col42 , Min(Case When ColKey = 43 Then ColInfo End) Col43 , Min(Case When ColKey = 44 Then ColInfo End) Col44 , Min(Case When ColKey = 45 Then ColInfo End) Col45 , Min(Case When ColKey = 46 Then ColInfo End) Col46 , Min(Case When ColKey = 47 Then ColInfo End) Col47 , Min(Case When ColKey = 48 Then ColInfo End) Col48 , Min(Case When ColKey = 49 Then ColInfo End) Col49 , Min(Case When ColKey = 50 Then ColInfo End) Col50 , Min(Case When ColKey = 51 Then ColInfo End) Col51 , Min(Case When ColKey = 52 Then ColInfo End) Col52 , Min(Case When ColKey = 53 Then ColInfo End) Col53 , Min(Case When ColKey = 54 Then ColInfo End) Col54 , Min(Case When ColKey = 55 Then ColInfo End) Col55 , Min(Case When ColKey = 56 Then ColInfo End) Col56 , Min(Case When ColKey = 57 Then ColInfo End) Col57 , Min(Case When ColKey = 58 Then ColInfo End) Col58 , Min(Case When ColKey = 59 Then ColInfo End) Col59 , Min(Case When ColKey = 60 Then ColInfo End) Col60 , Min(Case When ColKey = 61 Then ColInfo End) Col61 , Min(Case When ColKey = 62 Then ColInfo End) Col62 , Min(Case When ColKey = 63 Then ColInfo End) Col63 , Min(Case When ColKey = 64 Then ColInfo End) Col64 , Min(Case When ColKey = 65 Then ColInfo End) Col65 , Min(Case When ColKey = 66 Then ColInfo End) Col66 , Min(Case When ColKey = 67 Then ColInfo End) Col67 , Min(Case When ColKey = 68 Then ColInfo End) Col68 , Min(Case When ColKey = 69 Then ColInfo End) Col69 , Min(Case When ColKey = 70 Then ColInfo End) Col70 , Min(Case When ColKey = 71 Then ColInfo End) Col71 , Min(Case When ColKey = 72 Then ColInfo End) Col72 , Min(Case When ColKey = 73 Then ColInfo End) Col73 , Min(Case When ColKey = 74 Then ColInfo End) Col74 , Min(Case When ColKey = 75 Then ColInfo End) Col75 , Min(Case When ColKey = 76 Then ColInfo End) Col76 , Min(Case When ColKey = 77 Then ColInfo End) Col77 , Min(Case When ColKey = 78 Then ColInfo End) Col78 , Min(Case When ColKey = 79 Then ColInfo End) Col79 , Min(Case When ColKey = 80 Then ColInfo End) Col80 , Min(Case When ColKey = 81 Then ColInfo End) Col81 , Min(Case When ColKey = 82 Then ColInfo End) Col82 , Min(Case When ColKey = 83 Then ColInfo End) Col83 , Min(Case When ColKey = 84 Then ColInfo End) Col84 , Min(Case When ColKey = 85 Then ColInfo End) Col85 , Min(Case When ColKey = 86 Then ColInfo End) Col86 , Min(Case When ColKey = 87 Then ColInfo End) Col87 , Min(Case When ColKey = 88 Then ColInfo End) Col88 , Min(Case When ColKey = 89 Then ColInfo End) Col89 , Min(Case When ColKey = 90 Then ColInfo End) Col90 , Min(Case When ColKey = 91 Then ColInfo End) Col91 , Min(Case When ColKey = 92 Then ColInfo End) Col92 , Min(Case When ColKey = 93 Then ColInfo End) Col93 , Min(Case When ColKey = 94 Then ColInfo End) Col94 , Min(Case When ColKey = 95 Then ColInfo End) Col95 , Min(Case When ColKey = 96 Then ColInfo End) Col96 , Min(Case When ColKey = 97 Then ColInfo End) Col97 , Min(Case When ColKey = 98 Then ColInfo End) Col98 , Min(Case When ColKey = 99 Then ColInfo End) Col99 From Y Where RowKey > (ParStartWithRow - 1) -- Remove header if necessary Group By RowKey Order By RowKey; Label on Specific Function PARSECSV IS 'CSV Parser' ;

Thanks Birgitta. I think Modifies SQL Data would also be required here. Based on the ACS wizard, the default behavior would be Reads SQL Data. Without the Modifies, the system will return SQL0577 for this routine.

Also, just a matter of taste, I would name the columns after their Excel counterparts (i.e., A,B,C,...AA,AB,AC,...) to more easily map them out to the origins.

One needs to be cognizant about certain size limitation split() currently imposes.

Looking at the parameter definition, INPUT_LIST is defined as CLOB(1048576). I have a (relatively) large csv file (40 columns, just under 30K rows, and the size stands at just under 7M). The first CTE from the new UDTF parsecsv() will cut the processing short when run against that file.

The size of the input parm for split() could probably be bumped up to help the size limitation issue, but I would be concerned about the performance impact. The performance of the new UDTF parsecsv() is not too great already.

It seems to me that, while very cool, this routine could only be deployed to run against relatively small files. Birgitta, would you agree, or am I missing anything?