Interview Questions   Tutorials   Discussions   Programs   Videos   

SAS - How to convert .xls file into CSV format?




343
views
asked mar September 20, 2014 06:29 AM  

How to convert .xls file into CSV format?


           

1 Answers



 
answered By vishnoiprem   0  

The replies you have received so for don't seem to be answering the question. Your question as I understand it involves MS Office Automation more that SAS but you want a program that can be "called" with SAS to do the work. I have posted similar programs before but here it is again specifically for CSV.

/*input*/  filename workbook "C:\Documents and Settings\&sysuserid\My
Documents\Book1.xls";
/*output*/ filename txtout   "C:\Documents and Settings\&sysuserid\My
Documents";
data Sheets(keep=indx csvpath);
   /* gather info */
   length workbook txtout script filevar command $256;
   workbook = pathname('WORKBOOK');
   txtout   = pathname('TXTOUT');
   script   = catx('\',pathname('WORK'),'SHEETS2TXT.vbs');
   filevar  = script;

   /* write the script */
   file dummy1 filevar=filevar;
   put 'Const ' workbook=$quote256.;
   put 'Const ' txtout=$quote256.;
   put 'Const xlCSV = 6';

   put 'Set objExcel = CreateObject("Excel.Application")';
   put 'With objExcel';
   put +3 '.Visible = False';
   put +3 '.DisplayAlerts = False';
   put +3 'Set objWorkbook  = .Workbooks.Open(workbook)';
   put +3 'i = 0';
   put +3 'Set colSheets = .WorkSheets';
   put +3 'For Each objSheet In colSheets';
   put +6   'i = i + 1';
   put +6   'WScript.echo i & " " & txtout & "" & objsheet.name & ".csv"';
   put +6   'objSheet.SaveAs txtout & "" & objSheet.name & ".csv", xlCSV';
   put +6   'Next';
   put +3 '.Application.Quit';
   put +3 'End With';

   /* close the script file by opening another, not used */
   filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
   file dummy1 filevar=filevar;

   /* look at the script, not necessary but may be useful */
   infile dummy2 filevar=script end=eof;
   do while(not eof);
      input;
      putlog _infile_;
      end;

   /* call the script */
   command = catx(' ','cscript',quote(strip(script)),'//nologo');
   infile dummy3 pipe filevar=command end=eof truncover;
   do while(not eof);
      input indx csvPath $256.;
      output;
      putlog _infile_;
      end;
   stop;

run;

filename workbook clear; filename txtout clear; proc print; run;

LIBNAME urlib 'C:Sale reportsSVsales.xls';
This will avail the sheets of sales.xls as individual members of urlib library in SAS. 
Step 2:
Data _null_;
set urlib.sheet1;
file 'C:UsersSV file.ext ' dsd;
Put var1--varN;
Run;
by.
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert