question

SnehasishNandy-5618 avatar image
0 Votes"
SnehasishNandy-5618 asked SnehasishNandy-5618 published

Error while writing data to csv and exporting to existing excel

Hi All

I am writing a csv file and then trying to put it in an existing excel file. I am using ImportExcel and PSExcel modules

 foreach($data in $WorkSheetNames)
 {
     $obj = New-Object PSObject
     $final_data ="=HYPERLINK(`"#" + $data + "!A" +$count + """"+ "," + """" + "$data" + """)"
    
     if($final_data -match "Index")
     {
     }
     else
     {
     Add-Member -InputObject $obj -MemberType NoteProperty -Name "Index" -Value $final_data
     }
     $table +=$obj
     $count++
        
 }
 $table | Export-Csv -Path "E:\Index.csv" -Append -NoTypeInformation
 #=HYPERLINK("#10.124.0.0_27!A2","10.124.0.0_27")
 Import-Csv -Path "E:\Index.csv" | Export-Excel -Path "E:\ipaddress.xlsx" -WorkSheetname 'Index' -MoveToStart


while executing getting the below error. it is generating the csv

CSV Data

"Index"
"=HYPERLINK(""#10.124.0.0_27!A3"",""10.124.0.0_27"")"
"=HYPERLINK(""#10.124.0.32_27!A4"",""10.124.0.32_27"")"
"=HYPERLINK(""#10.124.0.64_27!A5"",""10.124.0.64_27"")"
"=HYPERLINK(""#10.124.1.0_26!A6"",""10.124.1.0_26"")"
"=HYPERLINK(""#10.124.1.128_26!A7"",""10.124.1.128_26"")"
"=HYPERLINK(""#10.124.1.192_26!A8"",""10.124.1.192_26"")"


Error

Add-Worksheet : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its
properties do not match any of the parameters that take pipeline input.
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.2.2\Public\Export-Excel.ps1:129 char:26
+ $ws = $pkg | Add-Worksheet @params
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (OfficeOpenXml.ExcelPackage:PSObject) [Add-Worksheet], ParameterBindingException
+ FullyQualifiedErrorId : InputObjectNotBound,Add-Worksheet


Could not get worksheet Index
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.2.2\Public\Export-Excel.ps1:135 char:16
+ catch {throw "Could not get worksheet $WorksheetName"}
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (Could not get worksheet Index:String) [], RuntimeException
+ FullyQualifiedErrorId : Could not get worksheet Index



Can you please tell me what is the issue? Is it the data which I am trying to write to csv?

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers