Write Text file data to csv-CodePudding

Below is my data in text file

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     Z                       DVD-ROM         0 B  No Media           
  Volume 1         System Rese  NTFS   Partition    500 MB  Healthy    System  
  Volume 2     C   SYS          NTFS   Partition     99 GB  Healthy    Boot    
  Volume 3     S   SWAP         NTFS   Partition   6141 MB  Healthy    Pagefile
  Volume 4     D   DATA         NTFS   Partition    199 GB  Healthy            
  Volume 5     E   bit locker   NTFS   Partition      9 GB  Healthy            
  Volume 6     F   test         NTFS   Partition     10 GB  Healthy

I have to write it in csv. tried below code but not able to handle to Blank values. e.g. for first row Label,Fs,Info values are blank

$data = get-content -Path "C:\d.txt"

$result = switch -Regex ($data) {
    '^\s*Volume \d'  {
        $disk,$status,$Label,$Fs,$Type,$size,$Stat,$Info = $_.Trim() -split '\s{2,}'

        [PsCustomObject]@{        
           'Server'   = $server
           'Volume ###' = $disk           
           'Ltr'   = $status
           'Label' = $Label
           'Fs'   = $Fs
           'Type' = $Type
           'Size'     = $size
           'Status' = $Stat
           'Info' = $Info
           
        }
    }
}

# output on console screen
$result | Format-Table -AutoSize

# output to CSV file
$result | Export-Csv -Path "C:\$server.csv" -NoTypeInformation -Append

Output is coming like

Server          Volume ### Ltr         Label      Fs        Type      Size    Status  Info    
------          ---------- ---         -----      --        ----      ----    ------  ----    
AxxxxxxxxxxxxxP Volume 0   Z           DVD-ROM    0 B       No Media                          
AxxxxxxxxxxxxxP Volume 1   System Rese NTFS       Partition 500 MB    Healthy System          
AxxxxxxxxxxxxxP Volume 2   C           SYS        NTFS      Partition 99 GB   Healthy Boot    
AxxxxxxxxxxxxxP Volume 3   S           SWAP       NTFS      Partition 6141 MB Healthy Pagefile
AxxxxxxxxxxxxxP Volume 4   D           DATA       NTFS      Partition 199 GB  Healthy         
AxxxxxxxxxxxxxP Volume 5   E           bit locker NTFS      Partition 9 GB    Healthy         
AxxxxxxxxxxxxxP Volume 6   F           test       NTFS      Partition 10 GB   Healthy

Please let me know how to handle the spaces or any other way I can write it to csv

After executing @Mathias code

"Count","IsReadOnly","Keys","Values","IsFixedSize","SyncRoot","IsSynchronized"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"
"9","False","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","System.Collections.Specialized.OrderedDictionary OrderedDictionaryKeyValueCollection","False","System.Object","False"

this is what I am trying to do


$server = $env:COMPUTERNAME

# Start by reading in the table
# Replace this statement with `
$lines = Get-Content "c:\d.txt"
$lines = $lines -split '\r?\n'

# Assign the first two lines to separate variables
$header, $guardRails, $lines = $lines

# Split the header into individual column names
$columnNames = @(
  $header.Trim() -split '\s{2,}' |ForEach-Object Trim
)

# Use regex to match all the individual `---` sequences, grab their offset   length
$columnOffsets = @(
  [regex]::Matches($guardRails, '(?<!-)- (?!-)') |Select Index,Length
)

# Parse the data based on the offsets located above
foreach($line in $lines){
  # Prepare a dictionary to hold the column values, add the Server property straight away
  $properties = [ordered]@{
    Server = $server
  }

  # Now we just need to iterate over the lists of column headers and extract the corresponding substring from the line
  for($i = 0; $i -lt $columnNames.Length; $i  ){
    # Grab the column name and offset
    $propertyName = $columnNames[$i]
    $offset = $columnOffsets[$i]

    # Grab the substring corresponding to the column
    $propertyValue = $line.Substring($offset.Index, $offset.Length).Trim()

    # Add the information to our property dictionary
    $properties[$propertyName] = $propertyValue
  }

  # Output a new object based on the properties we grabbed from the column data
  [pscustomobject]$properties


# output on console screen
$properties | Format-Table -AutoSize

# output to CSV file
$properties | Export-Csv -Path "C:\$server.csv" -NoTypeInformation -Append

}

CodePudding user response：

Use the line below the header ( ---------- --- -----------...) to detect at which offsets to parse data for a specific column name:

# Start by reading in the table
# Replace this statement with `$lines = Get-Content .\path\to\file.txt` in your script
$lines = @'
  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     Z                       DVD-ROM         0 B  No Media           
  Volume 1         System Rese  NTFS   Partition    500 MB  Healthy    System  
  Volume 2     C   SYS          NTFS   Partition     99 GB  Healthy    Boot    
  Volume 3     S   SWAP         NTFS   Partition   6141 MB  Healthy    Pagefile
  Volume 4     D   DATA         NTFS   Partition    199 GB  Healthy            
  Volume 5     E   bit locker   NTFS   Partition      9 GB  Healthy            
  Volume 6     F   test         NTFS   Partition     10 GB  Healthy            
'@ -split '\r?\n'

# Assign the first two lines to separate variables
$header, $guardRails, $lines = $lines

# Split the header into individual column names
$columnNames = @(
  $header.Trim() -split '\s{2,}' |ForEach-Object Trim
)

# Use regex to match all the individual `---` sequences, grab their offset   length
$columnOffsets = @(
  [regex]::Matches($guardRails, '(?<!-)- (?!-)') |Select Index,Length
)

# Parse the data based on the offsets located above
$volumeInfo = foreach($line in $lines){
  # Prepare a dictionary to hold the column values, add the Server property straight away
  $properties = [ordered]@{
    Server = 'Server123'
  }

  # Now we just need to iterate over the lists of column headers and extract the corresponding substring from the line
  for($i = 0; $i -lt $columnNames.Length; $i  ){
    # Grab the column name and offset
    $propertyName = $columnNames[$i]
    $offset = $columnOffsets[$i]

    # Grab the substring corresponding to the column
    $propertyValue = $line.Substring($offset.Index, $offset.Length).Trim()

    # Add the information to our property dictionary
    $properties[$propertyName] = $propertyValue
  }

  # Output a new object based on the properties we grabbed from the column data
  [pscustomobject]$properties
}

$volumeInfo |Export-Csv path\to\output.csv -NoTypeInformation