다음을 통해 공유

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

  • Anonymous
    February 24, 2018
    Thanks, dBase support.and I hope to support dBase as Power BI's data source for more easy operation.
  • Anonymous
    March 01, 2018
    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?
    • Anonymous
      March 02, 2018
      Hi Priscilla, can you point me to example files where I can try this out?
      • Anonymous
        March 02, 2018
        Hi Samuel,Hmm... that's a bit tricky... can't find any sample files.Can i send you the files through a private message ?
        • Anonymous
          March 03, 2018
          Yes, send me an email at samles @ microsoft . com.
          • Anonymous
            July 17, 2018
            Samuel,Did you find an answer to the FPT file being a separate file.
  • Anonymous
    March 25, 2018
    I seem to have error msg. Any workaround to this issue?
    • Anonymous
      March 27, 2018
      What is the error message you are hitting and what is the scenario when you encounter this?
  • Anonymous
    April 13, 2018
    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 = -2147467259can help me in the resolution of this connection to the DBF table.Greetings.
    • Anonymous
      April 17, 2018
      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
    • Anonymous
      September 03, 2018
      Jorge, pudiste hacerlo funcionar, uso igual el SAI y es un problema con este tema de bases de datos obsoletas.
  • Anonymous
    April 15, 2018
    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=-2147467259Tried, OleDB 15, dbase 5.0, but still no go - can you check if this still works for you?
    • Anonymous
      April 17, 2018
      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.
      • Anonymous
        February 20, 2019
        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=-2147467259Could 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 PCLooking forward to your responseThanksV4VB
  • Anonymous
    June 26, 2018
    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
  • Anonymous
    July 27, 2018
    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.
  • Anonymous
    July 27, 2018
    Thank you Sam. This works perfectly.Getting Started Tip for others with Excel 2016:Click on Data -> New Query -> From Other Sources -> Blank QueryDon't forget to change the file path and file name in the Query 1 Code
  • Anonymous
    September 04, 2018
    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
    • Anonymous
      September 04, 2018
      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.
  • Anonymous
    November 19, 2018
    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.0The 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?
    • Anonymous
      November 20, 2018
      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
  • Anonymous
    June 09, 2019
    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.
    • Anonymous
      June 13, 2019
      Great suggestion, thanks!