I need to connect to a odbc connection to an excel workbook.

Can anyone point me to some documentation on how this works.

Thanks

alan

Alan,
All you need to do is set up an ODBC DSN for the Excel .XLS file.
In the ODBC Admin panel - go to the Drivers tab and see if you have
Microsoft Excel Driver(*.xls) installed.
On the User DSN tab, click Add.  Then select Microsoft Excel Driver and
Finish.  Give it a name, pick the .XLS file and version.  Once that is
created, you can use the DSN like any other ODBC connection.

Paul Horan [TeamSybase]
VCI   www.twoplus.com
Springfield, MA

"Alan Lehman" <alan.lehman@midata.com> wrote in message
news:12zZvn51$GA.86@forums.sybase.com...
> I need to connect to a odbc connection to an excel workbook.
>
> Can anyone point me to some documentation on how this works.
>
> Thanks
>
> alan
>
>

Any ideas about having the user select the file.

I am planning on creating a pipeline object to allow the user to select a
file and import it to a database.

We are currently useing ole, but the I want to use the commit function of
the pipeline for perfoemance.


Paul Horan[TeamSybase] <paulh@SpamBakedBeansandSpamTwoplus.com> wrote in
message news:Iojjnu51$GA.300@forums.sybase.com...
> Alan,
> All you need to do is set up an ODBC DSN for the Excel .XLS file.
> In the ODBC Admin panel - go to the Drivers tab and see if you have
> Microsoft Excel Driver(*.xls) installed.
> On the User DSN tab, click Add.  Then select Microsoft Excel Driver and
> Finish.  Give it a name, pick the .XLS file and version.  Once that is
> created, you can use the DSN like any other ODBC connection.
>
> Paul Horan [TeamSybase]
> VCI   www.twoplus.com
> Springfield, MA
>
> "Alan Lehman" <alan.lehman@midata.com> wrote in message
> news:12zZvn51$GA.86@forums.sybase.com...
> > I need to connect to a odbc connection to an excel workbook.
> >
> > Can anyone point me to some documentation on how this works.
> >
> > Thanks
> >
> > alan
> >
> >
>
>

For your DW, be aware that you need to create a Named Range in your
spreadsheet, with the first row being the column names.  Make sure that you
use UPPERCASE for the names.  And don't mix alpha and numeric data in the
same column.  Both MS and Intersolv drivers cannot handle those.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Sybase Developer's Network: http://www.sybase.com/sdn

"Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com> wrote in
message news:Iojjnu51$GA.300@forums.sybase.com...
> Alan,
> All you need to do is set up an ODBC DSN for the Excel .XLS file.
> In the ODBC Admin panel - go to the Drivers tab and see if you have
> Microsoft Excel Driver(*.xls) installed.
> On the User DSN tab, click Add.  Then select Microsoft Excel Driver and
> Finish.  Give it a name, pick the .XLS file and version.  Once that is
> created, you can use the DSN like any other ODBC connection.
>
> Paul Horan [TeamSybase]
> VCI   www.twoplus.com
> Springfield, MA
>
> "Alan Lehman" <alan.lehman@midata.com> wrote in message
> news:12zZvn51$GA.86@forums.sybase.com...
> > I need to connect to a odbc connection to an excel workbook.
> >
> > Can anyone point me to some documentation on how this works.
> >
> > Thanks
> >
> > alan
> >
> >
>
>

I con connect to the excel profile and show the sheets in the book.  I get
the following error when i try to view tthe data

syntax error near from clause.

SELECT string,number,name from sheet1$

string,number and name are are named ranges with all caps.


Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
news:ni92Gr61$GA.203@forums.sybase.com...
> For your DW, be aware that you need to create a Named Range in your
> spreadsheet, with the first row being the column names.  Make sure that
you
> use UPPERCASE for the names.  And don't mix alpha and numeric data in the
> same column.  Both MS and Intersolv drivers cannot handle those.
>
> --
> Terry Dykstra (TeamSybase)
> Canadian Forest Oil Ltd.
> Sybase Developer's Network: http://www.sybase.com/sdn
>
> "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com> wrote in
> message news:Iojjnu51$GA.300@forums.sybase.com...
> > Alan,
> > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > In the ODBC Admin panel - go to the Drivers tab and see if you have
> > Microsoft Excel Driver(*.xls) installed.
> > On the User DSN tab, click Add.  Then select Microsoft Excel Driver and
> > Finish.  Give it a name, pick the .XLS file and version.  Once that is
> > created, you can use the DSN like any other ODBC connection.
> >
> > Paul Horan [TeamSybase]
> > VCI   www.twoplus.com
> > Springfield, MA
> >
> > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > news:12zZvn51$GA.86@forums.sybase.com...
> > > I need to connect to a odbc connection to an excel workbook.
> > >
> > > Can anyone point me to some documentation on how this works.
> > >
> > > Thanks
> > >
> > > alan
> > >
> > >
> >
> >
>
>

