Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Ylreeb
Helper I
Helper I

Exporting Data to a MySQL-DB using R-scripting - works but indicates Error

Hi All,

 

I'm using the follwing R-scripting within a function, that exports data via R-odbcDriverConnect to an MySQL database. After figureing out some issues in transforming PBI time-data types, setting the data-column in PBI to "Date/Time" and creating the corresponding DB-table with "datetime" is working so far....see Workbench screenshot:

 

pbi_to_my_sql.PNG

 

Here is my test-setup for that scenario:

 

Source-Table (incl .setting the date-typ):

let
Source = #table(
{ "firstname", "lastname", "customerid", "date"},
{{"Hello","WORLD", "7", "2015-01-01"},

{"G-Day","AUSTRALIA", "8", "2015-01-02"},

{"Gruezi","SWITZERLAND", "9", "2015-02-01"}}
),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"firstname", type text}, {"lastname", type text}, {"customerid", Int64.Type}, {"date", type datetime}})
in
    #"Changed Type"

 

PBI-Function:

let func =  
 // Updates the db.test_pbi_r_mysql.test_pbi1.
(dataset as table) =>

let

    RScript = R.Execute(
                        "                 
                            library(RODBC)
                            conn <- odbcDriverConnect(#(0022)DSN=MTO_MySQL; database=test_pbi_r_mysql; case=nochange#(0022))
                            sqlSave(conn, dataset, tablename=#(0022)test_pbi_r_mysql.test_pbi#(0022), rownames=FALSE, append=TRUE)
                            close(conn)
                            ",
                            [dataset=dataset]),
    output = RScript{[Name="output"]}//[Value]

in

    output
, documentation = [
Documentation.Name =  " Table.ExportToMySQL
", Documentation.Description = "
" , Documentation.LongDescription = "
", Documentation.Category = " Table.Others
", Documentation.Source = " .
", Documentation.Author = " MTO
", Documentation.Examples = {[Description =  " Ask Marcus of MTO for details
 ", Result = "
"]}]
 in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

 

MySQL-SQL-Statement:

CREATE TABLE `test_pbi` (   
    `customerid` int(11) NOT NULL,   
    `firstname` varchar(255) DEFAULT NULL,   
    `lastname` varchar(255) DEFAULT NULL,  
    `date` datetime DEFAULT NULL,  
    PRIMARY KEY (`customerid`) )
    ENGINE=InnoDB DEFAULT CHARSET=utf8

 

As allready mentioned - it works in terms of appending "fresh" rows to the MySQL-table. But every (additional) function invokation indicates that a the following error in PBI(Deskop):

An error occurred in the ‘’ query. DataSource.Error: ADO.NET: R script error.
Error in sqlSave(conn, dataset, tablename = "test_pbi_r_mysql.test_pbi",  :
  unable to append to table 'test_pbi_r_mysql.test_pbi'
In addition: Warning message:
In odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  :
  character data '2015-01-01T00:00:00.0000000' truncated to 19 bytes in column 'date'
Execution halted
Warning message:
closing unused RODBC handle 1

 

Someone any idea??

 

Thank in advance,

Marcus

6 REPLIES 6
Anonymous
Not applicable

Hi ,

I am new to power bi. i want export data from power bi to mysql. Please can u help in understanding the things and flow with software setup.

Please help me...

 

Thanks,

UBG

Hi,

 

it's quite easy.

You need to install and configure the R-runtime - just follow the MS instructions.

 

When you've processed your table in the way you wanted to store add a "Run R script" step.

In the script window fill in somekind like this:

library(RODBC)
conn <- odbcDriverConnect("DSN=ODBCDATASourceName; database=yourDBName; case=nochange")
sqlSave(conn, dataset, tablename="yourTableName.yourDBName;", rownames=FALSE, append=TRUE)
close(conn)

 

Ensure you've setup your MariaDB/MySQL properly so that your are able to interface it with ODBC Data Source Client on the maschine your are running PBI-Dektop on. Next you have to consider the datatypes of your culmns in PBI and your DB. Maria sometimes struggles with Date-Types exported by PBI.

As you see, the script APPENDS the PBI-table to the existing data stored in your DB. I tried to figure out just to update the data.

Therefor a (unique) index in the DB-table is in needed (also for some kind of DB maintenance). I spend nearly a day into this but run into errors in PBI (maybe the R interoperability bridge to .net)....

 

Regrads!!

 

I'm still

 

 

Anonymous
Not applicable

thank you for sharing...

i need small clarification on

library(RODBC)
conn <- odbcDriverConnect("DSN=ODBCDATASourceName; database=yourDBName; case=nochange")
sqlSave(conn, dataset, tablename="yourTableName.yourDBName;", rownames=FALSE, append=TRUE)
close(conn)

 

i dont know mysql, please help in understaing what are the  fields i need to update and where to see ODBCDATASourceName,yourDBName etc.

i am unble to understand syntax.

please help...

your ODBC Data Source....highlighted the ODBCDATASourceName:

 

blaa.PNG

v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @Ylreeb,

 

I am not familiar with R script. But, per my research, I found this article provide an example about exporting data to SQL. 

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana Gu,

 

as you might have seen, my created table has some similar values as your linked article Smiley Wink

My scenario based on that - but extended by an ID as DB primary key and a date to cover even more relevant...

 

But trying this based on an external data source (in this case CSV's-files) I run in this known global PBI Desktop issue "Formula.Firewall: Query references other queries, so it may not directl...."

 

It's a pain 😞

 

Regards,

Marcus

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.