Microsoft Excel can open DBASE IV, i.e. *.dbf files, but...

[jj2007]

I am playing around with ESRI shapefiles, i.e. maps. They come as a set of three files, *.shp, *.shx and *.dbf

Now the older ones among us (i.e. the great majority) know perfectly well what a DBF file is. And even my trusty old Microsoft Excel 2003 can open a DBF file.

So I went ahead to grab info from the DBF, using xlsConnect. That worked like a charm, Excel opens the file, selects the rows and columns I need, and I can grab the text with xlsRead$(). Perfect.

And then I stumbled over a map where all this worked except that I could grab only the first two rows.

What was wrong? I spent several hours trying all kinds of Excel commands, I even saved the dbf as native xls to exclude quirks with the dbf format.

Then I finally had the bright idea to take a closer look at what I get with xlsRead$(). Surprise, surprise: zeros, lots of zeros all over the place

DBase IV files have fixed size records, and they can contain zero bytes (most of them fill up with spaces, though).

So when you ask Excel to hand you over a range of cells, and the third cell has a zero byte in it, then it does give you the full string, great, but it has zero bytes in it, so your usual C-style zero-delimited string is, ehm, a bit shorter than expected...

Even if you save the file in native xls format, you get the same problem.

Wow. I bet it's a feature

[TimoVJL]

MS ODBC driver for dBase should test too with that database.
Give a link for testing.

[jj2007]

Give a link for testing

Here it is.

[TimoVJL]

LibreOffice found 251 records from it 

MS ODBC driver in Windows 7 have problems with it.

[sinsi]

MS ODBC driver in Windows 7 have problems with it.

Windows 11 also.
Excel complains about a string too long?

jj is it a dBASE IV file? The header seems to say it's a dBASE III file (or FoxPro Plus)

I can connect to it using ODBC and get the table name but as soon as I try to exec "SELECT * FROM tablename" it can't find the object.

[Biterider]

Hi
I opened the DB using DataBaseApp.exe for test purposes only and traversed it without problems.
(from https://github.com/ObjAsm/ObjAsm-C.2/blob/master/Projects/X/Database/DatabaseApp.exe)

It is DBaseIII compatible and has no problems with "World_Countries.dbf".

Biterider

[jj2007]

Windows 11 also.
Excel complains about a string too long?

jj is it a dBASE IV file? The header seems to say it's a dBASE III file (or FoxPro Plus)

No change if I save as dBase III or IV. ODBC is just very messy. In theory, it's all documented, see e.g. SQLDriverConnect (Excel Driver), but in practice half of my files don't connect

[jj2007]

I can open a connection, and a query using SQLExecDirect

Code Select Expand

select * from D:\Masm32\examples\IczelionSQL\Out4C.xls
succeeds. Point is that file doesn't even exist...!

How exactly do you open a database file?

I opened the DB using DataBaseApp.exe for test purposes only and traversed it without problems.
(from https://github.com/ObjAsm/ObjAsm-C.2/blob/master/Projects/X/Database/DatabaseApp.exe)

Works fine

I see you are not using odbc32.dll - what do you use instead?

[Biterider]

A self-written assembler DBF handler with (multiple) indexing. 
It targets an API similar to the one you use in an DB-IDE. It does not support SQL.

Regards, Biterider 

[jj2007]

A self-written assembler DBF handler with (multiple) indexing

I wrote one 30 years ago, can't remember whether in GfaBasic or 68000 Assembly

I had hoped to solve the mystery with the SQL failures. Still fighting...

[Biterider]

I wrote one 30 years ago

It is currently a very powerful implementation that aims to skip abstraction layers and thus has very fast response time, which can be used in a server or a JIT application, for example.

Biterider

[jj2007]

@BiteRider: yes, speed is an issue. ODBC is remarkably slow

I had hoped to solve the mystery with the SQL failures. Still fighting...

Gotcha

After many attempts to find the magic connection string to open World_Countries.dbf with SQLDriverConnect and SQLExecDirect, I fell in a deep, desperate trance... when I wrote my DBF reader 30 years ago, everything was so much easier. Even the file names were shorter, remember 8.3? 

[TimoVJL]

dBASE .DBF File Structure

Code Select Expand

  DBINFO - Dbase File Information Utility.

  Structure of Database: WORLD_COUNTRIES.DBF

  Last Update.......: 12-28-123
  Number of Records.: 251
  Header Size.......: 65
  Record Size.......: 45
  Number of Fields..: 1

  FIELD       TYPE  LEN  DEC
  =====       ====  ===  ===

  COUNTRY     C     44


[jj2007]

Code Select Expand

Dbf_File$=_dosCmd$ '* 24.11. 2007 * reads DBF3+4 files
IF RIGHT$(LOWER$(Dbf_File$),3)<>"dbf" THEN Dbf_File$=@GetShortName$(...)
16 years ago I already knew about the 8.3 problem. I am flabbergasted that Windows 10 can't handle long file names in its ODBC implementation. Since GetShortPathName fails miserably on Win10, renaming the dbf file to a DOS 8.3-compatible name is the only option. Shame on you, Redmond

[TimoVJL]

With connection string:

Code Select Expand

"DRIVER={Microsoft dBase driver (*.dbf)};DriverID=277;Dbq=C:\TEMP;SELECT clause can be shorter

Code Select Expand

SELECT * FROM World.dbf
SQLDriverConnect (dBASE Driver)

for 64 bit world
Can't connect to dBase files from web app