MS Office 2013 xls-dbf (dbase IV) conversion

25398
9
07-10-2015 03:43 PM
DanielBrenner
Occasional Contributor


YES I KNOW this is more of an MS OFFICE Forum question but I cannot access their forum at work right now and a significant amount of my work does rely on dbf format files. 

My office has just switched to MS Office 2013 and neither MS Excel or MS Access support dbf (dbase IV) format.  I am SOL unless I get an add on.   I even used to be able to convert xls files in MS Access (even with Field Does Not Fit in Record error message) but can't even do that now.

Has anyone run into this same problem?   What are my options?

Will send an xls file if you think you have a real solution.

Thanks for understanding.

Dan B

0 Kudos
9 Replies
XanderBakker
Esri Esteemed Contributor

May I assume that you have access to ArcGIS for Desktop? If so, you could try the Excel To Table (Conversion) Excel To Table—Help | ArcGIS for Desktop to convert to a folder which will create a dbf file.

0 Kudos
DanPatterson_Retired
MVP Emeritus

Xander add this link for 10.3 to your thread...the one you provided is just going to the top of help...could be temporary

Excel To Table—Help | ArcGIS for Desktop

XanderBakker
Esri Esteemed Contributor

Yep you're right. Pretty annoying that the links are malfunctioning again (the patch to correct this is going to be applied this evening?)

0 Kudos
DanPatterson_Retired
MVP Emeritus

Popular question this week...your choices are

  • use a different spreadsheet (ok rule that one out, Quattro pro, that open suite(?) etc are better)
  • save to a csv file (ie text file with comma-separated)
  • save to any other format that Arc can read
  • get the add-on
  • always keep the older version somewhere (I know...this isn't an option for you, but for someone it is)

Good luck

DanielBrenner
Occasional Contributor

Sorry, Dan, but that isn't helping me.

1) Things like Quattro Pro, add ons, and the older version of MS

Office aren't allowed or supported in my working environment. 

2) .csv files and other formats don't work very well for joining into

larger dbf tables, and on top of that, have you ever noticed how much

of a pain it is to have to edit (copy-paste contents of fields from

the joined fields to align with the original fields in a .shp file?

Let alone having to copy-paste from MS Excel to do that given the

limited database editing functionalities of ArcGIS?    That right

there makes ArcGIS very antiquated in the 21st century.

Thanks for trying.

Dan B

0 Kudos
MelitaKennedy
Esri Notable Contributor

I googled office 2013 dbase and had several hits. The third one looked promising--install the Office 2010 runtime, then hack the registry! I hope you can understand why I didn't post a direct link.

Melita

DanielBrenner
Occasional Contributor

Melita,

I am not in a position to do what you are suggesting (download other softwares and hacking the registry).

0 Kudos
TedKowal
Occasional Contributor III

The obvious answer is to purchase a third party ODBC/ADO driver which will allow for the import/export of dbf files.

One horrible workaround I found is to save the data as an excel table and import that into access.  Not pretty but does work.

BTW if you are using a 32 bit Office 2013 Install --- you can still create a dBase DSN.... If you are using 64 bit SOL

Found this on GitHub... jenojunio/import-dbf-to-msaccess2013-function · GitHub

------

If you are ambitious and want to find all the dll's, you can use .NET or even VBA(I haven't tried but should work) to access the DBF's

While you cannot use the external data tab. You can in VB .Net 2013 Express (And VBA I believe)
use this code. You will need to add to your registry the dbase information.
Dim AccessConn As New System.Data.OleDb.OleDbConnection( _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\S\Job.mdb")


' Import DBF File
AccessConn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand( _
"SELECT * INTO [Job] FROM [dBase III;DATABASE=D:\S].[JobX]", AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()

You can create a new text file for the registry files.
You also may need a .dll named ACEXBE.DLL in C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE14
Create a text file called, dBASE-3.0.reg copy the lines below into it. The save and close. Click on file. This will add the dbase to the registry
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE III]
"Engine"="Xbase"
"ExportFilter"="dBASE III (*.dbf)"
"ImportFilter"="dBASE III (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:01

Here are other dbase files:dBASE-4.0.reg
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE IV]
"Engine"="Xbase"
"ExportFilter"="dBASE IV (*.dbf)"
"ImportFilter"="dBASE IV (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:01

dBASE-5.0.reg
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\ISAM Formats\dBASE 5.0]
"Engine"="Xbase"
"ExportFilter"="dBASE 5 (*.dbf)"
"ImportFilter"="dBASE 5 (*.dbf)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"SupportsLongNames"=hex:01

xBase.reg
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Xbase]
"win32"="C:\\PROGRA~1\\COMMON~1\\MICROS~1\\OFFICE14\\ACEXBE.DLL"
"DbcsStr"=hex:01
"Mark"=dword:00000000
"Date"="MDY"
"Exact"=hex:00
"Deleted"=hex:01
"Century"=hex:00
"CollatingSequence"="Ascii"
"DataCodePage"="OEM"
"NetworkAccess"=hex:01
"PageTimeout"=dword:00000258
0 Kudos
DanPatterson_Retired
MVP Emeritus

Given your work environment, you might have to approach those within your IT department to see if some of the  constraints can be lifted given that many of the aforementioned recommendations provide viable solutions in many situations.

0 Kudos