Comparing two CSV files in powershell and creating an output

Question

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

mklement0 · Accepted Answer · 2018-11-10 19:32:54Z

1

$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

edited Nov 10, 2018 at 19:32

answered Nov 10, 2018 at 19:21

mklement0

452k68 gold badges728 silver badges987 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing two CSV files in powershell and creating an output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related