Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Textfile into oracle
Roland,
If you are saying that you have 100 columns in the spreadsheet but you only
want to copy 9 columns into a table with 9 fields then you have 2 choices.
Reduce the spreadsheet to only 9 columns (by copying to another worksheet)
and then saving as a CSV file and importing by sqlloader. I always think it
best to limit the data at the source end before transfer rather than
afterwards.
The other option is to use sqlloader to only pull out the fields that you want. I have had a quick look at the online manuals but cannot see how to do that unless you have a fixed position file which I assume is not the case (the syntax is below just in case)
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'
1) REPLACE
2) INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL)
There is a good sqlloader web-site (mentioned on this list a couple of months ago). Unfortunately I have just started a new contract and do not have internet access as yet. Do a search on Google
HTH John
-----Original Message-----
Sent: 06 June 2002 15:28
To: Multiple recipients of list ORACLE-L
Could anyone please give me an example on how the sqlloader script would look like , How many fields would it be,? I cant find anything ofthis in themanual. I have 100 fields in the excel file but only 9-10 field names...
Thanks in advance
Roland
"Alexandre Gorbatchev" <alexandre.gorbatchev_at_avermann.de>@fatcity.com den 2002-06-06 03:03 PST Sänd svar till ORACLE-L_at_fatcity.com
Sänt av: root_at_fatcity.com
Till: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Kopia:
Hello Roland,
The easiest way is to save it from excel as flat comma-separated text file. Then you should use Oracle SQL*Loader tool and yes you will have to pre-create the table with all fields you need. (99,9% that's what you need)
You cannot import .xls file into Oracle database directly with standard oracle tools. I mean with structure, not into the lob filed.
You could access file from PL/SQL and write your own import procedure. (not
a trivial task and not feasible probably :-)
Can use odbc from Excel directly.
May be there are some third-party tools to import.
Alexandre
> Hallo,
>
> I have this excelfile. Is it possible to import this into an oracle table.
This file is supposed to be located on unix machine from the beginning and
the import into an oracle table would be done from unix. How should I name
the fields in the oracle
> table. I mean should I use the 9-10 different field names in the
excelfile. There is at least 100 different columns in this excelfile. Is it
possible to import a file like that into oracle table,
>
> Please help me with some hints.
> (See attached file: try.xls)
>
> Thanks in advance
>
> Roland
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: alexandre.gorbatchev_at_avermann.de Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Roland.Skoldblom_at_ica.se Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: John.Hallas_at_vodafone.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jun 06 2002 - 11:53:29 CDT