Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    24

    Unanswered: help with SQL Loader

    Hello,

    I am hoping some one with some SQL Loader expertise can help me figure out how to load some data from a flat file. Here is a sample of the data:
    EMAIL\02/01/2004 00:02:34\<sender@domain>\<recipient@domain> \readme.zip\deleted\virus_name

    As you can see the fields are terminated by a "\".

    My control file looks like this:
    load data
    infile 'F:\SQLLOAD\LOGS\2004.04.01.TXT'
    replace
    into table virusinfo
    fields terminated by '\'
    (
    source_type CHAR(10)
    ,date_time TIMESTAMP
    ,sender CHAR(1000)
    ,recipient CHAR(1000)
    ,attachment CHAR(100)
    ,status CHAR(100)
    ,virus_name CHAR(100)
    )

    I get an error saying quoted string missing closing quote. I think the error may be caused by the "<" and ">" around the email address.

    Anyone have suggestions how to get this load to work?

    For your reference my table definition is:
    Name Null? Type
    ------------------------ ------- ----------- SOURCE_TYPE VARCHAR2(10)
    DATE_TIME DATE
    SENDER VARCHAR2(1000)
    RECIPIENT VARCHAR2(1000)
    ATTACHMENT VARCHAR2(100)
    STATUS VARCHAR2(100)
    VIRUS_NAME VARCHAR2(100)

    Thanks

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    did you try:
    PHP Code:
    load data
    infile 
    'F:\SQLLOAD\LOGS\2004.04.01.TXT'
    replace
    into table virusinfo
    fields terminated by 
    '\' optionally enclosed by '<' and '>
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    24
    I just tried that and now I recieve a different error message:
    SQL*Loader-350: Syntax error at line 5.
    Illegal combination of non-alphanumeric characters
    fields terminated by '\' optionally enclosed by '<' and '>'

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I may be wrong, but I believe that you are having a problem with the 2nd column of data. It is coming in as CHAR and you are defining it as a timestamp ... It is a DATE in your table ... You may need to mask the 2nd column to match up with the date format ... ie;

    col2 date "to_date(:col2, 'mm/dd/yyyy hh:mi:ss')" nullif col2 = ''

    or something to that effect ...

    HTH
    Gregg

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Good catch Gregg!

    I think this is where you are getting the error.
    Changing your table definition to timestamp could work

    if you want to keep the DATE on the table-column you could try:
    PHP Code:
    date_time date "to_date(substr(:date_time,1,16),'mm/dd/yyyy hh:mi')" 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    24
    Thank you for the suggestions.

    For some reason, my problem seemed to stem from the \ delimiter. I changed it to a ',' to try and I was able to load all my data. I will look into why the \ wasn't working but in the mean time I am happy that the data is in table.

    Here is the control file that I ended up with:

    load data
    infile 'F:\SQLLOAD\LOGS\2004.04.01.TXT'
    append
    into table virusinfo
    fields terminated by ','
    (
    source_type
    ,date_time date "MM/DD/YYYY HH24:MIS"
    ,sender
    ,recipient
    ,attachment
    ,status
    ,virus_name
    )

    Thanks again for the help,
    LeStop

Posting Permissions

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