How to connect to local .sql file?

[CloudMonkey]

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

[Anonymous]

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? 

[v-stephen-msft · Accepted solution]

Hi @CloudMonkey ,

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.

[amitchandak]

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

What is the expected behavior?