Запись данных текстового файла в csv

ProgramBox

Запись данных текстового файла в csv

Post author:admin
Запись опубликована:9 января, 2022
Post category:Вопросы по программированию

#powershell #csv #fixed-width

Вопрос:

Ниже приведены мои данные в текстовом файле

   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

Я должен записать его в csv. попробовал код ниже,но не смог обработать пустые значения. например,для метки первой строки, Fs, значения информации пусты

 $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

Выход приходит, как

 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

Пожалуйста, дайте мне знать, как обрабатывать пробелы или любым другим способом, которым я могу записать их в csv

После выполнения кода @Mathias

 "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"

это то, что я пытаюсь сделать

 
$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

}

Ответ №1:

Используйте строку под заголовком ( ---------- --- -----------... ), чтобы определить, при каких смещениях следует анализировать данные для определенного имени столбца:

 # Start by reading in the table
# Replace this statement with `$lines = Get-Content .pathtofile.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 pathtooutput.csv -NoTypeInformation

1. Спасибо Матиасу за твой ответ. Я попытался поместить $properties его в csv, но не получил значения. обновил свой ответ с выводом. не могли бы вы, пожалуйста, помочь здесь

2. @EmptyCoder Не экспортирует $properties — создавайте объекты из $properties , а затем экспортируйте эти объекты в CSV

3. Я этого не понимаю. можете ли вы, пожалуйста, дать мне образец, пожалуйста

4. @EmptyCoder Я обновил код в своем ответе

5. Это результат выражения [pscustomobject]$properties , которое вы хотите экспортировать

Метки: Запись данных текстового файла в csv