CODEWITHSUNDEEP
oracle-apex
gpreston
gpreston

Reputation: 1

Building app to upload CSV to Oracle 12c database via Apex

I'v been asked to create an app in Oracle Apex that will allow me to drop a CSV file. The file contains a list of all active physicians and associated info in my area. I do not know where to begin! Requirements:

-after dropping CSV file to apex, remove unnecessary columns -edit data in each field, ie if phone# > 7 characters and begins with 1, remove 1. Or remove all special characters from a column. -The CSV contains physicians of every specialty, I only want to upload specific specialties to the database table.

I have a small amount of SQL experience from Uni, and I know some HTML and CSS, but beyond that I am lost. Please help!

Began tutorial on Oracle-Apex. Created upload wizard on a dev environment

  1. User drops CSV file to apex
  2. Apex edits columns to remove unneccesary characteres
  3. Only uploads specific columns from CSV file
  4. Only adds data when column "Specialties" = specific specialties
  5. Does not add redundant data (physician is already located in table, do nothing)
  6. Produces report showing all new physicians added to table

Upvotes: 0

Views: 961

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Huh, you're in deep trouble as you have to do some job using a tool you don't know at all, with limited knowledge of SQL language. Yes, it is said that Apex is simple to use, but nonetheless ... you have to know at least something. Otherwise, as you said, you're lost.

See if the following helps.

  • there's the CSV file
  • create a table in your database; its description should match the CSV file. Mention all columns it contains. Pay attention to datatypes, column lengths and such
    • this table will be "temporary" - you'll use it every day to load data from CSV files: first you'll delete all it contains, then load new rows
  • using Apex "Create page" Wizard, create the "Data loading" process. Follow the instructions (and/or read documentation about it). Once you're done, you'll have 4 new pages in your Apex application
  • when you run it, you should be able to load CSV file into that temporary table

That's the first stage - successfully load data into the database. Now, the second stage: fix what's wrong.

  • create another table in the database; it will be the "target" table and is supposed to contain only data you need (i.e. the subset of the temporary table). If such a table already exists, you don't have to create a new one.
  • create a stored procedure. It will read data from the temporary table and edit everything you've mentioned (remove special characters, remove leading "1", ...)
    • as you have to skip physicians that already exist in the target table, use NOT IN or NOT EXISTS
  • then insert "clean" data into the target table

That stored procedure will be executed after the Apex loading process is done; a simple way to do that is to create a button on the last page which will - when pressed - call the procedure.

The final stage is the report:

  • as you have to show new physicians, consider adding a column (into the target table) which will be a timestamp (perhaps DATE is enough, if you'll be doing it once a day) or process_id (all rows inserted in the same process will share the same value) so that you could distinguish newly added rows from the old ones
  • the report itself would be an Interactive report. Why? Because it is easy to create and lets you (or end users) to adjust it according to their needs (filter data, sort rows in a different manner, ...)

Good luck! You'll need it.

Upvotes: 1

Related Questions