0

I am having a excel file having 15 columns and 1000's of records. I want to load that data to 3 different oracle tables.

How can we do this ? Should I convert the file to csv first?

Also, there is one more complication to task. I need to perform some validations before inserting to tables, eg. I have a column A in excel I want to use the column A to derive value B from an Oracle table say 'tab' , then store B on table 'tab'.

APC
  • 144,005
  • 19
  • 170
  • 281
Pradeep Bisht
  • 31
  • 1
  • 6
  • Are you find this one? https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer – yongsup Jun 23 '17 at 09:13
  • @yongsup - that answer relates to **exporting** data to CSV. This question is about **importing** data. – APC Jun 23 '17 at 10:59
  • You can load data from one file into 3 tables using sqlldr as long as there is an identifier on each row to identify which table that row goes to. Better if it is at the start of the row. You need to export to a csv first. Show some actual data rows of all types but change personal data if any of course. – Gary_W Jun 26 '17 at 14:16
  • Is the SAME data going to 3 different tables? Please show some sample data. – Gary_W Jun 28 '17 at 17:02

3 Answers3

0

Generally it is easier to work with raw data using SQL, so the first step is to get the raw data queryable in the easiest fashion.

The neatest solution is to use an external table. Convert the Excel spreadsheet into a CSV file then define an external table to query the file. Then you can use ...

 INSERT INTO << table1 >> (...)
 SELECT what_ever  FROM << external_table >>

... or even ...

INSERT ALL
    INTO << table1 >> (...)
    INTO << table2 >> (...)
    INTO << table3 >> (...)
SELECT *  FROM << external_table >>

... depending on what rules you need to apply.

If your organization already uses external tables this should be easy to configure. However, some places are funny about allowing the database to interact with OS files, so you may not be able to use this approach. Find out more.

Alternatively you can build a staging table which matches the CSV file, and load the data into that using SQL*Loader. SQL*loader is a client-side tool, so it is requires less permissions to use.

If you don't want to build any new structures at all you can edit the CSV file to form a set of SELECT statements from DUAL (use UNION ALL) and insert into the target tables from that. Mastery of regex can really help with tasks like this.

Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
0

If it is a routine activity, Use SQLLDR - SQL Loader to load the raw data to a staging table. Write a Stored Procedure with business logic needed to transform the data from staging table to main tables

Rajesh Mani Kumar G
  • 1
  • 2
0

You can use external csv as a table like this: Your file's location is here csv_dir/yourfile.csv

CREATE TABLE csv_test_table (
  COLUMN1 varchar2(255),
  COLUMN2 varchar2(255),
  COLUMN3 varchar2(255),
  COLUMN4 varchar2(255)

)
ORGANIZATION EXTERNAL (
  DEFAULT DIRECTORY csv_dir
  ACCESS PARAMETERS (
    records delimited BY newline
    skip 1
    fields 
    terminated BY ';' optionally enclosed BY '"' --or use ',' here
    lrtrim
    missing field VALUES are NULL
  )
  LOCATION ('yourfile.csv')  
);

and after that you can select from csv_test_table to wherever you want. The same data to 3 tables, or using some conditions.

babicsp
  • 33
  • 4