[oracle] Oracle: Import CSV file

I've been searching for a while now but can't seem to find answers so here goes...

I've got a CSV file that I want to import into a table in Oracle (9i/10i).

Later on I plan to use this table as a lookup for another use.

This is actually a workaround I'm working on since the fact that querying using the IN clause with more that 1000 values is not possible.

How is this done using SQLPLUS?

Thanks for your time! :)

This question is related to oracle csv import sqlplus sql-loader

The answer is


Another solution you can use is SQL Developer.

With it, you have the ability to import from a csv file (other delimited files are available).

Just open the table view, then:

  • choose actions
  • import data
  • find your file
  • choose your options.

You have the option to have SQL Developer do the inserts for you, create an sql insert script, or create the data for a SQL Loader script (have not tried this option myself).

Of course all that is moot if you can only use the command line, but if you are able to test it with SQL Developer locally, you can always deploy the generated insert scripts (for example).

Just adding another option to the 2 already very good answers.


An alternative solution is using an external table: http://www.orafaq.com/node/848

Use this when you have to do this import very often and very fast.


SQL Loader is the way to go. I recently loaded my table from a csv file,new to this concept,would like to share an example.

LOAD DATA
    infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
    REPLACE
    INTO TABLE LOAN_BALANCE_MASTER_INT
    fields terminated by ',' optionally enclosed by '"'
    (
    ACCOUNT_NO,
    CUSTOMER_NAME,
    LIMIT,
    REGION

    )

Place the control file and csv at the same location on the server. Locate the sqlldr exe and invoce it.

sqlldr userid/passwd@DBname control= Ex : sqlldr abc/xyz@ora control=load.ctl

Hope it helps.


Somebody asked me to post a link to the framework! that I presented at Open World 2012. This is the full blog post that demonstrates how to architect a solution with external tables.


From Oracle 18c you could use Inline External Tables:

Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as persistent object in the data dictionary.

With inline external tables, the same syntax that is used to create an external table with a CREATE TABLE statement can be used in a SELECT statement at runtime. Specify inline external tables in the FROM clause of a query block. Queries that include inline external tables can also include regular tables for joins, aggregation, and so on.

INSERT INTO target_table(time_id, prod_id, quantity_sold, amount_sold)
SELECT time_id, prod_id, quantity_sold, amount_sold
FROM   EXTERNAL (   
    (time_id        DATE NOT NULL,     
     prod_id        INTEGER NOT NULL,
     quantity_sold  NUMBER(10,2),
     amount_sold    NUMBER(10,2))     
    TYPE ORACLE_LOADER     
    DEFAULT DIRECTORY data_dir1
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY '|')     
   LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;

I would like to share 2 tips: (tip 1) create a csv file (tip 2) Load rows from a csv file into a table.

====[ (tip 1) SQLPLUS to create a csv file form an Oracle table ]====

I use SQLPLUS with the following commands:

set markup csv on
set lines 1000
set pagesize 100000 linesize 1000
set feedback off 
set trimspool on
spool /MyFolderAndFilename.csv
Select *  from MYschema.MYTABLE  where MyWhereConditions ;
spool off
exit

====[tip 2 SQLLDR to load a csv file into a table ]====

I use SQLLDR and a csv ( comma separated ) file to add (APPEND) rows form the csv file to a table. the file has , between fields text fields have " before and after the text CRITICAL: if last column is null there is a , at the end of the line

Example of data lines in the csv file:

11,"aa",1001
22,"bb',2002
33,"cc",
44,"dd",4004
55,"ee',

This is the control file:

LOAD DATA
APPEND 
INTO TABLE MYSCHEMA.MYTABLE
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
CoulmnName1,
CoulmnName2,
CoulmnName3
)

This is the command to execute sqlldr in Linux. If you run in Windows use \ instead of / c:

sqlldr userid=MyOracleUser/MyOraclePassword@MyOracleServerIPaddress:port/MyOracleSIDorService  DATA=datafile.csv  CONTROL=controlfile.ctl  LOG=logfile.log  BAD=notloadedrows.bad

Good luck !


Questions with oracle tag:

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function How to run .sql file in Oracle SQL developer tool to import database? How to kill all active and inactive oracle sessions for user What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? How to subtract hours from a date in Oracle so it affects the day also Why does Oracle not find oci.dll? Oracle SQL - DATE greater than statement How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 How do I view the Explain Plan in Oracle Sql developer? Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA How to change Oracle default data pump directory to import dumpfile? How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence DATEDIFF function in Oracle How can I solve ORA-00911: invalid character error? sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory Windows cannot find 'http:/.127.0.0.1:%HTTPPORT%/apex/f?p=4950'. Make sure you typed the name correctly, and then try again How do I resolve this "ORA-01109: database not open" error? ORA-01653: unable to extend table by in tablespace ORA-06512 How to Select Top 100 rows in Oracle? How to determine tables size in Oracle Split String by delimiter position using oracle SQL ORA-28000: the account is locked error getting frequently Oracle Add 1 hour in SQL Oracle listener not running and won't start PLS-00428: an INTO clause is expected in this SELECT statement ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? What is the max size of VARCHAR2 in PL/SQL and SQL? How to configure Glassfish Server in Eclipse manually Oracle query to identify columns having special characters The listener supports no services ORA-00907: missing right parenthesis NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition Equivalent function for DATEADD() in Oracle Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Questions with csv tag:

