How to Open a DBF File in Excel

by

Excel can handle many complex calculations and formulas to assist you with datasets. There are times when you need to use Excel to open files that are not native to the Excel application.

This tutorial will walk you through the steps necessary to open your .dbf file in Excel.

Open a DBF File in Excel Using the Open File Command

  1. Open Excel and navigate to the .dbf file. Excel will only show native files, so you must change the file type in the dialog box from “Excel Files” to dBase files.
Image showing the available file types to open in Excel, highlighting the dBase file type.

  1. Click the .dbf file in the dialog box and tap the “Open” button.
Image showing the DBF file in the "Open" file dialog box.

  1. The file opens in Excel and you can edit it to fit your needs.
Image showing the DBF file open in Excel.

Open a DBF File in Excel Using the Get Data Command

  1. Open Excel and tap “Data” in the top menu.
Image showing the Data menu highlighted in Excel.

  1. Click the down arrow next to Get Data to expand the options, select “From Other Sources…” and click “From Microsoft Query.”
Image showing the navigational path Get Data, From Other Sources, From Microsoft Query.

  1. Tap the “Options…” button in the “Choose Data Source” dialog box.
Image showing the Choose Data Source dialog box with the Options button highlighted.

  1. Position the mouse pointer directly after .oqy in the “File Name:” section and type: “ ;*.dbf.” Next, in the “Folders:” section, navigate to the drive where the DBF file exists and click the “OK” button.
Image showing the .dbf file extension added to the File Name: text box and the folder selected in the Drives: text box of the Select Directory dialog box.

  1. Click the “OK” button in the “Data Source Options” dialog box.
Image showing the OK button highlighted in the Data Source Options dialog box.

  1. Click the “OK” button in the “Choose Data Source” dialog box, and the “Create New Data Source” dialog box appears. Input a filename in the “What name do you want to give your data source?” box, and then choose “Microsoft Access dBase Driver” from the drop-down list under “Select a driver for the type of database you want to access:”
Image displaying the Choose Data Source dialog box with New Data Source highlighted.

Large red down arrow.
Image displaying the Create New Data Source dialog box with "My DBF File"

  1. Click the “Connect…” button and the ODBC dBase Setup box appears. Leave the version as dBase 5.0 and tap the “OK” button.
Image showing the ODBC dBASE Setup dialog box with dBASE 5.0 selected as the database version and the OK button highlighted.

  1. The file name path to the DBF file appears next to the “Connect…” button. Click the “OK” button.
Image showing the Create New Data Source dialog box with the DBF file path and the OK button highlighted.

  1. You will see the filename of your DBF file highlighted in the “Databases” tab. Click the “OK” button in the “Choose Data Source” dialog box.
Image showing "My DBF file" highlighted in the "Databases tab of the "Choose Data Source" dialog box.

  1. The tables and columns appear on the left-hand side of the “Choose Columns” dialog box. Highlight the tables and columns to include in your query, and then click the chevron arrow to move them into the “Columns in your query” portion of the dialog box. Click the “Next” button.
Image displaying the "Query Wizard - Choose Columns dialog with the DBASETAB highlighted in the "Available tables and columns" area and the chevron arrow as well as the "Next" button highlighted.

  1. The query wizard allows you to filter the data if you choose. For this tutorial, we will not be filtering data. Tap the “Next” button on the wizard.
Image displaying the "Query Wizard - Filter Data" dialog box with the "Next" button highlighted.
  1. The “Sort Order” dialog box appears. You can select how to sort the data from your table. Once you have made sorting selections, tap the “Next” button.
Image displaying the "Query Wizard - Sort Order" dialog box with the "Next" button highlighted.
Large Red Down Arrow.

Image showing the Query Wizard - Sort Order dialog box with "Company Name" in the first "Sort By" box, "Stock Price" in the second "Sort By box, and finally "Stock Symbol" in the last "Sort By" box.
  1. Make sure that the “Return data to Excel” radial button is selected, and tap the “Finish” button to complete the query.
Image showing the "Query Wizard - Finish" dialog box with the radial button for "Return Data to Microsoft Excel" selected and the "Finish" button highlighted.
  1. Choose how to import the data into Excel in the “Import Data” dialog box. For this tutorial, we will import the data as a simple table. Click “OK” to finish the import.
Image showing the "Import Data" dialog box with the radial button next to "Table" selected and the cell "A1" selected in the "Existing worksheet:" portion of the dialog box and the "OK" button highlighted.

  1. The data from the DBF file is displayed in the worksheet.
Image showing the contents of the DBF file displayed in the Excel worksheet.

Conclusion

Now you have learned two different methods for opening a .dbf file so that you can manipulate the data in Excel. Please feel free to take a look at other tutorials on our website.

Leave a Comment

Previous

How to Sort by Last Name in Excel

Next

How to Remove Text Before or After a Specific Character in Excel