Forum Discussion
Integrate PowerBI with Netsuite
If you enable webquery on a saved report, you can then download a .igy file,
open this in a text editor strip out everything before htts:\\
and strip out the following from the end
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
replace
["emailaddress","Please enter your email address:"]
with
then copy the string and go into PBI and got o data sources and use a web data source, paste this in and use an anonmous authentication, and away you go
i've also created a powershell scrip with a smilar function to sync to local MSSQL DB and then you can point PBI to a SQL DB, personal prefernce which way round you want to access your data
Would you be willing to share the PowerShell script you create for pulling data from NetSuite?
sure this is the PSS i used to go into a local SQl server
Invoke-Sqlcmd -Query "DELETE FROM [Netsuite].[dbo].[Customer_Product_Sales];" -ServerInstance "#########\SQLEXPRESS"
$url ="web address from Netuite as described in my original post"$WebResponse = Invoke-WebRequest -uri $url
$content = $WebResponse.Content
$content = $content.replace('<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body><table>', "" )
$content = $content.replace('</table></body></html>', "")
$content = $content.replace('</td>', "")
$content = $content.replace('</tr>', "")
$content = $content.replace('<tr>', "#" )
$content = $content.replace('<td>', "^^")
$content = $content.replace("`t|`n|`r", "")
$array = $content.Split('#')
ForEach ($tr in $array)
{
$TD = $tr.Split('^^')
if ($TD.count -gt 1)
{
$CustomerID = $TD[4] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$Customer = $TD[6] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$Customer_Ref = $TD[8] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$QTY = $TD[10] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=",""
$Year = $TD[12] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$yyy_MM = $TD[14] -replace "`n","" -replace "`r","" -replace "'",""
$Total_Revenue = $TD[16] -replace "`n","" -replace "`r","" -replace "=",""
$Brand = $TD[20] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$Category = $TD[22] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$Item = $TD[24] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""
$Description = $TD[26] -replace "`n","" -replace "`r","" -replace "=-","" -replace "=","" -replace "'",""Invoke-Sqlcmd -Query "INSERT INTO [Netsuite].[dbo].[Customer_Product_Sales] (Customer_ID , Customer , Brand , Category , Item , Customer_Ref , Description , Qty , Total_Revenue, Year , YYYY_MM) VALUES ('$CustomerID', '$Customer', '$Brand', '$Category', '$Item', '$Customer_Ref', '$Description', '$Qty', '$Total_Revenue', '$Year', '$yyy_MM');" -ServerInstance "#######\SQLEXPRESS"
}}
Thanks!
Do you know if any specific features need to be turned on in NetSuite to enable this kind of access?