RE: Re: Textfile into oracle

Roland,

At the risk of being the "extremo de la broma" to the OT lot.

The field names that you have specified in the excel file will need to be deleted anyway. You will need a control file that loads the data in to a table that has a hundred (100) fields. You will then need to save the excel file as a .csv file, and use this new "try.csv" file as your "infile". Whatever the fields actually are in the csv file, it would probably be a good idea to name the columns the same in your table..

Remember - the csv file should be data only.. No column headings are needed as SQL*Loader will just take whatever is in the file - and squirt it in to Oracle.. (Unless of course you use the SKIP clause - But lets not confuse you too much in one go eh?)

No - I don't have a sample control file.. Check out the following (available from every message that you send to this list..!)

http://www.orafaq.com/faqloadr.htm

HTH Mark

 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

-----Original Message-----
Roland.Skoldblom_at_ica.se
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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, June 06, 2002 12:03 PM

> 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: Mark Leith
  INET: mark_at_cool-tools.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).