Page 1 of 1

Excel ODBC driver

PostPosted: Thu May 28, 2009 5:23 am
by datadon
Does anyone know what driver is required to connect up NBDBPro to an excel (2003) file? I've tried a couple that don't seem to work.

Or can it not be done?

PostPosted: Thu May 28, 2009 8:07 am
by Wrangler
Excel files aren't databases, so I would imagine it wouldn't work with dbpro. I believe Excel 2003+ now uses xml format. I seem to recall there being an excel plugin out there, but I can't remember who created it.

PostPosted: Thu May 28, 2009 9:05 am
by datadon
The help file kind of makes it sound like it can be done. I think I need to use an ADO rather than a ODBC. Im just experimenting for now.

PostPosted: Thu May 28, 2009 9:07 am
by Wrangler
Would be cool if you can figure it out. Keep us posted.

PostPosted: Thu May 28, 2009 9:22 am
by Gaev
Dave had told me that NeoBookdbPro can be used with Excel files ... but I can't find the email where he provided details.

Soon as he gets his eyes (and rest of his body) from out of the alligator swamp (of false positive virus identification), I am sure he will reply here.

PostPosted: Thu May 28, 2009 11:17 am
by Neosoft Support
You can open an Excel worksheet in DBPro like this:

dbpOpenDatabase "MyDB" "filename.xls"

You don't need to worry about the ODBC driver or connection string. DBPro will take care of it automatically.

Excel stores its worksheet table names a little differently, so you might need to use the following to see what they are before opening a table:

dbpGetTableNames "MyDB" "[#13]" "[WorkSheets]"

You might want to make a copy of the worksheets before playing with them in DBPro. They can be opened without any problems, but they behave like a database rather than a spreadsheet. Formulas won't work.

PostPosted: Fri May 29, 2009 7:26 am
by datadon
Fantastic! works great. a new world of opportunity!

thanks

PostPosted: Fri May 29, 2009 7:52 am
by Wrangler
Gotta love that DBPro. Learned something here. I have a couple of applications that can benefit from it. Thanks, Dave!

PostPosted: Sat May 30, 2009 1:06 pm
by domino
Gotta love that DBPro....

I`ll echo that...

Just about to finish my first real DBPro enabled application - and I`m stunned by just how good the DBPro plugin is - Nice work NeoSoft!!

Cheers
Dave

PostPosted: Sat May 30, 2009 4:10 pm
by datadon
Not sure if this is worth pointing out, but I will anyway. No only can you use this plugin with excel, but also with csv files in the same way. It will treat them just like any other database. They are simple text files.

csv files are comma-separated-values and look like this:

field1,field2,field3,field4
value1,value2,value3,value4
value1,value2,value3,value4
value1,value2,value3,value4
value1,value2,value3,value4

The first row is a header row and will create column names.
value1 becomes a column named field1
value2 becomes a column named field2 etc.
each of the four values rows are records in the database.


You can easily write these type of text files using Neobook. Many current programs export or write to csv also. The plugin allows you to do many things with this data, sorting, query, adding, report writer, on and on.

Think deep. The possibilities are endlesssssssssss.

Re: Excel ODBC driver

PostPosted: Thu Mar 16, 2017 4:20 am
by yanzco
i tried this.
but not working..

dbpOpenDatabase "XLS" "E:\EVA Desktop\Project Marc\SourceDB\XLS.xls"

dbpGetTableNames "XLS" "[#13]" "[WorkSheets]"
dbpOpenTable "XLS" "[Worksheets]" ""



it says cant open XXX$

i only have 1 table in the excel..

Re: Excel ODBC driver

PostPosted: Thu Mar 16, 2017 5:07 am
by Gaev
yanzco:

dbpGetTableNames "XLS" "[#13]" "[WorkSheets]"
dbpOpenTable "XLS" "[Worksheets]" ""

it says cant open XXX$

i only have 1 table in the excel..

You didn't say what the name of your "sheet in the xls file" was ... is it XXX ?

In any case, [WorkSheets] gets created as an arrayed variable ... so try ...
Code: Select all
dbpOpenTable "XLS" "[Worksheets1]" ""


If that doesn't work, tell us what the value of [WorkSheets1] is ... and the name of your one sheet.

Also, it might help to place an exclamation mark in front of your file name ...
Code: Select all
dbpOpenDatabase "XLS" "!E:\EVA Desktop\Project Marc\SourceDB\XLS.xls"

Re: Excel ODBC driver

PostPosted: Thu Mar 16, 2017 11:18 am
by yanzco
it worked now..
the ! did the trick..
:O

thanks gaev!! :)