%Macro utl_prlcsv2xls ( Utl_Title=Create Excel Spreadsheet from Comma Delim File, Utl_Obj=utl_prlcsv2xls, /*------------------------------------------------------------*\ | | | MS-Excel does not have to be installed | | | | Code should work on Unix/Windows/NT/2000/XP | | will not work on Win95/98/ME? | | | | PERL and PERL utilities have to be installed ( all free ) | | | | Converts a comma delim file to Excel Spreadsheet | | | | Uses named pipes to communicate between PERL and SAS | | | | This code creates an Excel file using PERL and Base SAS | | It does not use DDE or ODBC and the Excel spreadsheet | | does not have to exist prior to execution of this macro. | | In other words PERL creates the Excel Spreadsheet. | | | | Be patient I have initial waits of about 15 seconds | | before sheet is built | | | | Perl script is at the end of this macro | | | \*------------------------------------------------------------*/ /*----------------------*\ | Inputs | \*----------------------*/ Utl_InPerlExe =%str(I:\perl\bin\perl.exe), /* Perl interpreter */ Utl_InPerlScript=%str(I:\Prl\csv2xls.pl), /* Play with this - learn Perl */ Utl_InCsv =%str(\\.\pipe\slo), /* Named pipe common ground Perl/SAS */ /*----------------------*\ | Process | \*----------------------*/ /*------------------------------------------------------------*\ | | | Tested in Win2000 Professional | | | | This code requires you install | | | | 1. PERL | | 2. Several Packages,you have to be connected to the web | | | | After you install Perl | | Connect to the internet then open a command window | | Type PPM3 or PPM you should see PPM> | | | | PPM> Install Parse-Text | | PPM> Install Text::CSV_XV | | PPM> Install Parse-RecDescent | | PPM> Install Spreadsheet-WriteExcel | | PPM> Quit | | | | Think of the above as adding objects to your | | autoexec lib. ( PERL folder Auto? ) | | | \*------------------------------------------------------------*/ /*----------------------*\ | Outputs | \*----------------------*/ Utl_OtXls=%str(I:\Prl\slo02.xls), /* Binary 97/2000 Excel */ Utl_FlowPoin=04840901490 ) / des="Create Excel Spreadsheet from Comma Delim File"; options xwait noxsync noxmin;run; run; filename vu namepipe "&Utl_InCsv" server retry=5;run; x "&Utl_InPerlExe &Utl_InPerlScript &Utl_InCsv &Utl_OtXls";run; Data _null_; file vu; put '1,2,3,4,5,"Hello"'; put '1,2,3,4,5,"World"'; run; %Mend utl_prlcsv2xls; %utl_prlcsv2xls; Log MLOGIC(FYL): Beginning execution. MLOGIC(FYL): This macro was compiled from the autocall file g:\utl\fyl.sas MLOGIC(FYL): Parameter UFILE has value utl_prlcsv2xls SYMBOLGEN: Macro variable UFILE resolves to utl_prlcsv2xls SYMBOLGEN: Macro variable UFILE resolves to utl_prlcsv2xls SYMBOLGEN: Macro variable UFILE resolves to utl_prlcsv2xls MLOGIC(FYL): Ending execution. 461 %Macro utl_prlcsv2xls 462 ( 463 Utl_Title=Create Excel Spreadsheet from Comma Delim File, 464 Utl_Obj=utl_prlcsv2xls, 465 466 /*------------------------------------------------------------*\ 467 | | 468 | MS-Excel does not have to be installed | 469 | | 470 | Code should work on Unix/Windows/NT/2000/XP | 471 | will not work on Win95/98/ME? | 472 | | 473 | PERL and PERL utilities have to be installed ( all free ) | 474 | | 475 | Converts a comma delim file to Excel Spreadsheet | 476 | | 477 | Uses named pipes to communicate between PERL and SAS | 478 | | 479 | This code creates an Excel file using PERL and Base SAS | 480 | It does not use DDE or ODBC and the Excel spreadsheet | 481 | does not have to exist prior to execution of this macro. | 482 | In other words PERL creates the Excel Spreadsheet. | 483 | | 484 | Be patient I have initial waits of about 15 seconds | 485 | before sheet is built | 486 | | 487 \*------------------------------------------------------------*/ 488 489 490 /*----------------------*\ 491 | Inputs | 492 \*----------------------*/ 493 494 Utl_InPerlExe =%str(I:\perl\bin\perl.exe), /* Perl interpreter */ 495 Utl_InPerlScript=%str(I:\Prl\csv2xls.pl), /* Play with this - learn Perl */ 496 Utl_InCsv =%str(\\.\pipe\slo), /* Named pipe common ground Perl/SAS 496 ! */ 497 498 499 /*----------------------*\ 500 | Process | 501 \*----------------------*/ 502 503 /*------------------------------------------------------------*\ 504 | | 505 | Tested in Win2000 Professional | 506 | | 507 | This code requires you install | 508 | | 509 | 1. PERL | 510 | 2. Several Packages,you have to be connected to the web | 511 | | 512 | After you install Perl | 513 | Connect to the internet then open a command window | 514 | Type PPM3 or PPM you should see PPM> | 515 | | 516 | PPM> Install Parse-Text | 517 | PPM> Install Text::CSV_XV | 518 | PPM> Install Parse-RecDescent | 519 | PPM> Install Spreadsheet-WriteExcel | 520 | PPM> Quit | 521 | | 522 | Think of the above as adding objects to your | 523 | autoexec lib. ( PERL folder Auto? ) | 524 | | 525 \*------------------------------------------------------------*/ 526 527 /*----------------------*\ 528 | Outputs | 529 \*----------------------*/ 530 531 Utl_OtXls=%str(I:\Prl\slo02.xls), /* Binary 97/2000 Excel */ 532 533 Utl_FlowPoin=04840901490 534 535 ) / des="Create Excel Spreadsheet from Comma Delim File"; 536 537 538 options xwait noxsync noxmin;run; 539 run; 540 541 542 filename vu namepipe "&Utl_InCsv" server retry=5;run; 543 544 545 x "&Utl_InPerlExe &Utl_InPerlScript &Utl_InCsv &Utl_OtXls";run; 546 547 Data _null_; 548 549 file vu; 550 551 put '1,2,3,4,5,"Hello"'; 552 put '1,2,3,4,5,"World"'; 553 554 run; 555 556 557 %Mend utl_prlcsv2xls; 558 559 560 %utl_prlcsv2xls; MLOGIC(UTL_PRLCSV2XLS): Beginning execution. MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_TITLE has value Create Excel Spreadsheet from Comma Delim File MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_OBJ has value utl_prlcsv2xls MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_INPERLEXE has value I:\perl\bin\perl.exe MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_INPERLSCRIPT has value I:\Prl\csv2xls.pl MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_INCSV has value \\.\pipe\slo MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_OTXLS has value I:\Prl\slo02.xls MLOGIC(UTL_PRLCSV2XLS): Parameter UTL_FLOWPOIN has value 04840901490 MPRINT(UTL_PRLCSV2XLS): options xwait noxsync noxmin; MPRINT(UTL_PRLCSV2XLS): run; MPRINT(UTL_PRLCSV2XLS): run; SYMBOLGEN: Macro variable UTL_INCSV resolves to \\.\pipe\slo SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(UTL_PRLCSV2XLS): filename vu namepipe "\\.\pipe\slo" server retry=5; MPRINT(UTL_PRLCSV2XLS): run; SYMBOLGEN: Macro variable UTL_INPERLEXE resolves to I:\perl\bin\perl.exe SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable UTL_INPERLSCRIPT resolves to I:\Prl\csv2xls.pl SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable UTL_INCSV resolves to \\.\pipe\slo SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable UTL_OTXLS resolves to I:\Prl\slo02.xls SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(UTL_PRLCSV2XLS): x "I:\perl\bin\perl.exe I:\Prl\csv2xls.pl \\.\pipe\slo I:\Prl\slo02.xls"; MPRINT(UTL_PRLCSV2XLS): run; MPRINT(UTL_PRLCSV2XLS): Data _null_; MPRINT(UTL_PRLCSV2XLS): file vu; MPRINT(UTL_PRLCSV2XLS): put '1,2,3,4,5,"Hello"'; MPRINT(UTL_PRLCSV2XLS): put '1,2,3,4,5,"World"'; MPRINT(UTL_PRLCSV2XLS): run; NOTE: The file VU is: Named Pipe Access Device, PROCESS=\\.\pipe\slo,RECFM=V,LRECL=256 NOTE: 2 records were written to the file VU. The minimum record length was 17. The maximum record length was 17. NOTE: DATA statement used: real time 5.04 seconds cpu time 0.01 seconds MLOGIC(UTL_PRLCSV2XLS): Ending execution. #!/usr/bin/perl -w ############################################################################### # # Example of how to use the WriteExcel module # # Program to convert a CSV comma-separated value file into an Excel file. # This is more or less an non-op since Excel can read CSV files. # The program uses Text::CSV_XS to parse the CSV. # # Usage: csv2xls.pl file.csv newfile.xls # # reverse('©'), March 2001, John McNamara, jmcnamara@cpan.org # use strict; use Spreadsheet::WriteExcel; use Text::CSV_XS; use Win32; use Win32::Pipe; sleep(5); # Check for valid number of arguments if (($#ARGV < 1) || ($#ARGV > 2)) { die("Usage: csv2xls csvfile.txt newfile.xls\n"); }; # Open the Comma Seperated Variable file open (CSVFILE, "$ARGV[0]" ) or die "$ARGV[0]: $!"; # Create a new Excel workbook my $workbook = Spreadsheet::WriteExcel->new($ARGV[1]); my $worksheet = $workbook->addworksheet(); # Create a new CSV parsing ojbect my $csv = Text::CSV_XS->new; # Row and column are zero indexed my $row = 0; while () { if ($csv->parse($_)) { my @Fld = $csv->fields; my $col = 0; foreach my $token (@Fld) { $worksheet->write($row, $col, $token); $col++; } $row++; } else { my $err = $csv->error_input; print "Text::CSV_XS parse() failed on argument: ", $err, "\n"; } } */