CODEWITHSUNDEEP
powershell
Nima
Nima

Reputation: 39

Comparing two CSV files in powershell and creating an output

I'm currently working on a script to import 2 csv files, compare the fqdn columns and output the results to a file.

The issue is after many hours of testing I'm at the point that it looks like my script is working up until the point of getting the path for each file that needs to be imported but I can't seem to get the import-csv command to do what I need it to.

I'd appreciate any guidance you can provide.

My script so far and the error I'm getting are below:

$CMDB_Installed = Get-ChildItem -Path C:\Users\nha1\Desktop\Reports\CMDBInstall | Sort CreationTime -Descending | Select -expa FullName -First 1 | Out-String

$SCOM_AgentList = Get-ChildItem -Path C:\Users\nha1\Desktop\Reports\SCOMUAT | Sort CreationTime -Descending | Select -expa FullName -First 1 | Out-String



$SL = Import-Csv $SCOM_AgentList

$CL = Import-Csv $CMDB_Installed



Compare-Object -ReferenceObject $CL -DifferenceObject $SL -Property fqdn |

Export-Csv -NoTypeInformation -Path = C:\Users\nha1\Desktop\Reports\AuditOutput\UATNeedsAgent+SCOM\UATHosts-NotinSCOM$(get-date -format yyyy-MM-dd).csv

Error Message:

import-csv : Illegal characters in path.

At line:4 char:7

+ $SL = import-csv $SCOM_AgentList

+       ~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OpenError: (:) [Import-Csv], ArgumentException

    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand


import-csv : Illegal characters in path.

At line:5 char:7

+ $CL = import-csv $CMDB_Installed

+       ~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OpenError: (:) [Import-Csv], ArgumentException

    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

Upvotes: 1

Views: 148

Answers (1)

mklement0
mklement0

Reputation: 437111

$CMDB_Installed = ... | Select -expa FullName -First 1 | Out-String

Don't use Out-String, unless you want a for-display, multi-line string representation.

Your file-path variables therefore contain newlines (line breaks), which Import-Csv complains about, given that newlines in file names are illegal in NTFS (on Windows).

Simply omit the Out-String call, given that Select -expa FullName -First 1 by definition already outputs a string (given that the .FullName property on the objects output by Get-ChildItem is [string]-typed).

To recreate the problem:

PS> Import-Csv "foo`n"  # illegal line break in file path
Import-Csv : Illegal characters in path.

To demonstrate that Out-String produces a multi-line string even with a single-line string as input:

PS> ('foo' | Out-String).EndsWith("`n")
True

Upvotes: 1

Related Questions