I recently was having a challenge which we all have had when working with Power BI.

The customer I was consulting to have all their data extracts saved in XLS (The dreaded XLS!)

I thought that there must be a way to convert them quickly and easily, which I found after Googling ( I honestly cannot find the link to where I got most of the script to give them credit) and as I like to call hacking a PowerShell script.

Now there is no reason to be scared of PowerShell it is extremely powerful and with my example below you only need to change one variable in the PowerShell script, and it will do the conversion for you.

Not only that you can automate PowerShell scripts so that they can be run on a schedule, which could be part of your Power BI data refresh schedule.

NOTE: You have to have Excel installed.

Here are the steps below for the PowerShell script

  • I opened PowerShell and ran as Administrator
  • I then copied the script below
$folderpath = "C:\Convert XLS"
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse | 
ForEach-Object `
{
    $path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
    
    "Converting $path"
    $workbook = $excel.workbooks.open($_.fullname)
 
    $path += ".xlsx"
    $workbook.saveas($path, $xlFixedFormat)
    $workbook.close()
    
    $oldFolder = $path.substring(0, $path.lastIndexOf("\")) + "\old"
    
    write-host $oldFolder
    if(-not (test-path $oldFolder))
    {
        new-item $oldFolder -type directory
    }
    
    move-item $_.fullname $oldFolder
    
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()
  • The only line you change is Line 1 which is the $folderpath

In my example I had the following

I then changed the $folderpath to “C:\Convert XLS”

I then ran the script, which if you are running on your computer will open and close Excel, which is nothing to be concerned with.

Once completed I then went back to my folder and I had the following XLSX files

And all the XLS files are moved to the old folder

Conclusion

I was now able to import the data into Power BI and use it for some amazing reports.

I hope that you found this useful and if you got any questions or suggestions please leave them in the comments below.