Author Posts

May 15, 2015 at 2:19 am

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 [b]converted.csv[/b] -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 [b]converted.xlsx[/b] -Encoding OEM 

it does not working

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

Thanks

May 15, 2015 at 4:31 am

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.

May 15, 2015 at 4:51 am

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

May 15, 2015 at 11:59 pm

I want convert Txt file to [u][i]XLSX[/i][/u] not [b]CSV[/b]

May 16, 2015 at 4:43 am

Powershell doesn't do that.

May 16, 2015 at 6:20 am

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.

August 13, 2016 at 6:22 pm

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