How can I import data from a .dbf (dBase) file into Power BI?

Today’s Power BI Mailbag Question comes from a friend who often works with geospatial data and, as a result, ends up with several different shapefile formats, including .dbf (dBase) database table files. There are several blog articles around shapefile mapping options in Power BI, but this question of accessing the .dbf file directly from Power BI was something I hadn’t been asked before.

Note: Power BI does not have native .dbf file support at the time of publishing this article, but the idea has been proposed at ideas.powerbi.com if you would like to vote for it. 

When I began searching for a solution, I found several threads in the .NET provider MSDN forum showing how to leverage the Access Oledb Provider (Microsoft.ACE.OLEDB.12.0) to import data from a .dbf file into SQL Server. With this, we can use a similar solution in Power BI. 

Data Source (Rainfall in Ethiopia):
For this example, I’m using a .dbf file containing the rain statistics in towns in Ethiopia from worldbank.org.

Steps:
In Power BI Desktop, click on Get Data -> Blank Query

Click on the Advanced Editor and replace the default text with the following text (replacing with your path and dBase file name):

let
    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:BlogDBF;extended properties=dBASE IV", [Query="select * from [rain.dbf]"])
in
    Source

This will prompt you to edit the credentials. Click "Edit Permissions" to continue.

Another prompt appears asking to approve of running the native query against this data source. Since the OLEDB provider syntax we provided contains the query string, you need to approve that this can be executed against the .dbf file. Click Run to continue.

You may get one additional dialog box requesting proper credentials as I did with some other data sources I used in testing this solution. I selected the “Default or Custom” option with no value supplied in the connection string property dialog. 

With this, the data will be loaded into Power BI and you can continue with your report creation.

Special thanks to Aaron Bator for constantly pushing the edges of Power BI and bringing new and innovative ideas and questions. Keep them coming!

Thanks,
Sam Lester (MSFT)