1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Csv sqlldr

Discussion in 'SQL PL/SQL' started by JDsal, Aug 17, 2018.

  1. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    I have many csv files with many columns some have 50 to 60 + columns. Also with 100's of thousands of records. I have Linux os, with oracle db. I need a way to move csv file data to oracle load table. I have tried sqlldr for example.
    1 LOAD DATA
    2 INFILE ‘customer_address.csv’
    3 INSERT INTO TABLE emp
    4 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”’ TRAILING NULLCOLS

    However, I have an address column which uses commas which ends up seeing these commas as delimiters and will not load data into my emp table?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Is the address enclosed in quotes?
     
  3. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your control file and a few lines of the CSV file, please.
     
  5. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    I can't not seem to duplicate the previous issue. Now I am getting invalid valid number where only a decimal point is in numeric field.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's expected since '.' is a character, not a number. You might want to edit those values and make them either 0 or 0.0.
     
  7. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    Can you do that with SQLLDR/could I set enclosed "." as null or something like that?
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You could delete the single . character and the value would be NULL. The column that goes into must be one where NULL values can exist.
     
  9. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    Null values can exist. I could delete the single ., but how? I will receive this type of csv file on a continual bases. So I would like to remove prior to loading into table using a script to find specified field where that character exist. Maybe Linux script or sqlldr hard code not sure how to go about this?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way:

    Code (SQL):
    LOAD DATA
    INFILE '/home/oracle/yumpo.txt'
    INTO TABLE yumpo
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    (
      snord  "decode(:snord,'.','0.00', :snord)",
      snard
      )
    The result is:

    Code (SQL):
    SQL*Loader: Release 12.2.0.1.0 - Production ON Mon Aug 20 15:43:29 2018

    Copyright (c) 1982, 2017, Oracle AND/OR its affiliates.  ALL rights reserved.

    Path used:      Conventional
    Commit point reached - logical record COUNT 3

    TABLE YUMPO:
      3 ROWS successfully loaded.

    CHECK the log file:
      yumpo.log
    FOR more information about the LOAD.


         SNORD SNARD
    ---------- ----------------------------------------
         1 Smapple
         0 Smoople
           3.3 Smeeple
     
  11. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    Thanks, I will try and let you know. Appreciate the help
     
  12. JDsal

    JDsal Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    Still getting invalid number for the field in question for records with '.'. Also not sure if this matters but some records are null also.
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You NEED to post your control file -- no one can help you without being able to see what that contains.