All strings in Apex and on the Salesforce platform are encoded in UTF-8, full stop. All Salesforce APIs accept input in UTF-8.
If you upload a text file that is not UTF-8 into a File object and attempt to process that file's content in Apex, you may encounter multiple different negative outcomes, or no change at all, depending on exactly what your code does. Text that cannot be interpreted as UTF-8 will result in a
StringException. Text that can be interpreted wrongly as UTF-8 may simply be mangled.
Every other application you might use to open a text file has its own semantics around how encodings are handled. Excel is notorious for making things difficult when working with CSV files that contain non-ASCII characters. I generally recommend the free and open source LibreOffice, which offers an encoding selector window every time you open a CSV so that you can ensure its content is interpreted and saved correctly. That said, it sounds like Excel is probably working for you here, unless the issues with unknown characters are manifesting after you've opened and resaved the file in Excel.
In Visual Studio Code, an encoding selector is available in the status bar at the base of the editor UI. Make sure it's set to UTF-8 when working with files from Salesforce.
For files destined to go into Salesforce, you may need to convert the encoding to UTF-8 from whatever is used by the software that generated the file, either in that source software or using some other tool to perform the conversion. The details will be dependent on what tools you are using.
In summary: use UTF-8 everywhere, and use only tools that fully support UTF-8. And if at all possible, don't parse CSVs in Apex.