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 (23)
  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

    2. Adrian Sanchez says:

      Jorge, pudiste hacerlo funcionar, uso igual el SAI y es un problema con este tema de bases de datos obsoletas.

  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.

      1. V4VB says:

        Hi Sam,
        I am also getting same error my DBF file name is : lagreich.dbf
        Error details :

        DataSource.Error: OLE DB: Could not find installable ISAM.
        Details:
        DataSourceKind=OleDb
        DataSourcePath=data source=C:\Users\standard\Desktop\Reports\;extended properties="dBASE IV";provider=Microsoft.ACE.OLEDB.12.0
        Message=Could not find installable ISAM.
        ErrorCode=-2147467259

        Could you please let me know what is exactly issue because this same code is working proper for my one PC but not working one of the different PC

        Looking forward to your response
        Thanks
        V4VB

  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

  7. Jeff says:

    Thank you Sam for this useful tutorial. I searched for an answer and this is exactly what I needed.

    If others have trouble I think the key is to start here:
    Click on Data Tab.
    Go to the following “New Query -> From Other Sources -> Blank Query”

    Follow directions directly after steps, make any necessary changes in the SQL code and should be good to go.

  8. Jeff says:

    Thank you Sam. This works perfectly.

    Getting Started Tip for others with Excel 2016:
    Click on Data -> New Query -> From Other Sources -> Blank Query

    Don’t forget to change the file path and file name in the Query 1 Code

  9. Amel says:

    Tried the Ethiopian example: does not work with the following error:

    DataSource.Error : OLE DB : The Microsoft Access database engine could not find the object ‘eth_rainst.dbf’. Make sure the object exists and that you spell its name and the path name correctly. If ‘eth_rainst.dbf’ is not a local object, check your network connection or contact the server administrator.
    Détails :
    DataSourceKind=OleDb
    DataSourcePath=data source=C:\Users\meller\Downloads\eth_rainst_0\;extended properties="dBASE IV";provider=Microsoft.ACE.OLEDB.12.0
    Message=The Microsoft Access database engine could not find the object 'eth_rainst.dbf'. Make sure the object exists and that you spell its name and the path name correctly. If 'eth_rainst.dbf' is not a local object, check your network connection or contact the server administrator.
    ErrorCode=-2147217865

    1. Hi Amel, 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 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.

  10. Martin.Gav says:

    Hi Sam, I am trying to connect Power BI to my company´s (very outdated) ERP data base which is in .dbf files, while following this instructions I got the following error:

    DataSource.Error: An error happened while reading data from the provider: ‘The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.’
    Details:
    DataSourceKind=OleDb
    DataSourcePath=data source=”C:\Users\Pc\Desktop\PRUEBA DATOS”;extended properties=”dBASE IV”;provider=Microsoft.ACE.OLEDB.12.0

    The same error displays while trying to connect to the example files of the ethiopian weather DB, do you have any idea or suggestion about how can I solve this?

    1. Hi Martin, when I’ve hit a similar error (Microsoft.ACE.OLEDB.12), the issue was an architecture mismatch where you have 32-bit data providers and the app needs 64-bit (or vice versa). Check out this post in the Power BI community to see if it can help you narrow it down. If that doesn’t do the trick, you may want to post to the Power BI community forum for some additional assistance. Good luck!

      https://community.powerbi.com/t5/Desktop/quot-Microsoft-Access-The-Microsoft-ACE-OLEDB-12-0-provider-is/td-p/109184

  11. DoryO says:

    I was too impatient for all this. So I just created a blank Access file (.accdb) and created a linked table pointing to the .DBF.
    Then used the .accdb file as my data source. The .mdb data flows right through. Took 30 seconds. Done.

    1. Great suggestion, thanks!

Comments are closed.

Skip to main content