I also am seeing an error code -3506 when I try to pipeline

Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
news:ni92Gr61$GA.203@forums.sybase.com...
> For your DW, be aware that you need to create a Named Range in your
> spreadsheet, with the first row being the column names.  Make sure that
you
> use UPPERCASE for the names.  And don't mix alpha and numeric data in the
> same column.  Both MS and Intersolv drivers cannot handle those.
>
> --
> Terry Dykstra (TeamSybase)
> Canadian Forest Oil Ltd.
> Sybase Developer's Network: http://www.sybase.com/sdn
>
> "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com> wrote in
> message news:Iojjnu51$GA.300@forums.sybase.com...
> > Alan,
> > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > In the ODBC Admin panel - go to the Drivers tab and see if you have
> > Microsoft Excel Driver(*.xls) installed.
> > On the User DSN tab, click Add.  Then select Microsoft Excel Driver and
> > Finish.  Give it a name, pick the .XLS file and version.  Once that is
> > created, you can use the DSN like any other ODBC connection.
> >
> > Paul Horan [TeamSybase]
> > VCI   www.twoplus.com
> > Springfield, MA
> >
> > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > news:12zZvn51$GA.86@forums.sybase.com...
> > > I need to connect to a odbc connection to an excel workbook.
> > >
> > > Can anyone point me to some documentation on how this works.
> > >
> > > Thanks
> > >
> > > alan
> > >
> > >
> >
> >
>
>

string,number,name are your actual column names? They are most likely
reserved words.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Sybase Developer's Network: http://www.sybase.com/sdn

"Alan Lehman" <alan.lehman@midata.com> wrote in message
news:zI8eP571$GA.279@forums.sybase.com...
> I con connect to the excel profile and show the sheets in the book.  I get
> the following error when i try to view tthe data
>
> syntax error near from clause.
>
> SELECT string,number,name from sheet1$
>
> string,number and name are are named ranges with all caps.
>
>
> Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
> news:ni92Gr61$GA.203@forums.sybase.com...
> > For your DW, be aware that you need to create a Named Range in your
> > spreadsheet, with the first row being the column names.  Make sure that
> you
> > use UPPERCASE for the names.  And don't mix alpha and numeric data in
the
> > same column.  Both MS and Intersolv drivers cannot handle those.
> >
> > --
> > Terry Dykstra (TeamSybase)
> > Canadian Forest Oil Ltd.
> > Sybase Developer's Network: http://www.sybase.com/sdn
> >
> > "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com> wrote
in
> > message news:Iojjnu51$GA.300@forums.sybase.com...
> > > Alan,
> > > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > > In the ODBC Admin panel - go to the Drivers tab and see if you have
> > > Microsoft Excel Driver(*.xls) installed.
> > > On the User DSN tab, click Add.  Then select Microsoft Excel Driver
and
> > > Finish.  Give it a name, pick the .XLS file and version.  Once that is
> > > created, you can use the DSN like any other ODBC connection.
> > >
> > > Paul Horan [TeamSybase]
> > > VCI   www.twoplus.com
> > > Springfield, MA
> > >
> > > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > > news:12zZvn51$GA.86@forums.sybase.com...
> > > > I need to connect to a odbc connection to an excel workbook.
> > > >
> > > > Can anyone point me to some documentation on how this works.
> > > >
> > > > Thanks
> > > >
> > > > alan
> > > >
> > > >
> > >
> > >
> >
> >
>
>

You are loosing time with ODBC. Do a workaround with a Text or CSV file
format.
On several occasions I have found PB data exchange with Microsoft 
products highly unreliable.

I created a new book and sheet with EMPLOYEE,RATE, AND HOURS

CREATE NAMED RANGES
CREATED A NEW ODBC AND PROFILE.
I still get the error.

I set the named range one cell at a time.  Is this correct?