Pandas: ValueError: cannot convert float NaN to integer Export result set on Dbeaver to CSV Convert txt to csv python script How to import an Excel file into SQL Server? "CSV file does not exist" for a filename with embedded quotes Save Dataframe to csv directly to s3 Python Data-frame Object has no Attribute (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape How to write to a CSV line by line? How to check encoding of a CSV file Excel: macro to export worksheet as CSV file without leaving my current Excel sheet How to get rid of "Unnamed: 0" column in a pandas DataFrame? Key error when selecting columns in pandas dataframe after read_csv Use python requests to download CSV ValueError: not enough values to unpack (expected 11, got 1) TypeError: a bytes-like object is required, not 'str' in python and CSV How to add header row to a pandas DataFrame TypeError: list indices must be integers or slices, not str Pandas read_csv from url Write single CSV file using spark-csv Encoding Error in Panda read_csv Java - Writing strings to a CSV file Deleting rows with Python in a CSV file How to convert dataframe into time series? Load CSV file with Spark IndexError: too many indices for array How do I skip a header from CSV files in Spark? Error in file(file, "rt") : cannot open the connection Printing column separated by comma using Awk command line What does the error "arguments imply differing number of rows: x, y" mean? Read specific columns with pandas or other python module How do I read a large csv file with pandas? Pandas df.to_csv("file.csv" encode="utf-8") still gives trash characters for minus sign Adding double quote delimiters into csv file Writing .csv files from C++ Python import csv to list What does the "More Columns than Column Names" error mean? Adding a newline character within a cell (CSV) Python Pandas: How to read only first n rows of CSV files in? Maximum number of rows of CSV data in excel sheet Parsing a CSV file using NodeJS Download a file from HTTPS using download.file() Create Pandas DataFrame from a string Calculate summary statistics of columns in dataframe datetime dtypes in pandas read_csv Import multiple csv files into pandas and concatenate into one DataFrame How to avoid Python/Pandas creating an index in a saved csv? How to split CSV files as per number of rows specified? Skip rows during csv import pandas Batch file to split .csv file

Questions with import tag:

Import functions from another js file. Javascript The difference between "require(x)" and "import x" pytest cannot import module while python can How to import an Excel file into SQL Server? When should I use curly braces for ES6 import? How to import a JSON file in ECMAScript 6? Python: Importing urllib.quote importing external ".txt" file in python beyond top level package error in relative import Reading tab-delimited file with Pandas - works on Windows, but not on Mac Intellij Cannot resolve symbol on import importing go files in same folder PyCharm import external library How can I import Swift code to Objective-C? Python name 'os' is not defined Python circular importing? Android Studio suddenly cannot resolve symbols Exception in thread "main" java.lang.Error: Unresolved compilation problems Call a function from another file? ipynb import another ipynb file Import Excel Spreadsheet Data to an EXISTING sql table? @import vs #import - iOS 7 ERROR 1148: The used command is not allowed with this MySQL version ImportError: No module named 'pygame' PG COPY error: invalid input syntax for integer Issue when importing dataset: `Error in scan(...): line 1 did not have 145 elements` How do I import an SQL file using the command line in MySQL? Importing variables from another file? Remove Project from Android Studio 'module' object is not callable - calling method in another file How to import or copy images to the "res" folder in Android Studio? How to insert an image in python The import com.google.android.gms cannot be resolved Import Python Script Into Another? Mongoimport of json file How to use custom packages Relative imports for the billionth time MYSQL import data from csv using LOAD DATA INFILE How to import data from text file to mysql database How do I import a CSV file in R? How to remove unused imports in Intellij IDEA on commit? Importing packages in Java Python: importing a sub-package or sub-module import dat file into R Importing CSV data using PHP/MySQL PHP import Excel into database (xls & xlsx) How to import multiple .csv files at once? How to import a csv file into MySQL workbench? MySQL: ignore errors when importing? import .css file into .less file

Questions with sqlplus tag:

When or Why to use a "SET DEFINE OFF" in Oracle Database SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory How do I resolve this "ORA-01109: database not open" error? How to echo text during SQL script execution in SQLPLUS PL/SQL ORA-01422: exact fetch returns more than requested number of rows how to pass variable from shell script to sqlplus Connect to Oracle DB using sqlplus sqlplus how to find details of the currently connected database session How to output oracle sql result into a file in windows? Escaping ampersand character in SQL string ORA-12154: TNS:could not resolve the connect identifier specified (PLSQL Developer) ORA-12514 TNS:listener does not currently know of service requested in connect descriptor DBMS_OUTPUT.PUT_LINE not printing Connect to sqlplus in a shell script and run SQL scripts How to kill a running SELECT statement TNS Protocol adapter error while starting Oracle SQL*Plus How to get the employees with their managers how to increase sqlplus column output length? How to View Oracle Stored Procedure using SQLPlus? Oracle SqlPlus - saving output in a file but don't show on screen Oracle: Import CSV file executing a function in sql plus Oracle query execution time CLEAR SCREEN - Oracle SQL Developer shortcut? How to display table data more clearly in oracle sqlplus How to display databases in Oracle 11g using SQL*Plus Force index use in Oracle sqlplus statement from command line How do you execute SQL from within a bash script? When do I need to use a semicolon vs a slash in Oracle SQL? How do I spool to a CSV formatted file using SQLPLUS? How can I issue a single command from the command line through sql plus? SQL Plus change current directory How do I format my oracle queries so the columns don't wrap? How to insert a string which contains an "&" How do I ignore ampersands in a SQL script running from SQL Plus?

Questions with sql-loader tag:

Oracle: Import CSV file Oracle sqlldr TRAILING NULLCOLS required, but why? Disable and later enable all table indexes in Oracle