Re: Excel to Oracle8i

There was also a program called Oraxcel that allowed you to connect to the DB from within excel and do inserts updates etc. The name has changed but a search should turn it up

Etienne Laverdiere wrote in message ...
>Hi, I used perl to do that.
>
>Search for the discussion around :
>Parsing a Excell table - or - a "Tab New_Line" text file?
>in comp.lang.perl.misc
>
>Here's a resume :
>
>Try the OLE manner, it is lest risky, but the ODBC way is more portable.
>You must use the perl package DBI.
>
>Bob Walton wrote:
>>
>> I note one other slight piece of weirdness: the type of a field is
>> apparently defined by the type of data in the entry on the second line
>> of the range. Mixed numbers and text in a given column don't work.
>
>That is the default behaviour (or actually the first 8 rows), but it is
>configurable. You can have Excel determine the data type based on a
>user definable number of rows. From the ODBC driver help documents
>(included with ODBC drivers as part of windoze):
>
><quote>
>Rows to Scan:
>
>The number of rows to scan to determine the data type of each column.
>The data type is determined given the maximum number of kinds of data
>found. If data is encountered that does not match the data type guessed
>for the column, the data type will be returned as a NULL value.
>
>For the Microsoft Excel driver, you may enter a number from 1 to 16 for
>the rows to scan. The value defaults to 8; if it is set to 0, all rows
>are scanned. (A number outside the limit will return an error.)
>
>For the Text driver, you may enter a number from 1 to 32767 for the
>number of rows to scan; however, the value will always default to 25. (A
>number outside the limit will return an error.)
>
>To set this option dynamically, use the MAXSCANROWS keyword in a call to
>SQLConfigDataSource.
></quote>
>
>> That certainly makes the DBI technique a less appealing method of
>> accessing spreadsheets (as compared to OLE).
>
>While I am no fan of M$, I think you may be a bit premature in your
>assessment. ODBC is one of their more complete implementations and it is
>richer than it appears at first glance.
>
>--
>Jeff
>
>
>
>
>-->
>
>
>"Etienne Laverdiere" <info_at_digitaltango.com> wrote in message
>news:R3tr5.208502$Gh.4105314_at_news20.bellglobal.com...
>> Hi all,
>> Thanks Bob and Jeff,
>>
>> I read the last post of Jeff Zucker about the OBDC configuration to avoid
 a
>> strange Data Typing. However I did not understood many things. I still
 would
>> rather prefer to use the DBI: OBDC package.
>> I didn't correct my datatyping bug right now (neither the fact that it
>> cannot read more than 255 characters, plus all my "number fields" are
>> interpreted with a additional 0 following a dot.)
>>
>> I am designing a quite large Intranet and I already use DBI:OBDC for the
>> entire query to a database. (My prototype is working on Access right
 now).
>> For a reason of portability, it would be better to minimize the ways I
 query
>> my database.
>>
>> Anyway. I will try the WIN32 solution if you think that the only way.
>>
>>
>> Best Regards for all,
>>
>> Etienne Laverdiere
>> Montreal
>>
>>
>> Here's the code I am working on :
>>
>>
>> #example of using DBI with Excel
>> use DBI;
>> #use Data::Dumper;
>>
>> my $drv = 'ODBC:driver=Microsoft Excel Driver (*.xls)';
>> my $dir = 'd:/data/base_prod.xls'; #an Excel file
>> my $table = '[Sheet1$]'; #a range name
>> my $dbh = DBI->connect( "dbi:$drv;Dbq=$dir",,,{RaiseError=>1} )
>> or die $DBI::errstr;
>> $dbh->{LongReadLen}=100000; # I tried that
>> $sth->{LongReadLen} =100000; # and that
>> my $sth = $dbh->prepare("select * from $table");
>> my $names=$sth->{NAME};
>>
>> ... # printing @$names
>>
>> print "dbh: " . $dbh->{LongReadLen} . "\n<br><br><br><br>";
>> print "sth: " . $sth->{LongReadLen} . "\n<br><br><br><br>";
>> my $array_ref = $dbh->selectall_arrayref($sth);
>>
>> ## and printing everything.. to field larger than 255 can be viewed. And
 it
>> stops the databases' parsing.
>>
>>
>>
>> "Bob Walton" <bwalton_at_rochester.rr.com> wrote in message
>> news:39ADB61E.9F4E4AC_at_rochester.rr.com...
>> > Etienne Laverdiere wrote:
>> > >
>> > > Yes you are right. I have the same problem now.
>> > > "Mixed numbers and text in a given column don't work.!"
>> > > I must find a way to import any data of any type, like reading for
 all
 field
>> > > 'type=>text' for a number or a real text.
>> > > I will continue searching. If you have a solution don't hesitate to
 write it
>> > > here.
>> > >
>> > > Best Regards,
>> > >
>> > > Etienne Laverdiere
>> > > Montreal
>> > >
>> > ...
>> > Etienne, please see my reply to your note time-stamped Saturday at
 11:51
>> > PM EDT for how to do what you want to do using OLE. That method
 returns
>> > whatever the contents of each cell is. If you can't find that note,
>> > here is the code:
>> >
>> > use Win32::OLE;
>> > use Data::Dumper;
>> > # use existing instance if Excel is already running
>> > eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
>> > die "Excel not installed" if $@;
>> > die "Excel not running" unless defined $ex;
>> > $wb=$ex->Workbooks(1);
>> > $ws=$wb->Worksheets(1);
>> > $ref=$ws->Range("B2:E10")->Value;
>> > print Dumper($ref);
>> >
>> > That uses the first worksheet of the first workbook.
>> > --
>> > Bob Walton
>>
>>
>
>
>
>
>
><snalion_at_hotmail.com> wrote in message news:8pu47u$pqk$1_at_nnrp1.deja.com...
>> Hi. I need help to export data from Excel into Oracle8i. I have no clue
>> in how to do this. Please help. Any help or hints would be appreciated.
>> Thank You
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>
Received on Tue Sep 19 2000 - 10:24:05 CDT