Export Foxpro 9 DBF to Dbase 5 File 1

[gbcpastor]

I have a client with proprietary software that requires dBase 5 files. I need to export a file in DBF format that is compatible with their system. I don't know what's different about the files but they have to be dBase 5 format.

 

[ggreen61]

I am not sure, but I think the command COPY TO with either the FOXPLUS or FOX2X setting for TYPE.

 

[EinTerraner]

I'm not sure, but You can try to use COPY TO <targetname> FOX2X
This should be at least compatible to borlands DBASE-4 and should be readable

 

[Joe Crescenzi]

I am not sure, but I think the command COPY TO with either the FOXPLUS or FOX2X setting for TYPE.

I agree. FOXPLUS or FOX2X are the most likely formats for DOS based dBase systems.

Just keep in mind that there could be other potential incompatible fields that can't be translated into older versions, so it will work as long as they're mainly using the core types (Character, Numeric, Logical, Etc.), and not using newer ones like DateTime, VarChar, or the binary types.

Also, you may want to ask if they can also accept files such as tab delimited, CSV, XLS, or flat SDF.

 

[EinTerraner]

When exporting such stuff check also some chars in there

#define    DosChars    "„”Ž™šáø"
#define    WinChars    "äöüÄÖÜß°"

I had to translate those from DOS to WIN

 

[gbcpastor]

This is not the old DOS version of dBase. I think they need files for windows ACCESS and they call the file type dBase 5. I tried 2x and FOXPLUS and neither worked. So, I'm still stuck. I'm trying to talk them into a CSV import on their end but I hate going to the client and tell them "I Can't Produce What They Need!".

 

[Chris Miller]

Well, you have a good "excuse": VFP is the successor of dBase, it's not dBase and doesn't need to be. It's still around and was around for about two decades longer, if you think of it as Ashton Tate product.

ACCESS can access VFP9 DBFs, if they can't VFP can access ACCESS databases, no matter if old mdbs or more modern, so it would be easy enough to populate an Access database with Foxpro data, wouldn't that be much more practical?

The only reason to use a table format file for data exchange would be data types that don't translate very well into text formats like CSV, like BLOBs. But then such formats would also not be supported by a dBase 5 file, would they?

Well, and in very short, if their goal is to import that exchange data file into an Access database, they must be very uninformed - to say it pollitely and you may need to say it even more politely - when they think an old dBase format is the common denominator for data exchange from VFP to ACCESS.

 

[atlopes]

I have a client with proprietary software that requires dBase 5 files. I need to export a file in DBF format that is compatible with their system. I don't know what's different about the files but they have to be dBase 5 format.

GBCPastor,

Run the following command, select one of the dBase 5 files your client uses, and share the result.

MESSAGEBOX(CAST(LEFT(FILETOSTR(GETFILE("dbf")), 1) AS W))

 

[Chris Miller]

What atlopes aims at is knowing the file type. The VFP help chapter about the dbf file structure tells you that in the first byte of a dbf file the type of DBF is noted. And VFP knows these types:

0x02 FoxBASE / dBase II 0x03 FoxBASE+ / FoxPro /dBase III PLUS / dBase IV, no memo 0x30 Visual FoxPro 0x31 Visual FoxPro, autoincrement enabled 0x32 Visual FoxPro, Varchar, Varbinary, or Blob-enabled 0x43 dBASE IV SQL table files, no memo 0x63 dBASE IV SQL system files, no memo 0x83 FoxBASE+/dBASE III PLUS, with memo 0x8B dBASE IV with memo 0xCB dBASE IV SQL table files, with memo 0xF5 FoxPro 2.x (or earlier) with memo 0xFB FoxBASE (?)

There are more values for this byte, not documented by VFP, but these are DBF types VFP can access natively, without using an ODBC driver. Which also means other languages - including VBA within Acccess can access all such DBF types by the VFP OLEDB Provider.

VFP can't create all these file types, but even if it would be able to, dBASE V is not within that, obviously.

So that's quite useless, but as there is the VFP OLEDB Provider and VBA can make use of ADODB, you can access modern/usual VFP DBF files (types 0x30-0x32) with that provider from Access. And as there are ODBC Drivers and OLEDB Providers for Access, VFP can also access Access databases and even create them and create and fill tables.

There is no reason to go through a dBASE V file. I think they thought it makes things easy for you as VFP is a dBASE clone. VFP has drowned the dBASE market as we were at dBASE versions II, III and IV already, there never was a need for VFP to continue parallel development with dBASE and support all dBASE file types, as it was already more successful at some stage, than dBASE ever was.

