Export data from Power Query to Local Machine or SQL Server using R scripts

Posted on by Leila Etaati

There is always a discussion on how to store back the data from Power BI to local computer or SQL Server Databases, in this short blog, I will show how to do it by writing R scripts inside Power Query.

 

First how to Store Files to PC

To store data back to a file location in your PC, we need to write one line of code in R.

In my scenario, first, I get some data from https://www.hbo.com/game-of-thrones/cast-and-crew

then I applied some changes to the data using Power query as you have seen in below picture, I applied more than 20 steps to fetch the images

Now I want to store back it into my PC, to do that, you need to navigate to Power Query Editor, then click on Transform, then Run R Scripts

 

Then in the new R script Editor write the below code, the dataset variable holds all queries,

in R scripts we able to use a function name write.table, you need to pass the dataset and file location, then the separation of CSV file that is in my scenario cama,

# 'dataset' holds the input data for this script
write.table(dataset, file="e://Fundataset//game-of-thrones//GOTImageCharacter.csv",sep=",",row.names=F)

Then push the OK bottom, then just check the location of the e://Fundataset//game-of-thrones//GOTImageCharacter.csv

you should see the file there, it was not a big dataset, I applied the same code for 60000 rows of data from AdventureWorks Fact internet sales table, it also took 10 seconds.

 

Second how to Store Files to SQL Server

In this example, I am going to store the data in SQL Server, the same file into SQL Server 2017. to do that you need to access to the specific package: RODBC

You need to install this package in R Studio.

In below codes write the codes,

access the RODBC package, then you need to use a function odbcDriverConnect, you need to specify the driver, server, database name and so forth.

then you need to use sqlSave, you need to use SQL connection, dataset, and name of the table.

library(RODBC) 
DBHANDLE<-odbcDriverConnect('driver={SQL Server};server=.\\ML2017;database=Ai;trusted_connection=true')

sqlSave(DBHANDLE, data.frame(dataset), tablename = "GOT",rownames=FALSE, append = FALSE)

when you run it, you may receive below error!

R will run twice, so it will store the file twice in the computer, you will get an error that file already exists.

To solve that, we need to put a try keyword in front of the Power Query as you can see in the below code.

just check the SQL Server table

 

to check if there is a possibility to check the dataset if exist not write the code there

 

TBLExist="GOTImage" %in% sqlTables(DBHANDLE)$TABLE_NAME
if (!TBLExist)

this will help, to check the existence of a table

# 'dataset' holds the input data for this script
#write.table(dataset, file="e://Fundataset//game-of-thrones//GOTImageCharacter.csv",sep=",",row.names=F)

library(RODBC) 
outputframe=data.frame(dataset)
DBHANDLE<-odbcDriverConnect('driver={SQL Server};server=.\\ML2017;database=Ai;trusted_connection=true')

TBLExist="GOTImage" %in% sqlTables(DBHANDLE)$TABLE_NAME
if (!TBLExist)
sqlSave(DBHANDLE, data.frame(dataset), tablename = "GOTImage",rownames=FALSE, append = FALSE)

by putting this code you did not get any error regarding the existing database.

 

 

Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

17 thoughts on “Export data from Power Query to Local Machine or SQL Server using R scripts”

Leave a Reply