Excel ODBC driver

[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?

[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.

[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.

[Wrangler]

Would be cool if you can figure it out. Keep us posted.

[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.

[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.

[datadon]

Fantastic! works great. a new world of opportunity!

thanks

[Wrangler]

Gotta love that DBPro. Learned something here. I have a couple of applications that can benefit from it. Thanks, Dave!

[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

[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.

[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..

[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"

[yanzco]

it worked now..
the ! did the trick..
:O

thanks gaev!!