-
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
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
-
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?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|