CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

shailaja3 — Wed, 16 Dec 2020 20:39:48 GMT

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.

Re: CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

Reeza — Wed, 16 Dec 2020 20:30:56 GMT

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

Re: CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

RichardDeVen — Wed, 16 Dec 2020 21:38:43 GMT

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

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

"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

topic Re: CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE. in SAS Programming

CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

Re: CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.

Re: CONCATENATING MULTIPLE XLSX , XLS FILES INTO ONE CSV FILE.