You could likely also create a dBASE V file, if you find a dBASE ODBC driver supporting that old format and able to create such DBFs, that would be the solution path, if you don't want to negotiate about simpler options available.

There indeed is a current dBASE 2019, but that's just a brand continued, Ashton Tate was bought by Borland and I liked Borlands Pascal and C++ Builder, I developed on their platform, but not with dBASE. I see that dBASE 5.0 was released by Borland, actuallly, and maybe there is a license or patent reason VFP didn't added in Borlands dBASE V format into the supported file formats.

I have no idea whether the current dBASE 2019 would still support dBASE V files,as its not by Borland anymore, they also ceased to exist and where bought by Micro Focus and I didn't look whether Micro Focus still exists, but the rights about dBASE went into a dBASE LLC.

One thing is sure: You will need an ODBC driver or OLEDB Provider to be able to use such files and maybe also be able to create them, I doubt you can hack your way to a dBASE V file by first creating the most compatible format and then change the file type byte #1 in the file. That'll only make it seem like being a dBASE V file, not make it one, actually, of course. And, this overview of file types only tells you that first byte value for dBASE V is not any of the given hexadecimal values.

And since dBASE 5 was by Borland I wonder if the new dBASE LLC company would support that old format and have drivers for that. So it's even not clear whether you get at old enough ODBC drivers, I doubt an OLEDB Provider for dBASE V exists, as that concept of database providers was introduced far later and MS lost interest to make OLEDB Providers an industry standard anyway.

 

[gbcpastor]

GBCPastor,

Run the following command, select one of the dBase 5 files your client uses, and share the result.

MESSAGEBOX(CAST(LEFT(FILETOSTR(GETFILE("dbf")), 1) AS W))

This is the result. I must produce this filetype for the software on their machine.

 

[Chris Miller]

Okay, accoding to the list of file type byte values I posted 0x03 means FoxBASE+ / FoxPro /dBase III PLUS / dBase IV, no memo.
I expected a value not listed, which would have made it clearer. The good news now is, that VFP could handle such DBFs, although it can't create such DBFs.

So it seems dBase 5 files are still like dBase IV files and even like dBase III PLUS.

As you have no option within native VFP to create such files, but you have such a file, you can work on it. Which includes ZAPping it and filling it with current/new data. So that would be an option, wouldn't it?

Make a copy of that DBF, ZAP and fill it. At least worth a try.

 

[Chris Miller]

On the other hand: That so many file type byte values have several options means it's no clear sign about the DBF structure, the 0x03 can also simply mean VFP dbf (of some old VFP version, surely) without memo, as FoxPro is also listed for 0x03.

Anyway, after making a file copy and ranaming that to say dbVcopy.dbf does this open and empty the DBF?

Set Tablevalidate To 0
USE dbVcopy
ZAP
BROWSE

If you get there the next thing to try would be INSERT SQL vs APPEND BLANK and REPLACE vs APPEND FROM some other DBF, even VFP9 DBF vs IMPORT or APPEND from a text file.

The irony of the last thing would be that any text file (CSV, tab delimited or SDF format) would be sufficient instead of the dbaseV file, too, Well, wait and see.

 

[atlopes]

This is the result. I must produce this filetype for the software on their machine.

View attachment 297

The following code will produce 0x03 files:

CREATE CURSOR tmp (pk N(5), abc c(10))
INSERT INTO tmp VALUES (1, 'www')
INSERT INTO tmp VALUES (2, 'xyz')
COPY TO test-foxplus.dbf TYPE FOXPLUS
COPY TO test-fox2.dbf TYPE FOX2
MESSAGEBOX(CAST(LEFT(FILETOSTR(GETFILE("dbf")), 1) AS W))

Adding a memo (or varchar, or blob, or varbinary...) to the source cursor will generate other types of files (0x83 and 0xF5).

CREATE CURSOR tmpm (pk N(5), abc c(10), xtra memo)
INSERT INTO tmpm VALUES (1, 'www', 'something to remember')
INSERT INTO tmpm VALUES (2, 'xyz', 'something to forget')
COPY TO d:\desenvolvimento\temp\teste-m-foxplus.dbf TYPE FOXPLUS
COPY TO d:\desenvolvimento\temp\teste-m-fox2.dbf TYPE FOX2
MESSAGEBOX(CAST(LEFT(FILETOSTR(GETFILE("dbf")), 1) AS W))

 

[Chris Miller]

I don't know if that matters and proves anything, but I looked for a dbase sample file that also had 0x03 as file type value and found one at https://github.com/cwarden/kettle/b...ormations/files/XBase - dBase sample data.dbf

1. It also showed 03 as file type value
2. I could use it and work on it in VFP9, no problem. Also BROWSE and ZAP it and even use INSERT SQL and ALTER TABLE.

