Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MatiasVizzari
Helper I
Helper I

Dbf bulk query

Good morning, I am still trying to create bulk connection for tables in DBF and am not able to find a solution, to see if anyone can help me a little
I have an excel with a list of branches, in column F of that excel I have the names of each one of the databases that I need to bring.
You would have to do a query that opens protruding, goes through column F (compute that over time), and overrides the base name in the query path.

As I imagine:

 

load locales.xlsx
for i Locals row
BASE - Column 7.Fila

Let
Source: OleDb.DataSource ("Microsoft.ACE.OLEDB.12.0 Provider; Data Source: C: - Users, matiasvizzari, Documents, Lynx, (BASE), DBF, DBF; extended properties: dBASE IV",
[Query: "select * from [ctb.dbf]"])
next i
In

I am sorry for the horrors in the example of how I imagine it, but it is because I have no idea how to correctly identify it.
From already thank you very much,
Cheers

1 ACCEPTED SOLUTION
MatiasVizzari
Helper I
Helper I

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.

View solution in original post

3 REPLIES 3
MatiasVizzari
Helper I
Helper I

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.

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 Entities /Table.
If you don't have a premium, make your transformations on Desktop from your DBF converted to a Power BI Dataflows

v-juanli-msft
Community Support
Community Support

Hi @MatiasVizzari 

You could refre to the following guides:

https://www.youtube.com/watch?v=qdcAoZU8B8Q

https://www.youtube.com/watch?v=BrMHv6zVsrI

 

Best Regards

Maggie

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors