CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

[shailaja3]

Hi everyone,

I am a SAS programmer, I would like to convert  multiple XLSX AND XLS  file into single CSV file. I was wondering if there any method to do,  please help me to find the solution. I am using Sas windows environment 9.4 version.

Any suggestions would be very helpful.

[Reeza]

There is a Visual Basic script here that does something similar, it converts XML to XLSX. You can modify the file types via the extension and the number in the code to save the files as CSV instead.

https://gist.github.com/statgeek/c51f58a009f8d315a200f34912e494b1

[RichardDeVen · Accepted solution]

You can use the XLSX or EXCEL library engine to read the data in an Excel worksheet, and ODS CSV to stack the data read in an output text file.

Example:

Excel: Create three Excel files for example

Spoiler

ods noresults;

ods excel file='1.xlsx' options(sheet_name='Class');
proc print noobs data=sashelp.class; run;
ods excel close;

ods excel file='2.xlsx' options(sheet_name='Class');
proc print noobs data=sashelp.class; run;
ods excel close;

ods excel file='3.xlsx' options(sheet_name='Class');
proc print noobs data=sashelp.class; run;
ods excel close;

ods results;

Code (Stack Excel data in CSV file)

ods noresults;   /* prevent automatic opening when ODS destination closed */  

filename mycsv 'mydata_from_excel.csv';  /* destination file */

ods csv file=mycsv;                      /* open csv destination */
  libname workbook XLSX '1.xlsx';
  proc report data=workbook.Class; run;  /* write data from worksheet "class" in 1st xlsx to csv file with headers */
ods csv close;                           /* close csv file */

filename csv 'mydata_from_excel.csv' mod;   /* same filename (destination) with MOD option to force appending */

ods csv file=mycsv;                      /* open csv destination which is append only file */

libname workbook XLSX '2.xlsx';
proc report data=workbook.Class noheader; /* write data from worksheet "class" in 2nd xlsx to csv file with NO headers */
run;                                 

libname workbook XLSX '3.xlsx';
proc report data=workbook.Class noheader; /* write data from worksheet "class" in 3rd xlsx to csv file with NO headers */
run;

libname workbook;  /* clear libref to remove resource lock on excel file */
ods csv close;     /* close destination */

ods results;

Check results, output should be 

Spoiler

"Name","Sex","Age","Height","Weight"
"Alfred","M",14,69,112.5
"Alice","F",13,56.5,84
"Barbara","F",13,65.3,98
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
"James","M",12,57.3,83
"Jane","F",12,59.8,84.5
"Janet","F",15,62.5,112.5
"Jeffrey","M",13,62.5,84
"John","M",12,59,99.5
"Joyce","F",11,51.3,50.5
"Judy","F",14,64.3,90
"Louise","F",12,56.3,77
"Mary","F",15,66.5,112
"Philip","M",16,72,150
"Robert","M",12,64.8,128
"Ronald","M",15,67,133
"Thomas","M",11,57.5,85
"William","M",15,66.5,112

"Alfred","M",14,69,112.5
"Alice","F",13,56.5,84
"Barbara","F",13,65.3,98
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
"James","M",12,57.3,83
"Jane","F",12,59.8,84.5
"Janet","F",15,62.5,112.5
"Jeffrey","M",13,62.5,84
"John","M",12,59,99.5
"Joyce","F",11,51.3,50.5
"Judy","F",14,64.3,90
"Louise","F",12,56.3,77
"Mary","F",15,66.5,112
"Philip","M",16,72,150
"Robert","M",12,64.8,128
"Ronald","M",15,67,133
"Thomas","M",11,57.5,85
"William","M",15,66.5,112

"Alfred","M",14,69,112.5
"Alice","F",13,56.5,84
"Barbara","F",13,65.3,98
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
"James","M",12,57.3,83
"Jane","F",12,59.8,84.5
"Janet","F",15,62.5,112.5
"Jeffrey","M",13,62.5,84
"John","M",12,59,99.5
"Joyce","F",11,51.3,50.5
"Judy","F",14,64.3,90
"Louise","F",12,56.3,77
"Mary","F",15,66.5,112
"Philip","M",16,72,150
"Robert","M",12,64.8,128
"Ronald","M",15,67,133
"Thomas","M",11,57.5,85
"William","M",15,66.5,112