Using PowerShell clear-content issue

I have a script that I want to run every night. It will create an Excel file (.xlsx) but I want to clear the contents before writing new content to it. I don’t have a problem using clear-content, but when I try to open the file afterwards, It says I cannot open due to the file format or file extension not being valid. Does anyone know what I can do or what is happening?

Hi, welcome to the forum :wave:

An Excel file is a container that stores not just the contents of the document but lots of data about how it’s structured. Change the extension to .ZIP and you’ll see it’s like browsing a folder or or archive file.

When you run Clear-Content you’re not just deleting the information on the spreadsheet, you’re actually deleting all the content that makes it an Excel file.

Welcome to the forum. :wave:t4:

Clear-Content will not work properly for binary file formats like *.XLSX or *.DOCX. But if you create a new file in some way it should be empty be default anyway.
To make your life easier you could try to work with the great module from Doug Finke

1 Like

Thank you both for the welcoming and also for your answers! I did not know that the command would clear the format of the Excel file. That won’t be very useful for me. I am actually not creating a new file either because I am using Power Automate to grab information from the Excel file. If I delete the file, Power Automate doesn’t like that and won’t fill in the table information for the specified file. I did read though, by default, out-file will overwrite the existing file. So I am playing around with that idea. Thank you both!

Out-File outputs strings or string representations of objects in plain text to a text file.

If you want to use a pure PowerShell approach to create or manipulate *.XLSX files you should take a look the module I linked in my last answer. You don’t even need an installed Excel for it. You could run it on a server from a scheduled task for example.

1 Like

Thank you. I did look up export-excel and then ran a get-help on it. There are a lot of things to do with that I think will help me.

The help for the module has a lot of room for improvement unfortunately. But if you need some more help you could watch this:

That guy has a lot of good stuff! I’ve been watching. Thanks for the resources!