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
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin
Taylor
Sent: Freitag, 18. Juni 2021 20:09
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL to read CSV file
This may be a moot question, because it appears SYSTOOLS.SPLIT() can't
handle quoted strings (e.g. "LastName,FirstName").
1. select LINE from table(QSYS2.IFS_READ(PATH_NAME => '/tmp/Filename.csv',
END_OF_LINE => 'CRLF')) 2. Yes 3. No
Thanks
date: Thu, 17 Jun 2021 19:10:00 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: SQL to read CSV file
1. Can you show how you call IFS_READ, i.e. how your parameters are set?
2. Are CRLF characters included in the IFS File or not?
3. Can the IFS File split into rows depending on a predefined length?
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
?Train people well enough so they can leave, treat them well enough so
they don't want to.? (Richard Branson)
--
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
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
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
midrange.com
