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

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

@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