alan
Alan Lehman <alan.lehman@midata.com> wrote in message
news:zI8eP571$GA.279@forums.sybase.com...
> I con connect to the excel profile and show the sheets in the book.  I get
> the following error when i try to view tthe data
>
> syntax error near from clause.
>
> SELECT string,number,name from sheet1$
>
> string,number and name are are named ranges with all caps.
>
>
> Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
> news:ni92Gr61$GA.203@forums.sybase.com...
> > For your DW, be aware that you need to create a Named Range in your
> > spreadsheet, with the first row being the column names.  Make sure that
> you
> > use UPPERCASE for the names.  And don't mix alpha and numeric data in
the
> > same column.  Both MS and Intersolv drivers cannot handle those.
> >
> > --
> > Terry Dykstra (TeamSybase)
> > Canadian Forest Oil Ltd.
> > Sybase Developer's Network: http://www.sybase.com/sdn
> >
> > "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com> wrote
in
> > message news:Iojjnu51$GA.300@forums.sybase.com...
> > > Alan,
> > > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > > In the ODBC Admin panel - go to the Drivers tab and see if you have
> > > Microsoft Excel Driver(*.xls) installed.
> > > On the User DSN tab, click Add.  Then select Microsoft Excel Driver
and
> > > Finish.  Give it a name, pick the .XLS file and version.  Once that is
> > > created, you can use the DSN like any other ODBC connection.
> > >
> > > Paul Horan [TeamSybase]
> > > VCI   www.twoplus.com
> > > Springfield, MA
> > >
> > > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > > news:12zZvn51$GA.86@forums.sybase.com...
> > > > I need to connect to a odbc connection to an excel workbook.
> > > >
> > > > Can anyone point me to some documentation on how this works.
> > > >
> > > > Thanks
> > > >
> > > > alan
> > > >
> > > >
> > >
> > >
> >
> >
>
>

with a text file, i can not control the commit to db.  I may have large
files.


Andrzej D Zaleski <gmsaxz@friko7.onet.pl> wrote in message
news:01bfd7bd$8c86d2a0$e9c8c8c8@pentium233.anzasoft.com...
> You are loosing time with ODBC. Do a workaround with a Text or CSV file
> format.
> On several occasions I have found PB data exchange with Microsoft
> products highly unreliable.

The whole area of rows and columns that contain your data and that represent
one table you include in the named range.
This named range is what will show as the tablename in PB.  The first row in
this named range must contain the column names.
For more details, see the Connecting to your DB manual.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Sybase Developer's Network: http://www.sybase.com/sdn

"Alan Lehman" <alan.lehman@midata.com> wrote in message
news:alqdZF81$GA.204@forums.sybase.com...
> I created a new book and sheet with EMPLOYEE,RATE, AND HOURS
>
> CREATE NAMED RANGES
> CREATED A NEW ODBC AND PROFILE.
> I still get the error.
>
> I set the named range one cell at a time.  Is this correct?
>
> alan
> Alan Lehman <alan.lehman@midata.com> wrote in message
> news:zI8eP571$GA.279@forums.sybase.com...
> > I con connect to the excel profile and show the sheets in the book.  I
get
> > the following error when i try to view tthe data
> >
> > syntax error near from clause.
> >
> > SELECT string,number,name from sheet1$
> >
> > string,number and name are are named ranges with all caps.
> >
> >
> > Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
> > news:ni92Gr61$GA.203@forums.sybase.com...
> > > For your DW, be aware that you need to create a Named Range in your
> > > spreadsheet, with the first row being the column names.  Make sure
that
> > you
> > > use UPPERCASE for the names.  And don't mix alpha and numeric data in
> the
> > > same column.  Both MS and Intersolv drivers cannot handle those.
> > >
> > > --
> > > Terry Dykstra (TeamSybase)
> > > Canadian Forest Oil Ltd.
> > > Sybase Developer's Network: http://www.sybase.com/sdn
> > >
> > > "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com>
wrote
> in
> > > message news:Iojjnu51$GA.300@forums.sybase.com...
> > > > Alan,
> > > > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > > > In the ODBC Admin panel - go to the Drivers tab and see if you have
> > > > Microsoft Excel Driver(*.xls) installed.
> > > > On the User DSN tab, click Add.  Then select Microsoft Excel Driver
> and
> > > > Finish.  Give it a name, pick the .XLS file and version.  Once that
is
> > > > created, you can use the DSN like any other ODBC connection.
> > > >
> > > > Paul Horan [TeamSybase]
> > > > VCI   www.twoplus.com
> > > > Springfield, MA
> > > >
> > > > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > > > news:12zZvn51$GA.86@forums.sybase.com...
> > > > > I need to connect to a odbc connection to an excel workbook.
> > > > >
> > > > > Can anyone point me to some documentation on how this works.
> > > > >
> > > > > Thanks
> > > > >
> > > > > alan
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

