Importing a .dbf (dBase) file into Power BI


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:\Blog\DBF\;extended properties=dBASE IV", [Query="select * from [rain.dbf]"])
in
    Source

 

Power BI Advanced Query Editor

 

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

 

Edit Credentials Dialog in Power BI

 

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.

 

Native Database Query dialog in Power BI

 

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. 

 

Default or custom credentials dialog in Power BI

 

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

 

dbf file loaded in Power BI

 

Rain fall in Ethiopia in Power BI 

 

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)

Comments (13)

  1. Yoshihiro Kawabata says:

    Thanks, dBase support.
    and I hope to support dBase as Power BI’s data source for more easy operation.

  2. Priscilla says:

    Hi, I have followed your post but am having issues connecting to my dbf file. Basically beacuse it’s giving me a “DataSource.Error: OLE DB: External table is not in the expected format” It seems to be happening because the fpt file is a seperate file.

    How can i link them please?

    1. Hi Priscilla, can you point me to example files where I can try this out?

      1. Priscilla says:

        Hi Samuel,

        Hmm… that’s a bit tricky… can’t find any sample files.
        Can i send you the files through a private message ?

        1. Yes, send me an email at samles @ microsoft . com.

          1. Gustavo says:

            Samuel,
            Did you find an answer to the FPT file being a separate file.

  3. Nonnatee Kanjana says:

    I seem to have error msg. Any workaround to this issue?

    1. What is the error message you are hitting and what is the scenario when you encounter this?

  4. jorge guevara says:

    good day,
    I get the following error message:

    DataSource.Error: OLE DB: External table is not in the expected format.
    Details:
    DataSourceKind = OleDb
    DataSourcePath = data source = F: \ VSAI \ Companies \ EMP3 \; extended properties = & quot; dBASE IV & quot ;; provider = Microsoft.ACE.OLEDB.12.0
    Message = External table is not in the expected format.
    ErrorCode = -2147467259

    can help me in the resolution of this connection to the DBF table.

    Greetings.

    1. Hi Jorge, can you do a quick test to rename the file to a name that is <= 8 characters? I hit an issue with the driver when the filename prior to the .dbf extension is greater than 8 characters.

      Thanks,
      Sam

  5. Martin says:

    Sam, tried to follow your example – downloaded the Ethiopia rain statistics (it is named eth_rainst.dbf) , and used the connection string you have, but I get this error – DataSource.Error: OLE DB: Could not find installable ISAM.
    Details:
    DataSourceKind=OleDb
    DataSourcePath=data source=C:\Users\martintse\Desktop\Temp\;extended properties="dBASE IV";provider=Microsoft.ACE.OLEDB.12.0
    Message=Could not find installable ISAM.
    ErrorCode=-2147467259

    Tried, OleDB 15, dbase 5.0, but still no go – can you check if this still works for you?

    1. Hi Martin, I just tested it out and it is still working for me without the error you posted. One thing I did encounter testing it out is that there appears to be an 8 character filename limitation with this driver. In the original blog, I’d unintentionally worked around this by renaming the file to rain.dbf. When I just tested it out with the downloaded file name, I was receiving an error that the file didn’t exist since it was greater than 8 characters. Renaming the file and updating the connection string fixed this.

  6. Hayder says:

    Hello, This is not working i’m also getting (DataSource.Error: OLE DB: External table is not in the expected format). and the file name is shorter than 8 characters.

    Can you please help

Skip to main content