RE: [EXTERNAL] Re: SQL to read CSV file

This looks familiar
Are you running this under strsql or in ACS sql scripts?
I am guessing strsql
Probably need to use ACS




Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone


-------- Original message --------
From: Gad Miron <gadmiron@xxxxxxxxx>
Date: 7/5/21 04:36 (GMT-05:00)
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: SQL to read CSV file

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>