Re: SQL to read CSV file

[Gad Miron <gadmiron@xxxxxxxxx>]

Hello guys

Testing IFS_READ_UTF8  / IFS_READ
when issuing
select * from table(IFS_READ('/GAD/ABC.txt'))
I  get
*POINTER
*POINTER
*POINTER
*POINTER
etc.

What am I doing wrong?

TIA
Gad


> date: Sat, 19 Jun 2021 12:41:48 +0200
> from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
> subject: RE: SQL to read CSV file
>
> Hi Justin,
>
> So in your example the IFS_READ will return multiple rows depending on how
> many CRLF characters are in your IFS File.
> The SPLIT() Function splits at the passed delimiter, it does not check
> whether the delimiter is embedded in double quotes or not.
> If your *.CSV file consists only of "strings" you may pass the delimiter
> ","
> to the SPLIT functions. In this case also the leading and trailing double
> quotes are removed.
> If you need to check if the delimiter is embedded in a "string" or not, you
> may (as others already suggested) do a reverse engineering, and retrieve
> the
> source code for the SPLIT function (it is not obfuscated), modify it and
> create your own SPLIT-Function.
>
> Here is an example how you can read a *.csv file directly from the IFS and
> split the result into columns. It even removes the leading and trailing
> double quotes from a string and converts a few columns into non-character
> data types.
> ... but it uses the original SPLIT function.
> With x as (Select * from
> Table(IFS_READ_UTF8('/home/YourDir1/YourDir2/YourCsvFile.csv'))),
>      y as (Select x.*, Ordinal_Position ColKey,
>                   Trim(B '"' from Element) as ColInfo
>              from x cross join Table(Split(Line, ',')))
> Select Line_Number,
>        Min(Case When ColKey = 1 Then ColInfo End) Your1Col,
>        Min(Case When ColKey = 2 Then ColInfo End) Your2Col,
>        Min(Case When ColKey = 3 Then ColInfo End) Your3Col,
>        Min(Case When ColKey = 4 Then Date(ColInfo) End) Your4Col,
>        Min(Case When ColKey = 5 Then Dec(ColInfo, 11, 2) End) Your5Col,
>        Min(Case When ColKey = 6 Then ColInfo End) Your6Col,
>        Min(Case When ColKey = 7 Then ColInfo End) Your7Col
> > From y
> Group By Line_Number
> Order By Line_Number
>
> Mit freundlichen Gr??en / Best regards
>
> Birgitta Hauser