CODEWITHSUNDEEP
powershell
dy625
dy625

Reputation: 85

Assistance Splitting Column Data into Multiple Columns from Import-CSV

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?

Upvotes: 2

Views: 1561

Answers (2)

dy625
dy625

Reputation: 85

Lee, I am currently unable to edit my own post because my reputation is too low -_-. As such, I will respond to the post with the information you're requesting:

For your further insight, here is my current code that's not working:

    $InStuff = Get-Content -Path 'MyCSV.csv'

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

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

$Collection

Output. As you can see, all columns except Column_1 are empty:

Column_1   : <134>Dec 13 13:50:23 10.137.119.42 MSWinEventLog 1 Security 123456789 
Thu Dec 13 13:50:23 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy625 N/A 
Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on 
an object.    Subject :   Security ID:  S-123456  Account Name:  dy625 Account 
Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object 
Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   
Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write 
Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX- 
XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   
Parameter 1:  -   Parameter 2:   123456
Column_2   : 
Column_3   : 
Column_4   : 
Column_5   : 
...
Column_1   : <134>Dec 13 13:50:18 10.137.119.42 MSWinEventLog 1 Security 123456789 
Thu Dec 13 13:50:18 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy626 N/A 
Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on 
an object.    Subject :   Security ID:  S-123456  Account Name:  dy626 Account 
Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object 
Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   
Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write 
Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX- 
XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   
Parameter 1:  -   Parameter 2:   123456
Column_2   : 
Column_3   : 
Column_4   : 
Column_5   : 
...
Column_1   : <134>Dec 13 13:50:14 10.137.118.22 MSWinEventLog 1 Security 123456789 
Thu Dec 13 13:50:14 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy627 N/A 
Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on 
an object.    Subject :   Security ID:  S-123456  Account Name:  dy627 Account 
Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object 
Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   
Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write 
Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX- 
XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   
Parameter 1:  -   Parameter 2:   123456
Column_2   : 
Column_3   : 
Column_4   : 
Column_5   :

As I stated before, I think the issue is that I don't know how to re-implement the '-split [environment]::NewLine' command with the updated syntax. As you can, see it's missing. I think this is the cause of the issue.

When I input the raw text as you suggest, with the entire first 3 lines, your syntax works correctly, as expected.

$InStuff = @'
Message
<134>Dec 13 13:50:23 10.137.119.42 MSWinEventLog 1 Security 123456789 Thu Dec 13 13:50:23 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy625 N/A Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on an object.    Subject :   Security ID:  S-123456  Account Name:  dy625 Account Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   Parameter 1:  -   Parameter 2:   123456
<134>Dec 13 13:50:18 10.137.119.42 MSWinEventLog 1 Security 123456789 Thu Dec 13 13:50:18 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy626 N/A Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on an object.    Subject :   Security ID:  S-123456  Account Name:  dy626 Account Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   Parameter 1:  -   Parameter 2:   123456
<134>Dec 13 13:50:14 10.137.118.22 MSWinEventLog 1 Security 123456789 Thu Dec 13 13:50:14 2018 4662 Microsoft-Windows-Security-Auditing MyCompany\dy627 N/A Success Audit mydc1.dy625.com Directory Service Access  An operation was performed on an object.    Subject :   Security ID:  S-123456  Account Name:  dy627 Account Domain:  MyCompany   Logon ID:  XXXXXXXX   Object:   Object Server:  DS   Object Type:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Object Name:  %{XXXXXXXX-XXXXXXXX-XXXXXXXX}   Handle ID:  0x0    Operation:   Operation Type:  Object Access   Accesses:  Write Property  Access Mask:  0x20   Properties:  Write Property {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX} {XXXX-XXXX-XXXXX}  {XXXX-XXXX-XXXXX}   Additional Information:   Parameter 1:  -   Parameter 2:   123456
'@ -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:

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

Column_3 Column_7
-------- --------
13:50:23 Security
13:50:18 Security
13:50:14 Security

Again, I think the issue is just that I don't know how to implement the '-split [environment]::NewLine'command.

$InStuff = Get-Content -Path 'MyCSV.csv' -split [environment]::NewLine

Error:

Get-Content : A parameter cannot be found that matches parameter name 'split'.

Anyway, I hope this sheds some clarity on the issue.

Upvotes: 0

Lee_Dailey
Lee_Dailey

Reputation: 7479

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

Upvotes: 2

Related Questions