Convert txt to xlsx

Welcome Forums General PowerShell Q&A Convert txt to xlsx

This topic contains 7 replies, has 5 voices, and was last updated by

 
Participant
2 years, 5 months ago.

  • Author
    Posts
  • #25254

    Participant
    Points: 0
    Rank: Member

    hi developer,

    I want to convert a text file into an xlsx file. here i use following powershell script.

    Get-Content .\aa.txt | Foreach-Object {($_ -replace "\s+",",")}|Out-File -FilePath converted.csv -Encoding OEM 

    The script works as above but i am going to change to xlsx in Out-File.

    Get-Content .\aa.txt | Foreach-Object {($_ -replace "\s+",",")}|Out-File -FilePath converted.xlsx -Encoding OEM 

    it does not working

    how to convert txt to xlsx and worksheet name?
    Any Idea?

    Thanks

  • #25262

    Keymaster
    Points: 1,785
    Helping HandTeam Member
    Rank: Community Hero

    Out-File produces text files. It doesn't matter what filename extension you give it, it's always a text file. Excel files are not text files.

    Try Export-CSV instead of Out-File. That will produce a CSV file, which Excel can open.

  • #25263

    Participant
    Points: 428
    Helping Hand
    Rank: Contributor

    You have a text file, which looks like it's space delimited?

    computer1 rob laptop
    computer3 john desktop
    computer4 sam laptop
    

    You would want to define the header row by creating a PSObject and as Don says above, Export-CSV. There are multiple ways to do things, but without seeing the file all we can do is provide an example:

    #Grab the text file
    $textFile = Get-Content 'C:\Users\rsimmers\Desktop\test.txt'
    
    #Loop through each line and assign everything produced in the
    $result = foreach ($line in $textFile) {
        #Split the line into an array using space as a delimiter
        $array = $line -Split " "
        #Create a new object to return to $result and define the what each "column" would be assigned to
        New-Object -TypeName PSObject -Property @{ComputerName=$array[0];Owner=$array[1];Type=$array[2]}
    }
    
    #Export the object to a CSV
    $result | Export-CSV C:\Test\MyCSV.csv -NoTypeInformation
    
    
  • #25297

    Participant
    Points: 0
    Rank: Member

    I want convert Txt file to XLSX not CSV

  • #25298

    Keymaster
    Points: 1,785
    Helping HandTeam Member
    Rank: Community Hero

    Powershell doesn't do that.

  • #25305

    Keymaster
    Points: 1,785
    Helping HandTeam Member
    Rank: Community Hero

    Have a look at http://www.dougfinke.com/blog/index.php/2015/04/20/painlessly-get-data-from-powershell-to-excel/. If you can import your text file and get the data into object form, Doug's tool will help you get it into an actual Excel file.

  • #50306

    Participant
    Points: 0
    Rank: Member

    The above link is broken, can you share an other one.

  • #50313

    Participant
    Points: 0
    Rank: Member

The topic ‘Convert txt to xlsx’ is closed to new replies.