Assistance Splitting Column Data into Multiple Columns from Import-CSV

Question

I import a CSV using import-csv command:

$P = Import-Csv "C:\MyCSV.csv"

I then run 'Get-Member':

$P | Get-Member

Output:

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
Message     NoteProperty string Message=ABC 1234 DEFGH 123:3212 IJKLM         NOPQRST 23\13\19 ABC1234 0978AJD

I then run 'Format-Table':

$P | Format-Table

Output:

Message
-------
ABC 1234 DEFGH 123:3222 IJKNM NOPQRHT 23\13\19 ABC1234 0978AJD...
BAC 3214 DEFAH 123:3422 IJFLM NOPQRAT 23\13\18 ABC1234 0978AJD...
CEC 1534 DEFIH 123:3312 IJALM NOPQRFT 23\13\17 ABC1234 0978AJD...
3BC 1144 DAFGH 123:3612 IJZLM NOPQRGT 23\13\16 ABC1234 0978AJD...

I want to split this output up further by delimiting by space. I do not care about properly naming each new column. I just want to be able to then select whatever column header certain text falls under and export that output to a new CSV.

Ideal output:

Column1 Column2 Column3 Column4  etc                                                                                                                                                                                       
------- ------- ------- -------                                                                                                                                                                                                      
ABC     1234    DEFGH   123:3222 etc

So I can then run a command such as:

select Column5,Column8

or a command like

select Column15,Column58

Can anyone assist me with this?

Can you provide a sample of what your input looks like? I suspect you can use Import-CSV more efficiently but I can't tell with the info provided. — kpogue
Are you confident that each row will have the same number of spaces (and thus, the same number of columns)? — TheMadTechnician
Yes, these are actually Windows Security event logs with a set format. — dy625

Lee_Dailey Lee_Dailey · Accepted Answer · 2018-12-19T21:52:35

This ought to do the job:

# fake reading in a CSV file as text
#    in real life, use Get-Content
$InStuff = @'
Message
ABC 1234 DEFGH 123:3222 IJKNM NOPQRHT 23\13\19 ABC1234 0978AJD
BAC 3214 DEFAH 123:3422 IJFLM NOPQRAT 23\13\18 ABC1234 0978AJD
CEC 1534 DEFIH 123:3312 IJALM NOPQRFT 23\13\17 ABC1234 0978AJD
3BC 1144 DAFGH 123:3612 IJZLM NOPQRGT 23\13\16 ABC1234 0978AJD
'@ -split [environment]::NewLine

$ColCount = $InStuff[1].Split(' ').Count

$Collection = $InStuff |
    Select-Object -Skip 1 |
    ConvertFrom-Csv -Delimiter ' ' -Header (1..$ColCount).ForEach({"Column_$_"})

$Collection |
    Select-Object -Property 'Column_3', 'Column_7'

Output:

Column_3 Column_7
-------- --------
DEFGH    23\13\19
DEFAH    23\13\18
DEFIH    23\13\17
DAFGH    23\13\16

What it does:

reads the file in as a text file, not a CSV file
gets a count on the # of columns
skips the 1st line
creates a CSV import
- sets the delimiter to <space>
- sets the header to the range of 1..$ColCount
filters for the desired columns

Assistance Splitting Column Data into Multiple Columns from Import-CSV

2 Answers