Unanswered: loading excel files into an oracle database
I have a bit of a problem I hope someone acan help me out with.... Im required to load Excel files into an Oracle database using SqlLoader, but I havent a clue how to do this! I know i have to convert them to .CSV files first, but where do i go from there? Please help...I need details!
Thanks a million
saoirse - you need 4 steps to load EMPTY Oracle tables with data via SQL*Loader. If the tables are not empty, use create a Visual Basic program to load the data.
step 1 -- Create a CSV file.
Make sure that text lengths are less than or equal to Oracle column, numbers fit into the Oracle number fields, booleans are in the format Oracle expects, and dates are properly formatted. Errors in data typing or size will cause SQLLoader to fail. Create the .csv file. Make sure all text items are surrounded by double quotes, and items are separated by commas.
step 2 -- Create a control file.
Use a text editor to create the control file, which has CR and LF at end of each line. Oracle columns are named in order of data found in the CSV file. Save the control file with a .CTL extension. .
INTO TABLE <name of Oracle Table>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
step 3 -- Combine CTL and CSV file.
If you do NOT want to add the CSV file to the end of the control file, then change INFILE * statement to read INFILE <csv filename>, DROP the BEGINDATA statement, and bypass this step. Add the CSV file to the end of the CTL file, on the line after the BEGINDATA statement.
Step 4 -- Invoke the SQL*Loader
The command to invoke the SQL*Loader is OS and Oracle version dependent. Check your oracle manual for correct command.
I did a search regarding SQLloader Errors and came across this thread. I have been in the process of converting an Excel file to our Oracle 8i DB. I followed the same process as outlined above. After getting a few messages regarding the Control file I finally ironed those problems out- I THINK...
I say "I think" because I'm now getting a username/password error. I've checked and double checked and the username and password have permission to insert on both the table and schema I'm working on.