converting excel file to xpt files

[Ravindra_]

Can i know how to convert excel files to xpt files. We have got some internal requirement. Thank you. There are few excel files with multiple tabs, and there is a limitation of SAS in our organisation that we will not be able to import excel files, we have only privilege to import csv files. can i know a best solution for this. 

[Sajid01]

Hello
You have few excel files and you cannot import them using SAS.
Then one approach would be save the excel sheets individually as csv and follow your solution.
You have other option of using tools like python or R and convert your files to csv and then follow your solution.

[Reeza]

That's probably a licensing issue but I thought XLSX libname support had moved to Base...you can get VBS scripts to convert workbooks and all sheets to CSV assuming they're well formed. If they're not, you may need Excel macros or some other method to convert those to CSV. Lots of solutions online for this approach..assuming you're on Windows and can run VBS.

https://itecnote.com/tecnote/excel-convert-xls-xlsx-files-all-sheets-to-csv-using-vbscript-delimited...

[Ksharp]

Or using this free software to convert " GroupDocs.Total Free Apps for Windows "

[Ravindra_]

As the final xpt files need to be sent to client and they are requesting us to use only SAS for this, is there any solution for this apart from vba script or groupdocs.

[Sajid01]

Hello @Ravindra_ 
You have put forth the following two constraints in your posts
(1)There is a limitation of SAS in our organisation that we will not be able to import excel files
(2)As the final xpt files need to be sent to client and they are requesting us to use only SAS for this.

The plausible approach would be to export the excel to csv (preferably from excel itself) and then use SAS to import the data (csv file) and then create a transport file(xpt). This approach will satisfy both of your constraints.
In my view the suggestion by @Reeza  needs to be considered. That simplifies the entire process. You may need to convince the decision makers in your company to implement this  suggestion.

[Ksharp]

No. As Reeza said, change your excel file into csv file by my suggested software or Reeza's VBA .
After that import these csv file via PROC IMPORT or Data Step, and you can change sas dataset into XPT fille by
xport engine of libname .

[Ravindra_]

VBA is which i am not aware of, i dont think we are allowed to use a third party software to convert excel to csv. Is there a thread available in the community to see any macro been suggested for this use to covert 165 excel files to csv.

[Ksharp]

Oh. you could issue OS command to execute Reeza's VBA in SAS .

Like:

x 'your os command here to execute vba' ;

filename x pipe 'your os command here to execute vba';

data _null_;

call system('your os command here to execute vba');

run;

[Ravindra_]

I will try this and let you know

[Reeza]

Well, you can have SAS generate and run the VBS if really necessary.