THANKS.  I got it now.
Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
news:PUxKUg81$GA.86@forums.sybase.com...
> The whole area of rows and columns that contain your data and that
represent
> one table you include in the named range.
> This named range is what will show as the tablename in PB.  The first row
in
> this named range must contain the column names.
> For more details, see the Connecting to your DB manual.
>
> --
> Terry Dykstra (TeamSybase)
> Canadian Forest Oil Ltd.
> Sybase Developer's Network: http://www.sybase.com/sdn
>
> "Alan Lehman" <alan.lehman@midata.com> wrote in message
> news:alqdZF81$GA.204@forums.sybase.com...
> > I created a new book and sheet with EMPLOYEE,RATE, AND HOURS
> >
> > CREATE NAMED RANGES
> > CREATED A NEW ODBC AND PROFILE.
> > I still get the error.
> >
> > I set the named range one cell at a time.  Is this correct?
> >
> > alan
> > Alan Lehman <alan.lehman@midata.com> wrote in message
> > news:zI8eP571$GA.279@forums.sybase.com...
> > > I con connect to the excel profile and show the sheets in the book.  I
> get
> > > the following error when i try to view tthe data
> > >
> > > syntax error near from clause.
> > >
> > > SELECT string,number,name from sheet1$
> > >
> > > string,number and name are are named ranges with all caps.
> > >
> > >
> > > Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote in message
> > > news:ni92Gr61$GA.203@forums.sybase.com...
> > > > For your DW, be aware that you need to create a Named Range in your
> > > > spreadsheet, with the first row being the column names.  Make sure
> that
> > > you
> > > > use UPPERCASE for the names.  And don't mix alpha and numeric data
in
> > the
> > > > same column.  Both MS and Intersolv drivers cannot handle those.
> > > >
> > > > --
> > > > Terry Dykstra (TeamSybase)
> > > > Canadian Forest Oil Ltd.
> > > > Sybase Developer's Network: http://www.sybase.com/sdn
> > > >
> > > > "Paul Horan[TeamSybase]" <paulh@SpamBakedBeansandSpamTwoplus.com>
> wrote
> > in
> > > > message news:Iojjnu51$GA.300@forums.sybase.com...
> > > > > Alan,
> > > > > All you need to do is set up an ODBC DSN for the Excel .XLS file.
> > > > > In the ODBC Admin panel - go to the Drivers tab and see if you
have
> > > > > Microsoft Excel Driver(*.xls) installed.
> > > > > On the User DSN tab, click Add.  Then select Microsoft Excel
Driver
> > and
> > > > > Finish.  Give it a name, pick the .XLS file and version.  Once
that
> is
> > > > > created, you can use the DSN like any other ODBC connection.
> > > > >
> > > > > Paul Horan [TeamSybase]
> > > > > VCI   www.twoplus.com
> > > > > Springfield, MA
> > > > >
> > > > > "Alan Lehman" <alan.lehman@midata.com> wrote in message
> > > > > news:12zZvn51$GA.86@forums.sybase.com...
> > > > > > I need to connect to a odbc connection to an excel workbook.
> > > > > >
> > > > > > Can anyone point me to some documentation on how this works.
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > alan
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

You mean you can commit a transaction to Excel?

Well well well:-)

I would not bet my head on it, but just try to rollback 
your writes.

Don't be disappointed too much. ODBC also provides
drivers to text files, dBase III, etc. none of which supports 
transactions.

Alan Lehman <alan.lehman@midata.com> napisa�(a) w artykule 
> with a text file, i can not control the commit to db.  I may have large

no.  With the odbc solution, I can create a pipline object that controls the
commits to the destination db.(not excel)
Excel is going to be used for the input.


Andrzej D Zaleski <gmsaxz@friko7.onet.pl> wrote in message
news:01bfd850$f5082c00$e9c8c8c8@pentium233.anzasoft.com...
> You mean you can commit a transaction to Excel?
>
> Well well well:-)
>
> I would not bet my head on it, but just try to rollback
> your writes.
>
> Don't be disappointed too much. ODBC also provides
> drivers to text files, dBase III, etc. none of which supports
> transactions.
>
> Alan Lehman <alan.lehman@midata.com> napisa�(a) w artykule
> > with a text file, i can not control the commit to db.  I may have large
>