Excel mysql pass parameters

%3CLINGO-SUB%20id%3D%22lingo-sub-1458986%22%20slang%3D%22en-US%22%3EExcel%20mysql%20pass%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458986%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3EI%20try%20connect%20exel%20to%20mssql%20serwer.%20Connections%20work%20great%20except%20WHERE%20clause.%3C%2FP%3E%3CP%3EThis%20is%20my%20simple%20query%20%3A%3C%2FP%3E%3CP%3ESELECT%20*%20FROM%20EMPLOYERS%20WHERE%20ID%20%26gt%3B%20100%3C%2FP%3E%3CP%3EHow%20I%20can%20pass%20where%20condition%20in%20this%20case%20100%20from%20exel%20cell%20%3F%3C%2FP%3E%3CP%3ETil%20now%20work%20%22%3F%22%20and%20then%20exel%20ask%20where%20is%20parameters%20but%20now%20this%20method%20stops%20working.%3C%2FP%3E%3CP%3EI%20found%20this%20%3A%20%3CA%20href%3D%22https%3A%2F%2Fexcelbi.pl%2Fjak-pobrac-parametr-zapytania-sql-z-komorek-arkusza-excel-z-uzyciem-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelbi.pl%2Fjak-pobrac-parametr-zapytania-sql-z-komorek-arkusza-excel-z-uzyciem-power-query%2F%3C%2FA%3E%20work%20ok%20but%20filtering%20is%20perform%20in%20exel%20not%20in%20sql%20so%20performance%20may%20be%20low.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1458986%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1460702%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20mysql%20pass%20parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697661%22%20target%3D%22_blank%22%3E%40pzawodzinski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20ID%20in%20some%20named%20cell%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20178px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198548iE97953BBF35B12F9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20use%20it%20in%20query%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20GetID%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22ID%22%5D%7D%5BContent%5D%5BColumn1%5D%7B0%7D%2C%0A%20%20%20%20queryString%3D%22SELECT%20*%20FROM%20EMPLOYERS%20WHERE%20ID%20%26gt%3B%20%22%20%26amp%3B%20Text.From(GetID)%0A%20%20%20%20%2F%2F%C5%B9r%C3%B3d%C5%82o2%20%3D%20Sql.Database(%22localhost%5Csqlexpress2019%22%2C%20%22SZKOLENIE%22%2C%20%5BQuery%3DqueryString%5D)%0Ain%0A%20%20%20%20queryString%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi.

I try connect exel to mssql serwer. Connections work great except WHERE clause.

This is my simple query :

SELECT * FROM EMPLOYERS WHERE ID > 100

How I can pass where condition in this case 100 from exel cell ?

Til now work "?" and then exel ask where is parameters but now this method stops working.

I found this :

 

 

let
    Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"ID", Int64.Type}}),
    IDFirmy = #"Zmieniono typ"{0}[ID],


    Źródło2 = Sql.Database("localhost\sqlexpress2019", "SZKOLENIE", [Query="SELECT * FROM EMPLOYERS WHERE ID > 100"]),
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło2, each ([ID] = IDFirmy ))
in
    #"Przefiltrowano wiersze"

 

 

 

work ok but filtering is perform in exel not in sql so performance may be low.

1 Reply
Highlighted

@pzawodzinski 

If ID in some named cell as here

image.png

you may use it in query like

let
    GetID = Excel.CurrentWorkbook(){[Name="ID"]}[Content][Column1]{0},
    queryString="SELECT * FROM EMPLOYERS WHERE ID > " & Text.From(GetID)
    //Źródło2 = Sql.Database("localhost\sqlexpress2019", "SZKOLENIE", [Query=queryString])
in
    queryString