Fields Terminated By '|' Optionally Enclosed By '"' Trailing Nullcols
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Fields Terminated By '|' Optionally Enclosed By '"' Trailing Nullcols

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    Fields Terminated By '|' Optionally Enclosed By '"' Trailing Nullcols

    Hello!,
    I have a file *.xls with pipe '|' terminated and *.ctl also with FIELDS TERMINATED BY '|'. Question is, Can I load the .xls to oracle table?. I have tried but no positive result.

    Please advise with you suggestion.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You're not running ESP v7.2 so I can't see what the problem is

    Is the .xls an excel file or a text file (flat file) with an unusual extension? You can only load a flat file (check by opening it in notepad or similar).

    What does the .log file say? Is there a .bad file as well? etc etc etc?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What do you mean by *.xls file? Is it MS Excell file or what? If yes, then you can not load this file with SQL*Loader. Excel files (.xls files) are binary files, yet SQL*Loader can chew only plain ASCII files.

    You have to save your Excell file in some other "plain ASCII" format, like *.csv or fixed length fields file (*.txt, *.prn, ....) and then load it with SQL*Loader.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by jmodic
    yet SQL*Loader can chew only plain ASCII files.
    Oh Jurij, I don't think that's quite true:

    SQL*Loader supports different character encoding schemes (called character sets, or code pages). SQL*Loader uses features of Oracle's globalization support technology to handle the various single-byte and multibyte character encoding schemes available today.

    See Also:
    Oracle9i Database Globalization Support Guide

    In general, loading shift-sensitive character data can be much slower than loading simple ASCII or EBCDIC data.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Right, I agree - bad word choosing from my side. I should have think of at least EBCDIC character format, if nothing else! So instead stating that "it can handle only ASCII files" I should have said "it can handle only plain TEXT files that maight use various character-encoding schemes". And even this might not be totaly accurate, as it actually "partialy" supports binary data too ("native" datatipes!). But those non-character datatypes (*******, FLOAT, DOUBLE, ....) in their binary format can not be delimited, i.e. they either are (OS-dependant) predefined fixed length or you must specify their length, but can never be sepparated with delimiters.

    Anyway, I don't think there is any way you could directly load binary xls file with SQL*Loader.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jul 2001
    Posts
    334
    Hi,
    Thanks for the response.

    let me more clear what type of data file I have. The data file is in regular Excell sheet/file with file extension .xls e.g(myfile.xls) and terminated by by '|' see below

    1ooo|Item1|345.90
    2000|Item2|100.00

    The above data has saved in 5 cell
    1000 = 1 cell
    | = 1 cell
    Item1 = 1 cell
    | = 1 cell
    345.90 = 1 cell

    now sql*loader with FIELDS TERMINATED BY '|'

    Hope this is clear.

    Thanks in advance

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Open your myfile.xls in a normal text editor (notpad.exe, for example) and see what you get! Can you read it "smoothly"? No? So can't Oracle. That's what we've been telling you about SQL*Loader not being able to read binary files.

    Hope this is clear.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    Hi

    My unserstanding is that you have only three actual values colums
    That is (col1=1000,col2=Item1,Col3=345.90)
    and the other two columns are only just a seperater. That is '|'.

    If you want to load only the above three columns in the table you can do the following steps:

    Step1: Remove the columns 2 and 4. i mean to say is the value of the columns is "|".

    Step2: Save the file as myfile.csv and filetype should be CSV (Comma Seperated Value)

    Step3: now run your loader program something like myfile.ctl below:
    myfile.ctl
    ==========
    LOAD DATA
    INFILE 'myfile.csv'
    INTO TABLE your_Table_Name_Here
    FIELDS TERMINATED BY ","
    (COL1 CHAR ,
    COL2 CHAR ,
    COL3 INTEGER EXTERNAL)

    Hope this may help you..

    AnandharajA
    Anandharaj
    a_anandharaj@yahoo.com

  9. #9
    Join Date
    Jul 2001
    Posts
    334
    Thank you very much every body of your help. I got my answer.

    Appreciate.

Posting Permissions

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


Click Here to Expand Forum to Full Width