Skip to main content
Question

best approach to import CSV file from FTP

  • September 5, 2021
  • 3 replies
  • 918 views

Forum|alt.badge.img+9
  • Sidekick (Employee)
  • 50 replies

Hi,

just would like to know for IFS APP10 or IFS Cloud,

what is the best approach to import a CSV file from FTP?

How you do this in your real project?

 

By the way, I know

FTP Reader → IFS Connect ->Routing → Address (Transforms ,  call BizAPI/RestAPI)

But looks too many development overhead.

This topic has been closed for comments

3 replies

Forum|alt.badge.img+6

We are on apps9. I would use a file data migration job to load the csv into the database and then use plsql procedure and api’s to update the relevant objects in IFS. You would need something to copy the file from FTP to the utl_file_dir.


dsj
Superhero (Partner)
Forum|alt.badge.img+22
  • Superhero (Partner)
  • 828 replies
  • September 7, 2021

In Apps 10 onwards you can directly call PLSQL method instead of BizApi from routing address. Therefore no need of transformer and BizApi development. CSV can be parsed and processed inside the PLSQL method.

 

Read More here: PL/SQL Methods (ifs.com)

 

Cheers!

Damith


durette
Superhero (Customer)
Forum|alt.badge.img+19
  • Superhero (Customer)
  • 525 replies
  • September 7, 2021

I've found using Oracle Loader external tables is easier than using migration jobs. They're far easier to reuse for multiple purposes.

You'll need to edit this if you want more than 10 columns or more than 256 characters in your data. You'll also need to cast strings to numbers or dates as needed downstream.

Reading from a CSV file sitting in Oracle directory is a lot like reading from a view into that file.

 

-- AS SYS:
CREATE OR REPLACE DIRECTORY c_external_csv_dir AS '\\your\server\location';
GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifsapp;
GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifssys;

-- AS IFSAPP:
CREATE TABLE c_external_csv_tab
(
   a1   VARCHAR2(256),
   a2   VARCHAR2(256),
   a3   VARCHAR2(256),
   a4   VARCHAR2(256),
   a5   VARCHAR2(256),
   a6   VARCHAR2(256),
   a7   VARCHAR2(256),
   a8   VARCHAR2(256),
   a9   VARCHAR2(256),
   a10  VARCHAR2(256)
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY c_external_csv_dir
   ACCESS PARAMETERS 
   (
      RECORDS DELIMITED BY newline
      BADFILE 'c_external_csv_tab.bad'
      DISCARDFILE 'c_external_csv_tab.dis'
      LOGFILE 'c_external_csv_tab.log'
      SKIP 1
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      LTRIM
      MISSING FIELD VALUES ARE NULL
      (
         a1   CHAR(256),
         a2   CHAR(256),
         a3   CHAR(256),
         a4   CHAR(256),
         a5   CHAR(256),
         a6   CHAR(256),
         a7   CHAR(256),
         a8   CHAR(256),
         a9   CHAR(256),
         a10  CHAR(256)
      )
  )
  LOCATION (c_external_csv_dir:'c_external_csv_tab.csv')
)
REJECT LIMIT UNLIMITED;

GRANT SELECT ON c_external_csv_tab TO ifsinfo WITH GRANT OPTION;
GRANT SELECT ON c_external_csv_tab TO ifssys WITH GRANT OPTION;
GRANT SELECT ON c_external_csv_tab TO ifsprint;


-- C_EXTERNAL_CSV_TAB now acts like a view to read directly from the CSV.
-- Drop in a CSV file into that directory, then read from it like this:

SELECT * FROM c_external_csv_tab;

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings