import-excel progress bar

[MuPp3t33r]

Hi Doug

Hoping you can help me here, what I'd like to do is to place a progress bar on importing an excel file.
Per the below code I'd like to have the entire worksheet saved into the variable "$database" which I can later refer to using for example $database.ClientCode.
If I run the line like this:
$database = import-excel $datacache -WorksheetName Tasks -StartRow 1 -DataOnly | select ClientCode,ClientSurname,TaskCode,TaskDesc,OfficeCode,GroupCode,ServLineCode,TaskPartner,TaskPartnerName,TaskManager,TaskManagerName
then it loads into the variable just fine and the remainder of my entire script works perfectly. Problem is it takes almost 3mins for the command to finish loading 55k lines from XLSX file, so I wanted to add a progress bar to see the status of the task.

I tried with below code, but I very obviously have done this all wrong, as it still takes 3mins to load (without progress bar) and then spends a minute showing the progress bar climbing up, but my $database variable is empty when done. Almost like its loading into the variable and then write-progress is flushing out the variable.

I accept that I'm being an idiot somewhere, but would be eternally grateful if you could give me some guidance here :) thank you

$datacache = "C:\path\to\data.xlsx" $index = 0 $total = 55645 $database = import-excel $datacache -WorksheetName Tasks -StartRow 1 -DataOnly | select ClientCode,ClientSurname,TaskCode,TaskDesc,OfficeCode,GroupCode,ServLineCode,TaskPartner,TaskPartnerName,TaskManager,TaskManagerName | ForEach-Object { $index++ write-Progress -Activity "Loading database into memory, please be patient as this might take a few minutes..." -Status "$index of $total [$('{0:N1}' -f (($index/$total)*100))%]" -PercentComplete (($index/$total)*100) } Write-Progress -Completed -Activity "make progress bar dissapear"

[MuPp3t33r]

Nevermind, fixed the empty variable issue with $_ at end of write-progress block, though the progress bar becomes redundant because it otherwise would have been in memory by that point. I think I understand now why it's doing this, it's loading the excel file into the variable $datacache and then write-progress is processing that data back into the same variable. I guess I'm just not sure how to show progress on the import-excel part of the job. Any thoughts?

[dfinke]

Nothing off the top of head to "enable" a Progress Bar. It's a good idea. Didn't design it to take it into consideration. I'll see if I can make some time to look into it.

[MuPp3t33r]

Awesome, thanks Doug :)