How to get text file content using powershell , and save the file as excel with the same name?

Venkat 21 Reputation points
2022-02-25T13:56:46.577+00:00

I need to get the content of the txt file and convert it into excel and save the file with the same name

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,735 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,576 questions
0 comments No comments
{count} votes

Accepted answer
  1. Michael Taylor 55,376 Reputation points
    2022-02-25T16:14:29.34+00:00

    How do you convert an arbitrary text file to an Excel spreadsheet?

    Getting the content of a file is easy.

    $text = Get-Content "myfile.txt"
    

    But text isn't a spreadsheet so you'd then have to do some sort of script-specific logic to convert it into a spreadsheet that would make sense. Perhaps you could convert to CSV using PS if that made sense for your data.

    $csv = ConvertTo-Csv -InputObject $text
    

    Or export directly to a csv file.

    Export-Csv -InputObject $text -Path "myfile.csv"
    

    Excel understands CSV files so this file could be opened in Excel. If you want an actual Excel file then you'll need to convert it to OpenXML format with PS doesn't support out of the box. If Excel is installed on the machine then you can use the Excel COM API to create the file. Rather than posting all that code here, refer to this blog article that talks about converting a text file to an Excel file.


2 additional answers

Sort by: Most helpful
  1. Rich Matheisen 47,386 Reputation points
    2022-02-25T20:58:38.837+00:00

    You can use the Excel COM object model to create and manipulate Excel files (which requires having the Excel application installed on the machine), but I'd recommend avoiding that and using the PowerShell ImportExcel module instead.

    An alternative (as offered by others) is to create a CSV file and import the CSV file into Excel. But CSV files are "text-only" and cannot contain formatting, formulas, etc.

    0 comments No comments

  2. Venkat 21 Reputation points
    2022-02-26T12:34:34.147+00:00

    @Michael Taylor

    My requirement is I will have multiple text files in one folder or one file. I need to get the content of the file dynamically one after one. using powershell


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.