Data From DBF

DataFromDBF.SK function is used to fetch data from a dBase Database file (.dbf file).

Pre-requisite

For the DataFromDBF.SK function to work on a PC/Server, Microsoft.ACE.OLEDB or Microsoft.Jet.OLEDB driver needs to be installed on it. If this requirement is not met, the below error message is thrown when the function is executed:

“Could not find installable ISAM”

The Microsoft.ACE.OLEDB driver is included as a part of Microsoft Access Database Engine 2010 Redistributable software. Download and install the .exe file based on the bit-ness of your Excel.

Download Link: https://www.microsoft.com/en-in/download/details.aspx?id=13255

Note: Sometimes, you might still get the “Could not find installable ISAM” error even though the MS Access Database Engine was installed earlier on the PC. Check whether the program is still in the PC by checking in “Add or Remove Programs”. If it’s present, remove the program and re-install it again.

Syntax and usage:

As of now, SheetKraft Excel Add-In does not contain any UI element/button to access this function. The function can be accessed by typing =DataFromDBF.SK() on an Excel cell.

File_path: File path of the DBF file from which data needs to be extracted.

Spec_range: The range of Field names (like column names of a DB table) whose values needs to be extracted from the given DBF file. The Spec range should be a horizontal range not vertical.

The output would be a range of values as seen below

Additional Notes & Tips:

1. To view the list of field names present in a DBF file, open it in Excel (drag the file into an open Excel workbook). By doing this, the Field Names and Field Values in the DBF file can be viewed. Copy the required Field Names and paste it as a horizontal range in your setup workbook.

2. The number of characters in a DBF field name should not exceed 10 characters.

3. Duplicate Field Names: If DataFromDBF.SK is used on a DBF with duplicate field names in it, only the data under the first duplicate field name is always fetched. Consider a sample DBF as shown below, here the field name SIP_CNT is repeated twice.

The fetched data would look like this. As you can see below, the data under the first duplicate field name is repeated at under the second duplicate field as well.

4. Corrupted DBFs: Sometimes when trying to extract the data from a DBF file using DataFromDBF.SK function, you might encounter this error:

“The record is deleted. The search key was not found in any record”

This error occurs because the DBF file is corrupted. It usually occurs in the DBF files shared by CAMS (a Mutual Fund Transfer Agency). In case of DBFs shared by CAMS, the corruption happened due to the reason below:

This corrupted file can be fixed by using a specific batch script file. The fixed DBF can then be used to extract data. The mentioned batch file and its usage can be seen in the sample setup shared.