Schedule upload of daily csv files

[STEV583]

Hi All,

Just getting my head around Power BI. We have a lot of legacy systems that provide us with daily files. I think I understand that you can mannually load and append this data to existing data. However, is it possible to automate this step?

Thanks 

[Anonymous]

Hi @STEV583,

You can use the Power BI PowerShell modules mentioned in this blog to automate the process that upload CSV files to Power BI. Below is the sample script. 

cls
 
$ErrorActionPreference = "Stop"
 
$currentPath = (Split-Path $MyInvocation.MyCommand.Definition -Parent)
 
 
 
#Create Archive Folder
 
new-item -Name "Archive" -Force -ItemType directory -Path "$currentPath\CSVData"  | Out-Null
 
Import-Module "$currentPath\Modules\PowerBIPS" -Force
 
while($true)
 
{
 
                # Iterate each CSV file and send to PowerBI
 
                Get-ChildItem "$currentPath\CSVData" -Filter "*.csv" |% {
 
                                $file=$_              
 
                                #Import csv and add column with filename
 
                                $data = Import-Csv $file.FullName | select @{Label="File";Expression={$file.Name}}, *
 
                                # Send data to PowerBI
 
                                $data |  Out-PowerBI -dataSetName "CSVSales" -tableName "Sales" -types @{"Sales.OrderDate"="datetime"; "Sales.SalesAmount"="double"; "Sales.Freight"="double"} -batchSize 300 -verbose
 
                                # Archive the file
 
                                Move-Item $file.FullName "$currentPath\CSVData\Archive\" -Force
 
                }
 
              
 
                Write-Output "Sleeping..."
 
                Sleep -Seconds 5
 
}

After you firstly upload csv files to Power BI, create report and dashboard using the new dataset,  when a new CSV file is dropped into the \CSVData directory, they will automatically be uploaded, the new data will be appended to the existing Power BI data and the dashboard will update without any manually operations.


Thanks,
Lydia Zhang

[BhaveshPatel]

Yes, You can do this using the Gateway or The other best product I always used is Power Update. For more information, visit at this link

http://www.powerpivotpro.com/2015/02/introducing-power-update/

[STEV583]

Hi All,

Just getting my head around Power BI. We have a lot of legacy systems that provide us with daily files. I think I understand that you can mannually load and append this data to existing data. However, is it possible to automate this step?

Thanks