Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: loading excel files into an oracle database

    Hi there!
    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

  2. #2
    Join Date
    Jul 2001
    Location
    NC
    Posts
    102
    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. .

    LOAD DATA
    INFILE *
    INTO TABLE <name of Oracle Table>
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (<tableColumn2>,<tableColumn4>,<tableColumn3>,<tab leColumn1>)
    BEGINDATA

    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.

    SQLLDR USERID=<pw/uid> CONTROL=<ctl filename.CTL> LOG=<ctl filename.LOG>


    The log file will report your success or failure and the reason for the failure.

  3. #3
    Join Date
    Jun 2002
    Posts
    2

    Thanks

    Thanks a million for your help - this looks pretty good. so im going to give it a try now. Cheers!

  4. #4
    Join Date
    Nov 2002
    Posts
    2

    Angry

    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.

    Can anyone tell me what might be the problem?

    Thanks a million...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •