Export data to SQL

Hits: 23724

Update 14. May 2019:
There is another approach using Power Query in SSIS:
https://www.powerbi-pro.com/power-query-in-ssis/

Since the launch of Power Query and even more Power BI Desktop, there have always been questions about the possibility to export data to SQL Databases from Power Query or Power BI.

As of today this isn’t possible as neither Power Query nor Power BI Desktop offers a native way to do that. But it works with the implementation of “R”, at least in Power BI Desktop.

But before we dive into the topic, I have to admit that usually I write my blogs in German. So, please I apologize if you have trouble understanding my writing….. 

Prerequisites

To achieve the goal of exporting data from PBI Desktop to a SQL Database, you have to install R-Studio. And needless to say PBI Desktop and a SQL Database. And please be sure to have installed the R library “RODBC”.

Let’s start

Let us start with a simple set of data by writing the following code:

let
Source = #table(
{"CustomerId", "FirstName", "LastName"},
{
{"Hello","WORLD", "1"},
{"G-Day","AUSTRALIA", "2"},
{"Gruezi","SWITZERLAND", "3"}
}
)
in Source

Not so high sophisticated but it works for demo purposes.

Then call the “R-Script” and type the following code:
library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server; server=NBXYZ; Database=ExcelDemo")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="Customers",rownames=FALSE, safer=FALSE, append=TRUE)
close(conn)

That’s all!

What does the “R-Script”?

• The first line “library(RODBC)” calls the library in question.
• The second line “conn <- odbcDriverConnect(“driver=SQL Server; server=NBXYZ; Database=ExcelDemo”)” declares a variable “conn” and assigns the driver, the servername and the Database.
• The third line does some error checking “odbcClearError(conn))
• The fourth line “sqlSave(conn, dataset, tablename=”Customers”, rownames=FALSE, safer=FALSE, append=TRUE)” saves the data set to the SQL Database.
• The last line “close(conn)” closes the connection.

Final Thoughts

After searching for a while to achieve this, I stumbled upon the solution, when I wrote an article about exporting data from PBI Desktop to a .csv file. After finding the solution, it seems to be very simple….

I’m pretty sure that there are more solutions out there to export data to SQL, but until today I haven’t found an article about this topic.

Und für meine deutschsprachigen Freunde

Der Artikel beschreibt die Möglichkeit, Daten aus Power BI Desktop in eine SQL Datenbank zu exportieren. Alles was Ihr dazu braucht ist der Power BI Desktop, R-Studio und natürlich eine SQL Datenbank.
Im Weiteren habe ich ein einfaches Datenset erstellt und dieses mit dem wirklich trivialen R-Code in die Datenbank exportiert. Das war schon alles.

Happy Querying!