Re: SQL to read CSV file

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


"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)