CODEWITHSUNDEEP
powershell
MISNole
MISNole

Reputation: 1062

PowerShell - Sorry, we couldn't find Microsoft.PowerShell.Core\FileSystem::

I'm trying to modify the script created by Boe Prox that combines multiple CSV files to one Excel workbook to run on a network share.

When I run it locally, the script executes great and combines multiple .csv files into one Excel workbook.

Clear-Host
$OutputFile = "ePortalMonthlyReport.xlsx"
$ChildDir = "C:\MonthlyReport\*.csv"
cd "C:\MonthlyReport\"

echo "Combining .csv files into Excel workbook"
. C:\PowerShell\ConvertCSVtoExcel.ps1
Get-ChildItem $ChildDir | ConvertCSVtoExcel -output $OutputFile
echo " "

But when I modify it to run from a network share with the following changes:

Clear-Host
# Variables
$OutputFile = "ePortalMonthlyReport.xlsx"
$NetworkDir = "\\sqltest2\dev_ePortal\Monthly_Report"
$ChildDir = "\\sqltest2\dev_ePortal\Monthly_Report\*.csv"

cd "\\sqltest2\dev_ePortal\Monthly_Report"

echo "Combining .csv files into Excel workbook"
. $NetworkDir\ConvertCSVtoExcel.ps1
Get-ChildItem $ChildDir | ConvertCSVtoExcel -output $OutputFile
echo " "

I am getting an error where it looks like it using the network path twice and I am not sure why:

Combining .csv files into Excel workbook

Converting \sqltest2\dev_ePortal\Monthly_Report\001_StatsByCounty.csv naming worksheet 001_StatsByCounty --done

opening csv Microsoft.PowerShell.Core\FileSystem::\sqltest2\dev_ePortal\Monthly_Report\\sqltest2\dev_ePortal\Monthly_Report\001_StatsByCounty.csv) in excel in temp workbook

Sorry, we couldn't find Microsoft.PowerShell.Core\FileSystem::\sqltest2\dev_ePortal\Monthly_Report\\sqltest2\dev_ePortal\Monthly_Report\001_StatsByCounty.csv. Is it possible it was moved, renamed or deleted?

Anyone have any thoughts on resolving this issue?

Thanks,

Upvotes: 0

Views: 1655

Answers (2)

Travis Clark
Travis Clark

Reputation: 1

Much of the issues are caused in almost every instance where the script calls $pwd rather than $PSScriptRoot. Replace all instances with a quick find and replace.

$pwd looks like: 

PS Microsoft.PowerShell.Core\FileSystem::\\foo\bar

$PSScriptRoot looks like:

\\foo\bar

The second part i fixed for myself is what @TessellatingHeckler pointed out. I took a longer approach.

It's not the most efficient way...but to me it is clear.

[regex]$regex = "^\w\:\\"
[regex]$regex2 = "^\\\\"

$test = 0

If ($regex.ismatch($input) -and $test -eq 0 ) {
$tempcsv = $excel.Workbooks.Open($input)
$test = 1 }

If ($regex.ismatch("$($input.fullname)") -and $test -eq 0) {
$tempcsv = $excel.Workbooks.Open("$($input.fullname)") 
$test = 1}

If ($regex2.ismatch($input) -and $test -eq 0) {
$tempcsv = $excel.Workbooks.Open($input)
$test = 1 }

If ($regex2.ismatch("$($input.fullname)") -and $test -eq 0) {
$tempcsv = $excel.Workbooks.Open("$($input.fullname)")
$test = 1}

If ($test -eq 0) {    
$tempcsv = $excel.Workbooks.Open("$($PSScriptRoot)\$input") 
$test = 0 }

Upvotes: 0

TessellatingHeckler
TessellatingHeckler

Reputation: 28963

Because in the script it uses the following regex:

[regex]$regex = "^\w\:\\"

which matches a path beginning with a driveletter, e.g. c:\data\file.csv will match and data\file.csv will not. It uses this because (apparently) Excel needs a complete path, so if the file path does not match, it will add the current directory to the front of it:

#Open the CSV file in Excel, must be converted into complete path if no already done
    If ($regex.ismatch($input)) {
        $tempcsv = $excel.Workbooks.Open($input) 
        }
    ElseIf ($regex.ismatch("$($input.fullname)")) {
        $tempcsv = $excel.Workbooks.Open("$($input.fullname)") 
        }    
    Else {    
        $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")      
        }

Your file paths will be \\server\share\data\file.csv and it doesn't see a drive letter, so it hits the last option and jams $pwd - an automatic variable of the current working directory - onto the beginning of the file path.

You might get away if you edit his script and change the regex to:

[regex]$regex = "^\w\:\\|^\\\\"

which will match a path beginning with \\ as OK to use without changing it, as well.

Or maybe edit the last option (~ line 111) to say ...Open("$($input.fullname)") as well, like the second option does.

Upvotes: 1

Related Questions