Exporting Power BI Desktop data to SQL Server using Python | Sandeep Pawar | PawarBI.com

[@EdgardStultjens]

Thanks for sharing Sandeep!

[@arijitguha8967]

Thank you for the solution. Is there a way to automate data load from Power BI to Sql Server?

[@aldandifitrarahmadhan7660]

did u found the answer ?

[@candyskullxoxo4660]

Hi, i love your gui function !!! But i have the same problem with append. it will append the 3 rows of my test table 3 times. So i got 9 rows instead of 3. Do you now why this happens? I love your Video it is great

[@alaaedine8932]

i have authentication password and username of my database sql server , how add it in ?

[@thabomanare3848]

Is this script the same if I were to export to an Oracle Database?

[@MacPaulos]

Thank you for the info :)
I tested with "Enter data" as my source to add a single row with 3 columns. I'm seeing duplication (twice) of data.
If I apply a change through power query , the data is added 4 times if I load up Power Query.
Any idea on where to look on that?

[@SandeepPawar1]

set if_exists='replace'

[@cnrike]

Hello Mr @Sandeep Pawar.
I would like to thank you very much for this script. I have modified it to run in PostgreSQL, but I found an error with "if_exist" clause when choosing "append": lines are appended twice and sometimes 3 times! It happens when I execute the code in a query on Power BI.
Do you have any idea about solving it or to bypass it?
I am usign to transfer huge volumes of data after being transformed and I like to use this "append" functionality because it allows me to send the DB updates in blocks. But it is a nuissance to have the data duplicated every time I use this append functionality.
Kindest Regards.

[@jurgle31337]

Thank you for the very good solution for exporting BI data to SQL. I have attempted to run the function that you created, and am getting the following error
An error occurred in the ‘SQL_Export’ query. DataSource.Error: ADO.NET: Python script error.
Traceback (most recent call last):
File "PythonScriptWrapper.PY", line 2, in
import os, pandas, matplotlib
ModuleNotFoundError: No module named 'pandas'
Can you please help?

[@SandeepPawar1]

Jonathan looks like you have not installed pandas. did you install python, pandas and sqlalchemy ? If you did, make sure powerbi is poined to the right python directory

[@jurgle31337]

@@SandeepPawar1 I've got the function to work for a small dataset, but after trying on a much larger table with much more data, the Invoked Function just sits there and shows the wait symbol like it's refreshing.

[@SandeepPawar1]

@@jurgle31337 how large is your table? In my case I have exported 3M rows and took abt 30 min.. other option is to use DAX studio

[@jurgle31337]

@@SandeepPawar1 This table is only 500 rows and 30 columns. It shouldn't take that long, but it's been sitting here for about 30 minutes now. It was near instantaneous when doing the test shot.

[@jurgle31337]

@@SandeepPawar1 Will the function give me any output if there is an error on the Server side? Like a reply from the server if a datatype or column discrepancy is found?

[@emmanuelkolawole6720]

the script does not work at all. please fix script. brings error message all the time. see what I used below:
import sqlalchemy
from sqlalchemy import create_engine, MetaData, Table, select
from six.moves import urllib
params = urllib.parse.quote_plus("DRIVER=ODBC Driver 17 for SQL Server; SERVER=DESKTOP-KFHEGRI\SQLEXPRESS; DATABASE=pbi; trusted_connection=yes")
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, echo=False)
connection = engine.raw_connection()
dataset.to_sql(name='from_pbi2', con=engine, index=False, if_exists='append') // or 'replace'

[@emmanuelkolawole6720]

There is SyntaxError: invalid syntax on the last line