Re: Excel to Oracle8i

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.