xls to xlsx rename filename extension

[chrissowden]

Hey I have a question about how I can batch a folder with several xls files into xlsx. I am using SAS grid 9.4.

[Tom]

Why?  Where they accidentally named with the wrong extension?

Note that changing the extension on a file's name will not change the content or format of the file.

[chrissowden]

Due to being on the grid our new setup.sas can only read in xlsx files and not xls.

[Tom]

The same code that can read XLSX files should be able to read XLS files with only a small modification.

Can you show an example where it doesn't work?

Perhaps you just need to update your version of SAS to something that was released in the last 18 months?

If your SAS cannot read the XLS files then you cannot use SAS to convert them to XLSX.  You will need to use Excel or some other tool.

[Kurt_Bremser]

You need to CONVERT those files. RENAMING them does NOTHING to help you.

.xls and .xlsx files have a completely different structure. .xls need a specific, Microsoft-supplied, Windows-only module to read them, while .xlsx is (for Microsoft standards) well documented and has therefore enabled SAS to come up with platform-independent code for reading them.

Open the files in Excel, save them as xlsx, and then put them onto the SAS server.

But you are very well advised to immediately convert to csv (a texual file format) and read that with a custom data step, so you have control over the structure (Excel file always involve guessing about structure on the part of SAS). Working with Excel-derived data directly always turns out to be a major PITA.

[SuryaKiran]

Do you have SAS 9.4 x64 and does not communicate with Office x32. Then possible solutions are:

1) Save your files in other format than xls or xlsx. I suggest CSV and you can use VBA code to convert the files to CSV.

2)  Install SAS PC Server Files on your computer.

[RW9]

You mistake the file format.  A file's extension tells the operating system what program to use to open a file.  Changing this will not change the format.

XLS is a proprietary binary Excel format from years ago.

XLSX is a zipped file contain some folders and xml files.

If you change the file extension from one to the other this will not change the internal file, Excel may be ok loading it (as it can read both quite happily), but for your purposes it will not work.

If you are using a non-windows system or your bit does not match, then you will have a lot of issues trying to load the data.  

A simple method is to write a small Excel VBA script which opens each XLS file and saves them to a portable far more usable format like CSV or XML.  You can then write good robust code to read in these text files on any system,  at anytime in the future regardless of OS or software.