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 (https://www.jj2007.eu/MasmBasicQuickReference.htm#Mb1085). 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. :sad:
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 :biggrin:
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 :cool:
MS ODBC driver for dBase should test too with that database.
Give a link for testing.
LibreOffice found 251 records from it :thumbsup:
MS ODBC driver in Windows 7 have problems with it.
Quote from: TimoVJL on December 28, 2023, 11:57:10 PMMS 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.
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 (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
Quote from: sinsi on December 29, 2023, 06:06:57 PMWindows 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) (https://learn.microsoft.com/en-us/sql/odbc/microsoft/sqldriverconnect-excel-driver?view=sql-server-ver16), but in practice half of my files don't connect :sad:
I can open a connection, and a query using SQLExecDirect
select * from D:\Masm32\examples\IczelionSQL\Out4C.xlssucceeds. Point is that file doesn't even exist...!
How exactly do you open a database file?
Quote from: Biterider on December 29, 2023, 06:54:05 PMI 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 (https://github.com/ObjAsm/ObjAsm-C.2/blob/master/Projects/X/Database/DatabaseApp.exe))
Works fine :thumbsup:
I see you are
not using odbc32.dll - what do you use instead?
A self-written assembler DBF handler with (multiple) indexing. :biggrin:
It targets an API similar to the one you use in an DB-IDE. It does not support SQL.
Regards, Biterider
Quote from: Biterider on December 30, 2023, 04:03:14 AMA self-written assembler DBF handler with (multiple) indexing
I wrote one 30 years ago, can't remember whether in GfaBasic or 68000 Assembly :biggrin:
I had hoped to solve the mystery with the SQL failures. Still fighting...
Quote from: jj2007 on December 30, 2023, 04:24:44 AMI 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
@BiteRider: yes, speed is an issue. ODBC is remarkably slow :rolleyes:
Quote from: jj2007 on December 30, 2023, 04:24:44 AMI had hoped to solve the mystery with the SQL failures. Still fighting...
Gotcha :thumbsup: 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? :cool:
dBASE .DBF File Structure (https://blogs.embarcadero.com/dbase-dbf-file-structure/)
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
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 :sad:
With connection string:
"DRIVER={Microsoft dBase driver (*.dbf)};DriverID=277;Dbq=C:\TEMP;SELECT clause can be shorter
SELECT * FROM World.dbf
SQLDriverConnect (dBASE Driver) (https://learn.microsoft.com/en-us/sql/odbc/microsoft/sqldriverconnect-dbase-driver?view=sql-server-ver16)
for 64 bit world
Can't connect to dBase files from web app (https://stackoverflow.com/questions/43055700/cant-connect-to-dbase-files-from-web-app)
The ODBC documentation is horribly confused, but with trial and error we may resolve its mysteries. Attached a testbed - extract to a folder and run it. Hit Escape+Return to exit.
Queries that work:
select * from World_Countries
select * from World_Co
select * from otherdb3
select * from otherdb4
select * from otherdb4WhatEverSuitsYou
Queries that don't work:
select * from World_C
select * from otherdb2
select * from otherdb
There is even a logic in it, but it's pretty weird. Good old DOS 8.3 times ;-)
New testbed. I've managed to open and read DBF (III and IV), XLS files and MDB files :biggrin:
Usage: drag a dbf, xls or mdb file over the exe; for mdb files, you need to adjust the query to main, i.e. take away the numbers.
One oddity is that dbf and xls files require a path to the folder, while mdb files need a filename (the testbed handles this automatically).
Question: can xls files (testjj2.xls, testjj3.xls) be read with the tool if the user has not MS Office installed?
Can I have some feedback please for other Windows versions? I have tested in on Win10 and a WinXP VM (where only Excel 3.0 works).
P.S. Below results of C:\WINDOWS\SysWOW64\odbcad32.exe, for admin and ordinary user.
https://answers.microsoft.com/en-us/msoffice/forum/all/i-need-to-locate-and-download-the-odbc-driver-for/27563edf-fb35-4415-ba68-e0de5b95b963 (https://answers.microsoft.com/en-us/msoffice/forum/all/i-need-to-locate-and-download-the-odbc-driver-for/27563edf-fb35-4415-ba68-e0de5b95b963)
https://stackoverflow.com/questions/42782449/missing-accdb-database-drivers#43434633 (https://stackoverflow.com/questions/42782449/missing-accdb-database-drivers#43434633)
EDIT:
#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#include <stdio.h>
#pragma comment(lib, "user32.lib")
int __cdecl main(void)
{
HKEY hKey, hKey2;
DWORD dwIdx, dwRC, dwType;
char szSub[260], szValue[260], szTmp[260];
hKey = 0;
szValue[0] = 0;
if (!RegOpenKey(HKEY_LOCAL_MACHINE, "SOFTWARE\\ODBC\\ODBCINST.INI", &hKey))
{
dwIdx = 0;
while (RegEnumKey(hKey, dwIdx++, szSub, sizeof(szSub)) == ERROR_SUCCESS)
{
wsprintf(szTmp, "SOFTWARE\\ODBC\\ODBCINST.INI\\%s", szSub);
if (!RegOpenKey(HKEY_LOCAL_MACHINE, szTmp, &hKey2))
{
dwRC = sizeof(szValue);
dwType = REG_SZ;
if (RegQueryValueEx(hKey2, "Driver", NULL, &dwType, (BYTE *)szValue, &dwRC))
;
//puts(szTmp);
//puts(szValue);
printf("%s\t%s\n", szTmp, szValue);
RegCloseKey(hKey2);
}
}
RegCloseKey(hKey);
}
return 0;
}
EDIT: perhaps useless to download, as those have bugs.
unable to load odbcji32.dll (https://answers.microsoft.com/en-us/msoffice/forum/all/unable-to-load-odbcji32dll/3fe66d64-2da7-499d-b1ae-c16c7c40bba2?page=2)
Thanks, Timo :thumbsup:
As much as possible, I want to stick with components that are already installed by default. So far I can read older xls, dbf and mdb files with standard components.
A few discoveries
- a DBF file is not a database, but a database table
- most versions of ODBC truncate the filename to 8.3, so it might work if you use the short name
- downloading the FoxPro9 OLEDB driver seems to open any DBF, including the list that didn't work
A direct download link is https://github.com/VFPX/VFP9SP2Hotfix3/raw/master/VFPOLEDBSetup.msi
old 32-bit version
VFPODBC.msi (https://github.com/VFPX/VFP9SP2Hotfix3/blob/master/VFPODBC.msi)
EDIT:
Using it directly, don't have install it.
"DRIVER={Microsoft Visual FoxPro Driver};SourceType=DBF;DBQ=xxxx;"
Quote from: sinsi on January 01, 2024, 02:04:10 AMA few discoveries
- a DBF file is not a database, but a database table
Right.
Quote- most versions of ODBC truncate the filename to 8.3, so it might work if you use the short name
Unfortunately, it's not that easy.
Quote- downloading the FoxPro9 OLEDB driver seems to open any DBF, including the list that didn't work
A direct download link is https://github.com/VFPX/VFP9SP2Hotfix3/raw/master/VFPOLEDBSetup.msi
As written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.
Currently, I am looking into OLE DB. A can of worms, of course, but I am confident that I can come up with a
read sheet xyz from test.xls into the Sql$() array in less than 100 lines of underlying code.
Happy New Year to everybody, and thanks for all the suggestions :thumbsup:
Quote from: jj2007 on January 01, 2024, 03:46:46 AMAs written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.
On a clean install of Windows 7 I could open 2/3 MDBs and 2/4 XLSs with your proggie but I had to make a User DSN for each type - the user and system lists were both empty.
What drivers come in clean installation ?
MDAC ?
EDIT:
Microsoft OLE DB Provider for Jet and Jet ODBC driver are available in 32-bit versions only (https://learn.microsoft.com/en-us/office/troubleshoot/access/jet-odbc-driver-available-32-bit-version?source=recommendations)
Win7 has the odbc jet drivers for mdb and xls but they don't work woth office 97
Quote from: sinsi on January 01, 2024, 03:57:46 AMQuote from: jj2007 on January 01, 2024, 03:46:46 AMAs written above, I want to stick with components that are already installed by default, so that potential users don't shy away from using the macros.
On a clean install of Windows 7 I could open 2/3 MDBs and 2/4 XLSs with your proggie but I had to make a User DSN for each type - the user and system lists were both empty.
Interesting. My Win7-32 VM, which should be "clean" as there is no Office installed, has no user or system DNS, but I can read Access files from '97 to 2002 (maybe higher, I don't have samples at hand). Plus Excel 3.0 (but not 4.0 or 5.0), and DBASE III + IV (but not II).
QuoteMicrosoft Office 97 (version 8.0) is the fifth major release for Windows of Microsoft Office, released by Microsoft on November 19, 1996
Office 97 still has some friends. I stick to Office 2003, and feel a bit odd to use a software that is over 20 years old. Just can't get used to ribbon interfaces :badgrin:
Happy New Year, sinsi and stoo :thumbsup: (we have 5h40' left)
QuoteHappy New Year, sinsi and stoo :thumbsup: (we have 5h40' left)
:smiley: Thanks, hope you had a good one. :biggrin: