MS Office 2013 xls-dbf (dbase IV) conversion

23353
9
07-10-2015 03:43 PM
Highlighted
New Contributor III


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

Reply
0 Kudos
9 Replies
Highlighted
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.

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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

Highlighted
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?)

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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

Highlighted
New Contributor III

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

Reply
0 Kudos
Highlighted
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

Highlighted
New Contributor III

Melita,

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

Reply
0 Kudos
Highlighted
Regular Contributor II

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

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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.

Reply
0 Kudos