Export M ( power query ) query result to csv

Question

Basically I have about 50 excels worksheets with information to process. Every worksheet has a power query script inside to clean the information. I need to automate this process by any way possible.

I've downloaded power query SDK, in visual studio I run the .pq file and it works, but i cant find a way to write the query result to a file (or insert it directly to a sql server table).

I've tried this approach and it works (using R on a power BI book to export it to a file with the write.table command). The problem is that I dont know how to trigger the refresh without opening the book. (so I can add it to the job list).

The refresh every X time option in power BI is not useful here, I need to trigger the refresh after part of the sql jobs are done. So I need to trigger by some type of API.

Any type of solution is welcome.

Answer 1

I have a similar task and I'm using a Powershell script to open the excel file, refresh all worksheet and then export each to CSV. Then simply schedule the job as you like.

#Open XLSX Processor
$E = new-object -comobject excel.application
$E.Visible = $false
$E.DisplayAlerts = $true
$Workbook = $E.Workbooks.Open($File_XLSX)  

#Refresh all Queries
foreach ($conn in $Workbook.Connections){
  try{ $conn.OLEDBConnection.BackgroundQuery = $false } catch {}
  try{$conn.ODBCConnection.BackgroundQuery = $false } catch {}
}
$Workbook.RefreshAll()
$E.CalculateUntilAsyncQueriesDone()

# Export XLSX to CSV 
foreach ($Worksheet in $Workbook.Worksheets)
{
    $n = $File_XLSX + "_" + $Worksheet.Name
    $Worksheet.SaveAs($csvLoc + $n + ".csv", 6)
}
$Workbook.Close($false)

#Closing Excel
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Worksheet)|out-null
$Worksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook)|out-null
$Workbook=$null
# Close Excel
$E.quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($E)|out-null
$E=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()