What you can't do is create such files with CREATE TABLE or COPY TO. But as I could use ALTER TABLE, with that you can both ADD columns and DROP columns, so you could even change the structure to something completely different, the file header remains the same file type 0x03. So that could be a way to achive any dbase V file you want. It will take some experimenting to see which data types are safe to use with ALTER TABLE, though and whether the resulting DBF file can still be processed within dBASE V or Excel or whatever else they use it in.

 

[Chris Miller]

Okay, that crossed with my last post.

So you essentially found out COPY TO ... TYPE FOX2X or COPY TO ... TYPE FOXPLUS creates compatible files, I wonder why you didn't saw that from the first answer by Greg, already?

You even said

I tried 2x and FOXPLUS and neither worked.

 

[gbcpastor]

On the other hand: That so many file type byte values have several options means it's no clear sign about the DBF structure, the 0x03 can also simply mean VFP dbf (of some old VFP version, surely) without memo, as FoxPro is also listed for 0x03.

Anyway, after making a file copy and ranaming that to say dbVcopy.dbf does this open and empty the DBF?

Set Tablevalidate To 0
USE dbVcopy
ZAP
BROWSE

If you get there the next thing to try would be INSERT SQL vs APPEND BLANK and REPLACE vs APPEND FROM some other DBF, even VFP9 DBF vs IMPORT or APPEND from a text file.

The irony of the last thing would be that any text file (CSV, tab delimited or SDF format) would be sufficient instead of the dbaseV file, too, Well, wait and see.

And there you go. All I had to do is copy it out without memo fields and use type FOXPLUS and it worked great. Thank you Chris and thank all the rest for all the help.

 

[Chris Miller]

Okay, so memo fields are what broke it. Well, it's even true for the "modern" VFP DBF formats that files with memo have another file type value than without. AS you can see from the list it even differs whether an autoinc field exists or not.

I learned from this that dBASE V files seem to still be the same as dBASE IV and dBASE III Plus and FoxBase Plus. The formats with memo are 0x83 or 0xF5. They are not really different formats, you wouldn't also say VFP9 DBFs with formats 0x30, 0x31, or 0x32 are different formats, they just have different features and I guess the bit that's telling VFP there is a seprate file for the memo type fields file makes it look for that FPT file or not. Or DBT in case of dBASE.

Well, dBASE V is not explicitly mentioned and maybe (likely) in that dBASE version table with memo have other file types and so headers of such DBFs are incompatible to what VFP knows about DBFs. Unlucky to fail on that detail, of course, but at least VFP creates a DBT file with the TO FOXPLUS option instead of FPT, it doesn't fail on that detail. Maybe it's even just their ACCESS that fails on that and dBASE iself would work with such dbfs with a dbt memo file.

 

[mJindrova]

dBASE V file it's a Paradox DB file and it's incompatible with FP/VFP DBF.

 

[Chris Miller]

Well, FoxBase Plus, not FP/VFP. If I assume the same file type values are not only overlapping and can mean other structures, depending on which type of Xbase language is used to atually create them for itself, then FoxBase Plus must have been compatible to dBase and Paradox.

I take it from gbcpastors experience, that FoxBase Plus files with memo are not compatible to dBASE, while those without memo are.

But being sure about anything? I'm not sure about all field types, etc and I surely won't even want to know all the details about legacy DBF formats.

 

[gbcpastor]

Okay, so memo fields are what broke it. Well, it's even true for the "modern" VFP DBF formats that files with memo have another file type value than without. AS you can see from the list it even differs whether an autoinc field exists or not.

I learned from this that dBASE V files seem to still be the same as dBASE IV and dBASE III Plus and FoxBase Plus. The formats with memo are 0x83 or 0xF5. They are not really different formats, you wouldn't also say VFP9 DBFs with formats 0x30, 0x31, or 0x32 are different formats, they just have different features and I guess the bit that's telling VFP there is a seprate file for the memo type fields file makes it look for that FPT file or not. Or DBT in case of dBASE.

Well, dBASE V is not explicitly mentioned and maybe (likely) in that dBASE version table with memo have other file types and so headers of such DBFs are incompatible to what VFP knows about DBFs. Unlucky to fail on that detail, of course, but at least VFP creates a DBT file with the TO FOXPLUS option instead of FPT, it doesn't fail on that detail. Maybe it's even just their ACCESS that fails on that and dBASE iself would work with such dbfs with a dbt memo file.

The only info I could get about dBase 5 files was they were used by spinoffs like "Clipper" and others. Access "DBF" files are also in this configuration.

Again, thanks for your help.