Using powershell to create excel based on information from a text file

Question

Not sure if this is possible using Powershell to create excel file based on info on a text file.

for example the text file has name, phone, location for each user (in separate line)

Name: John Smith
phone: 555 555 5555
Location: CA
Name: Marry Jones
phone: 555 777 7777
Location: VA
repeat

I want to create a excel file to have three columns (Name,phone,Location) and populate data base the info from the text file.

Not sure this is even possible with powershell, my other option is to import the test file to a database then export it with correct format.

Thanks

What do you mean by excel file? `.xlsx`? Because if you want something you can open with excel then you can use `Export-Csv`. You just need to do some research — Santiago Squarzon, May 16 '23 at 22:18
A similar problem is posed and answered [here](https://stackoverflow.com/questions/49276451/export-a-hashtable-valued-property-to-a-csv-file) — Walter Mitty, May 17 '23 at 05:24

score 0 · Answer 1 · answered May 17 '23 at 10:26

Try following code. You can use export-csv on the object $table to get the csv file

$filename = 'c:\temp\test.txt'
$reader = [System.IO.StreamReader]::new($filename)
$table = [System.Collections.ArrayList]::new()
while(($line = $reader.ReadLine()) -ne $null)
{
   $line = $line.Trim()
   if(($line.Length -gt 0) -and $line.Contains(':'))
   {
      $splitLine = $line.split(':')
      if($splitLine[0].Trim() -eq 'Name')
      {
         $newRow = New-Object -TypeName psobject
         $table.Add($newRow) | Out-Null
      }
      $newRow | Add-Member -NotePropertyName $splitLine[0].Trim() -NotePropertyValue $splitLine[1].Trim()
    }
}
$table

Using powershell to create excel based on information from a text file

1 Answers1