1

Hello Fellow PS users,

I am trying to read from the csv as per below table. Obviously some values are missing and while I parse and execute and reexport it to csv, its not coming in the desired format, since the size of array is different for each line. How can i deal with empty columns and bind them correctly to the result?

Project StartDate EndDate Activity Desciption Owner

P1 10/10/23 10/10/25 Act1 Activity 1 Joe

P2 10/10/23 10/10/25 Act1 Activity 1 Joe

P3 Dave

foreach($line in $config -split ‘\s’ | ForEach-Object { “$_” })
{
#$iproject,$sd,$ed,$activity,$description,$owner = ($line -split ‘,’)[0,1,2,3,4,5]

$Output = New-Object -TypeName psobject -Property @{

‘Project’=$id
‘SD’=$startDate
‘EDate’=$endDate
‘Activity’=$activity
‘Description’=$description
‘Owner’=$owner
}
$Result += $Output

}

$Result | Select * | export-csv -path ‘C:\work\MHVALUESSorted.csv’ -NoTypeInformation

11 Spice ups
2

Do you you have an actual delimiter character between fields here or is it just a bunch of spaces? Can you show the original input data rather than what has already been manipulated?

2 Spice ups
3

Thank you for the response, Comma is the delimiter thats used.

4

Can you show the original input data before it is manipulated? I see you splitting on spaces as the first thing in your code. It would be nice to have data without seemingly arbitrary spaces.

5

The csv is like this

P1,10/10/23,10/10/25, Act1, Activity 1,Joe

P2,10/10/23,10/10/25, Act1, Activity 1,Joe

P3,Dave

6

If you want to add headers, you can use the -Header parameter on import-csv:

Import-Csv inputFile.csv -Header Project,StartDate,EndDate,Activity,Description,Owner |
	Export-Csv outputFile.csv -NoType
7

Thank you, I can try importing the header, but he values for each rows are dynamically bound. First 2 rows are ok, but while processing the 3rd row, the last value from 2nd row is written to the 3rd row as well which is not the required behaviour. The 3rd row needs only the 1st and last columns to be populated.

8

If you want to add headers but change your output delimiter, you can use -Delimiter on export-csv:

# export with tab delimiter
Import-Csv inputFile.csv -Header Project,StartDate,EndDate,Activity,Description,Owner |
	Export-Csv outputFile.csv -NoType -Delimiter "`t"

If you want text output with aligned columns that’ll render the output feel useless for additional programming, you can use Format-Table:

(Import-Csv inputFile.csv -Header Project,StartDate,EndDate,Activity,Description,Owner |
    Format-Table -Auto | Out-String -Stream) -ne '' |
        Set-Content outputFile.txt
9

I would have to imagine there are hidden newline characters that your text editor is not seeing. If the value Joe is showing up under Project on row 3, then there is a newline character between , and Joe on the second line.

10

If we are dealing with newline characters that are not at the end of a line, then we will need a way to remove those without impacting the real end of a line. For example, if all end of lines have CRLF but you have sporadic LF without CR throughout, you can simply replace those single LF characters:

(Get-Content inputFile.csv -Raw) -split '\r\n' -replace '\n' -ne '' |
    ConvertFrom-Csv -Header Project,StartDate,EndDate,Activity,Description,Owner |
        Export-Csv outputfile.csv -NoType
11

personally I typically dump CSVs into a hashtable using import-csv. makes it a lot easier to work with normally, however as stated above headers are required for this as well. gives you a nice “no effort” way to call the items as well when its done

for reference, i use it with a list of software… so i specify the below as “Software” without the quotes

$CSV = “”

$CSVContent = Import-csv $csv | Group-object -AsHashTable -Property

so using my software reference above i could call something like $

$CSVContent[“Adobe Air”] .TaskSequenceVariable <— another header from the csv file and it would return the variable used in my task sequence to detect if it should install adobe air for that software load.