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.

You're not running ESP v7.2 so I can't see what the problem is :confused:

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?

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.

Quote:

---

Originally posted by jmodic
yet SQL*Loader can chew only plain ASCII files.

---

Oh Jurij, I don't think that's quite true:

Quote:

---

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.

---

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.

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

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.

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

Thank you very much every body of your help. I got my answer.

Appreciate.