Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to connect to local .sql file?

Hello,

 

Please can you tell me how to connect to a local .sql file? (it has many sql tables within it)

 

Googling seems to only return examples for connecting to servers but I have the file locally on my computer and it's not username/password protected.

 

Thank you for your help,

 

CM

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    It is not possible to load a .SQL file in a Power BI report, but you can specify a SQL query when adding a data source, if you expand the Advanced Options:

     

    If your query is complex you can create a stored procedure to wrap all of the code and then call the stored procedure from Power BI, e.g. EXEC NameOfYourStoredProcedure

     

    Reference: Possible to load .SQL file to Power bi?

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous . You can connect to .sql as a text file. That will give a table with SQL(I think so).

     

    What is the expected behavior?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    It is not possible to load a .SQL file in a Power BI report, but you can specify a SQL query when adding a data source, if you expand the Advanced Options:

     

    If your query is complex you can create a stored procedure to wrap all of the code and then call the stored procedure from Power BI, e.g. EXEC NameOfYourStoredProcedure

     

    Reference: Possible to load .SQL file to Power bi?

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I read the SQL as text file, add a custom column, pivot and adjust to be text combine with delimiter, drop excess column, and then go into advanced editor and make Sql database query. Here is some code if you want to try yourself:

     

     

     

    let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("absolute_file_path.sql"))}),
    #"for pivot" = Table.AddColumn(Source, "Custom", each 1),
    pivoted = Table.Group(#"for pivot", {"Custom"}, {{"Sql", each Text.Combine([Column1], " 
"), type text}}),
    #"SQL Query Readable" = pivoted{[Custom=1]}[Sql],
    #"Output" = Sql.Database("your-server.database.windows.net", "your-database-name", [Query=#"SQL Query Readable"])
in
    #"Output"

     

     

    This way you do not need to go in and change the query content in the Power BI report. Nice if changing the file a lot because then you do not need to republish report every single time. Makes dataset depend on SQL in file and not SQL in query editor.

     

    This probably has some security flaws and is more of a loophole, if file is formatted weird perhaps this could break?