SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle Database.
You can load data into an Oracle database by using the sqlldr command in UNIX.
sqlldr username@server/password control =loader.ctl data=data_file.csv
loader.ctl is the control file.
Following is the example of control file that needs to be placed at the $XX_TOP/bin directory
INTO TABLE XX_EMP_STG
fields terminated by “,” optionally enclosed by ‘”‘
Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.
Possible problems and workarounds:
The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor):
' Removing tabs and carriage returns from worksheet cells Sub CleanUp() Dim TheCell As Range On Error Resume Next For Each TheCell In ActiveSheet.UsedRange With TheCell If .HasFormula = False Then .Value = Application.WorksheetFunction.Clean(.Value) End If End With Next TheCell End Sub
Note that the extension .ctl is not entered in the executable form.
The control file emp_ctl.ctl file should exist in $XXCUST_TOP/bin directory (XXCUST_TOP corresponds to Custom Applications).