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!

control file help

Discussion in 'General' started by dsub42, Apr 11, 2010.

  1. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Hi Im importing a simple data set using sql loader, below is the controll file im using...

    The field named 'CustName' imports all the records but surround them in the oracle table with single quotes ... eg 'Dave' .. not Dave.. how can i alter the control file to remove these sinngle quotes when the data is imported...



    Also....



    The field named 'registered' contains data as 'true' or 'false' in the csv... i need to import this into oracle as 1 for true and 0 for false... how can i alter the control file to do this?



    my control file is as follows:



    OPTIONS (ERRORS=999)

    LOAD DATA

    INFILE "c:\test.csv"

    BADFILE "c:\test.bad"

    INSERT

    INTO TABLE test

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

    (CustName,
    Registered)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I need to see the data file you're using as well as a create table script for the destination table.
     
  3. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    I have managed to use decode to turn false/true into y/n and also a substr function to remove enclosing single speach marks...

    the next problem I am facing is commas in the address field..

    do you have any suggestions how to deal with the commas or alternative soloutions to the others?

    2 rows of data are as follows... it is representative of the entire data set.. (comma in second line of address 1)

    1,'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,

    2,'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

    The above lines are being sent into a different table:

    CustiD - Number(1)
    Suffix - VarChar2 (3)
    Fname -VarChar2 (20)
    Lname -VarChar2 (20)
    CustSpecNo - Number(4)
    Address1 -VarChar2 (100)
    Address2 -VarChar2 (100)
    Address3 -VarChar2 (100)
    Address4 -VarChar2 (100)
    Datejoined - Date
    ...

    I want to use sql loader to get them into this table... having problems with data and the commas now.... '24,arly Street'

    Any ideas?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You shoudln't need substr() to replace the single quotes as your strings should be delimited by double quotes as you instructed SQL*Loader in your control file; convert the ' to " and your single quote problem disappears. So will your embedded comma issue as they will be part of known string data. This:

    'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,
    'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

    should be written like this:

    "Mr","John L","Davis","1234","19 belsover Street","dales court","pembrokshire","uk",01/04/2000,1.345,True,
    "Mrs","Christine","Dunn","3241","24,arly Street","spanish gardens","pembrokshire","uk",01/04/2000,3.454,True,

    Fix your data file to fix your 'problems'.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No need to change the Data file (.csv)

    You just have to change the control file slightly.

    Code (SQL):

    OPTIONS (ERRORS=999)
    LOAD DATA
    INFILE "c:\test.csv"
    BADFILE "c:\test.bad"
    INSERT
    INTO TABLE test
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'" TRAILING NULLCOLS
     
     
  6. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    I fixed it by using OPTIONALLY ENCLOSED BY "'" instead of '"' but if we take the following examples...

    '24,arly street'
    '18 matthew's close'

    ... one string including an embedded single quote and one an embeddedcomma

    if i dont use OPTIONALLY ENCLOSED BY "'" ... and i do use a substring function then "24, arly street" is not imported because of the embedded comma but "18 matthew's close" is imported correctly and includes the embedded single quote.

    if i do use OPTIONALLY ENCLOSED BY "'" then "24, arly stret" is imported correctly, however this time the row that contained "18 matthew's close" is moved to the bad file because that embedded single speach mark breaks the file.

    so i think the only way i can make sure that commas and embedded single quotes are imported into the column is by (and correct me if im wrong).. if i do use OPTIONALLY ENCLOSED BY "'" ... and then on that column in the control file put something in to escape that embedded single quote?

    any idea how I would do this? escaping embedded single quotes that is?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Or do what I told you earlier and enclose your strings in " and then the apostrophes are imported correctly and the commas are also imported correctly. This is why I did not suggest you change your control file on the string enclosure character.
     
  8. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    but say this was not an option, how can i use a string function etc to escape the ingle quotes inbetween the enclosing single quotes?

    im just trying to learn and play around, any help you could give me would greatly be appriciated
     
  9. dsub42

    dsub42 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    The problem is that I have been given a csv file with about 20 columns, .. all the strings are enclosed in single quotes.. so theres not alot I can do to convert these into double quotes...

    either way lets say i didnt convert the single quotes, is there not a function that will escape sinlge quotes in the string?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot. To preserve the apostrophes you need to change the string enclosure character to " because sql*loader takes every occurrence of the delimmiter pairs to define a string and your apostrophe terminates the string it is part of when you tell the utility to use ' to enclose strings. You have already discovered this behaviour. You could, of course, change all of your apostrophes to " then go through the data in the table and convert them back with translate() but you'd still be changing characters in your data file and it appears that changing characters in your data file is not an option simply because you don't want it to be. Yes, there are other options such as writing an OCI/OCCI program to load the data in whatever format you decide you want, writing a Pro*C program to do the load, create an external table and use insert statements to load your data from the external table to your 'permanent' table but using an external table would cause you to run into this same problem as external tables are processed by sql*loader.
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You use notepad or vim to edit the file, it's a fairly simple task. That you apparently don't want to do this is a different matter. I have done this on flat files before when they didn't meet the criteria necessary to properly load the data.