is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

------------------------------
David Strawn
------------------------------

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

For these IBM i services through SQL interface, Run SQL Script tool in IBM i ACS provides samples to help you use many of them. This is how you access it :

From Run SQL Script main menu bar, select  Edit --> Examples --> Insert from Examples


Select IBM i Services from the drop-down field and scroll down to the section for IFS and select one of your interest 


You can write your own and add it to this example repository. 

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

Thanks for the references.  I took a quick look at the links you sent, but it doesn't look like these methods give you any way to preserve column integrity, which I was hoping to be able to do.  but i will dig deeper into it...

------------------------------
David Strawn

Original Message:
Sent: Fri May 12, 2023 09:42 PM
From: Satid Singkorapoom
Subject: using select to read a file in the IFS

For these IBM i services through SQL interface, Run SQL Script tool in IBM i ACS provides samples to help you use many of them. This is how you access it :

From Run SQL Script main menu bar, select  Edit --> Examples --> Insert from Examples


Select IBM i Services from the drop-down field and scroll down to the section for IFS and select one of your interest 


You can write your own and add it to this example repository. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Fri May 12, 2023 11:57 AM
From: David Strawn
Subject: using select to read a file in the IFS

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

------------------------------
David Strawn
------------------------------

Thanks for the references.  I took a quick look at the links you sent, but it doesn't look like these methods give you any way to preserve column integrity, which I was hoping to be able to do.  but i will dig deeper into it...

For these IBM i services through SQL interface, Run SQL Script tool in IBM i ACS provides samples to help you use many of them. This is how you access it :

From Run SQL Script main menu bar, select  Edit --> Examples --> Insert from Examples


Select IBM i Services from the drop-down field and scroll down to the section for IFS and select one of your interest 


You can write your own and add it to this example repository. 

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

You never have "column integrity" in IFS streamfiles, which are just a sequence of bytes! The SQL functions referenced by @Satid Singkorapoom can return these bytes for you but you will have to interpret them yourself.

SQL is not suited for these kind of operations, and I would go for CPYFRMIMPF for CSV files. This command will copy the streamfile into a SQL table and validate the "column integrity".

For Excel files, I would recommend Python and Pandas to convert an Excel file into a SQL table. But this is a very advanced technique, which requires knowledge of open source tools on IBM i.

Best regards,
Christian

------------------------------
Christian Jorgensen
IT System Administrator
Network of Music Partners A/S

Original Message:
Sent: Tue May 16, 2023 11:02 AM
From: David Strawn
Subject: using select to read a file in the IFS

Thanks for the references.  I took a quick look at the links you sent, but it doesn't look like these methods give you any way to preserve column integrity, which I was hoping to be able to do.  but i will dig deeper into it...

------------------------------
David Strawn

Original Message:
Sent: Fri May 12, 2023 09:42 PM
From: Satid Singkorapoom
Subject: using select to read a file in the IFS

For these IBM i services through SQL interface, Run SQL Script tool in IBM i ACS provides samples to help you use many of them. This is how you access it :

From Run SQL Script main menu bar, select  Edit --> Examples --> Insert from Examples


Select IBM i Services from the drop-down field and scroll down to the section for IFS and select one of your interest 


You can write your own and add it to this example repository. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Fri May 12, 2023 11:57 AM
From: David Strawn
Subject: using select to read a file in the IFS

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

------------------------------
David Strawn
------------------------------

Dear Christian

For importing Excel (or Calc) spreadsheet file into a DB2i table (and the reverse - export), there is another long-lasting non-programmatic tool which is Data Transfer to/from IBM i available as well, since the time of AS/400 Client Access for Windows and now from IBM i ACS - Data Transfer which now looks like this : 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Wed May 17, 2023 04:22 AM
From: Christian Jorgensen
Subject: using select to read a file in the IFS

You never have "column integrity" in IFS streamfiles, which are just a sequence of bytes! The SQL functions referenced by @Satid Singkorapoom can return these bytes for you but you will have to interpret them yourself.

SQL is not suited for these kind of operations, and I would go for CPYFRMIMPF for CSV files. This command will copy the streamfile into a SQL table and validate the "column integrity".

For Excel files, I would recommend Python and Pandas to convert an Excel file into a SQL table. But this is a very advanced technique, which requires knowledge of open source tools on IBM i.

Best regards,
Christian

------------------------------
Christian Jorgensen
IT System Administrator
Network of Music Partners A/S

Original Message:
Sent: Tue May 16, 2023 11:02 AM
From: David Strawn
Subject: using select to read a file in the IFS

Thanks for the references.  I took a quick look at the links you sent, but it doesn't look like these methods give you any way to preserve column integrity, which I was hoping to be able to do.  but i will dig deeper into it...

------------------------------
David Strawn

Original Message:
Sent: Fri May 12, 2023 09:42 PM
From: Satid Singkorapoom
Subject: using select to read a file in the IFS

For these IBM i services through SQL interface, Run SQL Script tool in IBM i ACS provides samples to help you use many of them. This is how you access it :

From Run SQL Script main menu bar, select  Edit --> Examples --> Insert from Examples


Select IBM i Services from the drop-down field and scroll down to the section for IFS and select one of your interest 


You can write your own and add it to this example repository. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Fri May 12, 2023 11:57 AM
From: David Strawn
Subject: using select to read a file in the IFS

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

------------------------------
David Strawn
------------------------------

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

Thanks for all the input!

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?

Dear David

I forgot to say that CPYFROMIMPF and any IBM i command at all can also be invoked from SQL code using QCMDEXC scalar function:  CALL QSYS2.QCMDEXC( '<command line text>' ) ;

Thanks for all the input!

is it possible to use select to read records from a file (csv, xlsx...) from the IFS?