Load CSV data into OAC

Jan Zahradka-Oracle

Summary:

I have a set of CSV data files generated daily. I need to find a solution how to load (import, synchronize, etc.) data into OAC tables.

Content (required):

I have a set of CSV data files generated daily. I need to find a solution how to load (import, synchronize, etc.) data into OAC tables.

Example: I imported file XY123.csv into OAC on Monday as new data source. On Tuesday I get new data CSV file with the same name XY123.csv. How can I load that new file into OAC to add or update the data in existing XY123.csv data source in OAC, because I have already some visualizations using that data source (I need to "refresh" data only using that Tuesday's new file)? I wish to do it in (semi-)automatic way.

Thank you a lot for any hint or advice.

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Joshua C. Stewart

As far as I know, there aren't any automated ways to load data from local file storage to OAC. There aren't API's available to POST data to dataset storage.

If you're looking to fully automate this process, there are 2 routes I can think of.

Option 1: Update your CSV generation process to drop new data into OCI Object Store. Build automation to load new data from OCI Object Store to Oracle Database/Autonomous Database, then use Oracle Database table as the source for your visualizations.

Option 2: Build a custom RPA (Selenium, pygui) script to run on your local machine that will concatenate your files and load them to OAC on a schedule.

There may be other ways, but none that I can think of.

Jan Zahradka-Oracle

Thank you Joshua,

Regarding Option 2: Could you advise me the way how to load data into the OAC? Via API? Via JDBC? I could incorporate it into my Java script which is preparing CSV data from OSvC.

Joshua C. Stewart

Hi Jan,

The main issue is that OAC's API's are limited. There isn't an API for loading the data directly to OAC. I would suggest loading your data directly to a database table.

If that isn't an option, I would have your script generate the file, drop the file on your client client, and kick off a script leveraging Selenium to navigate to OAC, and mimic the clicks a user would do to load the data.

We've built python scripts leveraging Selenium and pygui to automate the process of taking snapshots and loading the snapshots to Object Storage.

Also - is there a reason you aren't creating a direct connection to OSvC? I believe you can create a connection via the 'Oracle Applications' connector...

Jan Zahradka-Oracle

The reason why we are not accessing directly to OSvC is that we need to use OKCS data which is not available in the connector. I spent a lot of time with investigating it and much more time to create a scripted workaround.

Current situation (bunch of CSVs) is a half-way stand on the road to the ideal structure (we need to incorporate NetSuite data as well).