How to upload .csv file into tables in Apex?

Question

I have question using Apex 5 for uploading data into tables through csv file format. Using the Data loading wizard, I was able to do it easily for all the columns but I want to know how to do it only for specific columns.

For example, I have a table with three columns x, y, z and I have an Excel sheet with y and z data. x is a foreign key and its value has to be taken from another table. How can I insert only y and z data with x data coming from another table?

Answer 1

i am assuming that all fields in your database table has data so you you can add a page process for giving new values to the uploaded data which has null x columns

1 go to 4th page 'Data Load Results' which created by data wizard

2 click add a page process button

3 choose pl/sql

4 give a name and choose on submit - after computations and validations

5 enter an update command which suits your needs as below

update your_table_name
set 
x=20
where x is null;

6 write your success messages

7 choose finish button in option "when button presses"

8 create process

9 add a Branch button in Branches

10 give a name click next

11 enter the page you want to go after data load

12 choose finish button in option "when button presses"

13 go to finish button and choose submit in Action When Button Clicked -> action

by adding this process and branch

1st system will insert data to table

2nd it will update all the null x values to the static values in your table

3rd it will return to the page you want

Answer 2

I do this all the time, in reality it's simply easiest to import your data to a new table and do a manual merge. (it's also the fastest)

1. Import your data to a new temporary table (all options open and large values, so no rows will be dropped)
2. Merge / insert your data to the correct table
3. Drop the temporary table.

I was always trying to do a direct insert with non-homogeneous data and simply switching practices, made my life easier. --- EDIT ----

Explaining the process, importing spreadsheet or CSV data in APEX 5.0

Select data workshop

Select data format

New table + copy paste

Copy paste some spreadsheet data

Confirm new table and data

create new sequence

Press Load data

After that you can open up your favorite sql editor and insert the data using regular DML.

select * from STAGING_T1;

After you have processed the data, delete the staging table

drop table STAGING_T1;

Olafur

Answer 3

for similar case i created Data Loading Wizard pages in my application in wizard

there is a section data lookup which can be used for this need

you can inspect below tutorial in 4.1, probably it would work in 5.0

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r41/dataload/dataload.htm