Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good night, How are you?
I would need to know if someone made or knows how to make an automatic half query to load databases in .DBF, I have approximately 100 branches with more or less 12 tables each and each table in a .DBF file, it will be loaded table by table with a query, but I can't find the way or if it's possible to do a bulk load so I don't have to do the queries 1 by 1.
Does anyone know if it is possible?
Thank you in advance,
Cheers.
Solved! Go to Solution.
Good morning, I have already been able to solve the problem, if someone can help you, watching tutorials and reading about power query I found that having a reference excel and generating a GetData function for each of the necessary tables, I was able to bring from all the bases the tables and combine them into one, this is perhaps too punctual, I needed them agreed, but I share it in case someone ever serves it even if it is for reference
Getdata table Cli.dbf
(Nlocal)=> let Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true), Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data], LOCAL = Sheet1_Sheet{Nlocal}[Column9], Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\LINCEV3\"&LOCAL&"\DBF\;extended properties=dBASE IV", [Query="select * from [cli.dbf]"]) in Source
the excel that is called in the query has an ID per database, the name of the database and the name of the directory folder that has the database
ex: 1 Castelar caste
Query calling Getdata
let Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true), Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data], #"Encabezados promovidos1" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos1",{{"UBICACIÓN", type text}, {"AGRUPAMIENTO", type any}, {"NOMBRE_LOCAL", type any}, {"NOMBRE_LOCAL_TOTAL", type any}, {"COD_LOCAL", Int64.Type}, {"CONCEPTO", type text}, {"SSS", type text}, {"TIPO", type text}, {"LOCAL", type text}, {"Activo", Int64.Type}, {"Orden", Int64.Type}}), #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Personalizado", each GetData([COD_LOCAL])), #"Otras columnas quitadas" = Table.SelectColumns(#"Personalizada agregada",{"COD_LOCAL", "Personalizado"}) in #"Otras columnas quitadas"
I hope I have not made it very confusing, thank you all very much,
Cheers.
Good morning, I have already been able to solve the problem, if someone can help you, watching tutorials and reading about power query I found that having a reference excel and generating a GetData function for each of the necessary tables, I was able to bring from all the bases the tables and combine them into one, this is perhaps too punctual, I needed them agreed, but I share it in case someone ever serves it even if it is for reference
Getdata table Cli.dbf
(Nlocal)=> let Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true), Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data], LOCAL = Sheet1_Sheet{Nlocal}[Column9], Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\LINCEV3\"&LOCAL&"\DBF\;extended properties=dBASE IV", [Query="select * from [cli.dbf]"]) in Source
the excel that is called in the query has an ID per database, the name of the database and the name of the directory folder that has the database
ex: 1 Castelar caste
Query calling Getdata
let Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true), Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data], #"Encabezados promovidos1" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos1",{{"UBICACIÓN", type text}, {"AGRUPAMIENTO", type any}, {"NOMBRE_LOCAL", type any}, {"NOMBRE_LOCAL_TOTAL", type any}, {"COD_LOCAL", Int64.Type}, {"CONCEPTO", type text}, {"SSS", type text}, {"TIPO", type text}, {"LOCAL", type text}, {"Activo", Int64.Type}, {"Orden", Int64.Type}}), #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Personalizado", each GetData([COD_LOCAL])), #"Otras columnas quitadas" = Table.SelectColumns(#"Personalizada agregada",{"COD_LOCAL", "Personalizado"}) in #"Otras columnas quitadas"
I hope I have not made it very confusing, thank you all very much,
Cheers.
Hi @MatiasVizzari ,
Pls check whether below post would help:
https://www.excelforum.com/excel-general/1195425-append-multiple-dbf-files-in-powerquery.html
Or maybe you could convert .dbf files to csv files,then save them as a folder,finally check below reference.
https://powerpivotpro.com/2016/12/import-csv-files-folder-filenames-power-bi/
@MatiasVizzari , refer if this has some information to help you out
https://powerbi.microsoft.com/en-us/blog/bulk-operations-in-the-admin-portal/
Thank you very much for the answer, but this refers to tasks about operations in the power bi portal, not in the connections to databases or DBF as tables, also thanks for the information and understanding.
The only chance that you have to work with DBF files on Power BI is
using Devart OCBC Driver here -->> https://go.helpbi.com/dbf and Power BI Dataflows
First, create a Dataflows and then on another dataflow( if you have premium per user) make all the data mashup on a reference Enties /Table.
If you don't have a premium, make your transformations on Desktop from your DBF converted to a Power BI Dataflows
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |