0

I am tasked to create a template that will be Filled up by Business Users with Employee Information, then our program will load this into the Database using External Tables.

However, our Business Users constantly change the template by adding, removing or reordering fields.

I am convinced to use XLSX instead of CSV so that I can lock the Column Headers so they cannot remove, add and reorder the columns. However, When i query the External Table, it shows Non-ASCII Characters when reading XLSX because its in Binary.

How can i do either of the following?

  1. Effectively Read Excel Files from External Tables
  2. Lock the Headers of CSV Files?
Migs Isip
  • 1,450
  • 3
  • 23
  • 50

1 Answers1

0

What you have here is a political problem, but you are looking for a technical fix. Not a good fit.

The problem comes in two halves:

  1. Somebody decided it was a good idea to collect user input in a spreadsheet, which it is generally not.
  2. Users are fiddling with the input format, which they should not.

Fixes are:

  1. Strictly enforce the data structure. Reject any CSV which doesn't natch and make the users edit them. They will quickly tire of tweaking the spreadsheets when they realise they're just creating more work for themselves. But they will also get resentful, so consider ...
  2. Building a data input screen. It's pretty simple to knock up a spreadsheet-like grid UI. You don't need anything complicated in Java: Oracle's Apex is intended for exactly this sort of thing. Find out more.

However, if you are stuck with Excel as a UI I suggest you have a look at Anton Scheffer's excellent PLSQL as_read_xlsx package on the Amis site. Check it out. You'll probably need to replace your external table with a view over a table (perhaps pipelined) function.

APC
  • 144,005
  • 19
  • 170
  • 281