Convert Excel file created with tagsets.excelxp to xls/xlsx
in [SAS]
|
From: Alex on 27 Oct 2009 05:14 Hi all, I have xml files created with ODS tagsets.excelxp and would like to convert them from plain xml to xls and/or xlsx. I can open them in Excel and Save as..., but need to do it automatically from a SAS program. The sheets in the files are not just one rectangular data set each, but two or more data sets with different columns. Also the sheets contain a lot of formatting. So just importing the xml and exporting to xls/xlsx wouldn't work, I guess. Any ideas? Thanks, Alex
From: Savian on 28 Oct 2009 01:39 On Oct 27, 3:14 am, Alex <alexander.k...(a)iea-dpc.de> wrote: > Hi all, > I have xml files created with ODS tagsets.excelxp and would like to > convert them from plain xml to xls and/or xlsx. I can open them in > Excel and Save as..., but need to do it automatically from a SAS > program. The sheets in the files are not just one rectangular data set > each, but two or more data sets with different columns. Also the > sheets contain a lot of formatting. So just importing the xml and > exporting to xls/xlsx wouldn't work, I guess. > Any ideas? > Thanks, > Alex The first thought is to simply use SaviCellsPro and have it do it for you: http://www.sascommunity.org/wiki/SaviCellsPro The original SaviCells may be a better fit but you can determine that: http://www.sascommunity.org/wiki/SaviCells The second is that you write something similar to SCP and simply open and save at a rapid clip. If you go this route, download Visual Studio Express, create a console app, add in the Office interops, then open and save the files using Excel. Call everything via a SAS X command. Alan http://www.savian.net
From: Jack Hamilton on 28 Oct 2009 21:19 Why not do it in Excel? It's simple to write a VBA routine that converts all the XML files in a directory into XLS format. -- Jack Hamilton jfh(a)alumni.stanford.org Caelum non animum mutant qui trans mare currunt. On Oct 27, 2009, at 2:14 am, Alex wrote: > Hi all, > I have xml files created with ODS tagsets.excelxp and would like to > convert them from plain xml to xls and/or xlsx. I can open them in > Excel and Save as..., but need to do it automatically from a SAS > program. The sheets in the files are not just one rectangular data set > each, but two or more data sets with different columns. Also the > sheets contain a lot of formatting. So just importing the xml and > exporting to xls/xlsx wouldn't work, I guess. > Any ideas? > Thanks, > Alex
From: Alex on 30 Oct 2009 05:02 On Oct 29, 2:19 am, j...(a)STANFORDALUMNI.ORG (Jack Hamilton) wrote: > Why not do it in Excel? It's simple to write a VBA routine that > converts all the XML files in a directory into XLS format. > > -- > Jack Hamilton > j...(a)alumni.stanford.org > Caelum non animum mutant qui trans mare currunt. > > On Oct 27, 2009, at 2:14 am, Alex wrote: > > > Hi all, > > I have xml files created with ODS tagsets.excelxp and would like to > > convert them from plain xml to xls and/or xlsx. I can open them in > > Excel and Save as..., but need to do it automatically from a SAS > > program. The sheets in the files are not just one rectangular data set > > each, but two or more data sets with different columns. Also the > > sheets contain a lot of formatting. So just importing the xml and > > exporting toxls/xlsxwouldn't work, I guess. > > Any ideas? > > Thanks, > > Alex > > Thanks Alan and Jack! While I had almost begun to write a C# program as Alan suggested, a colleague threw this DDE solution at me. Works fine and is easier for me to implement as a .Net app or an Excel macro: %* open the xml in Excel ; x "D:\temp\myfile.xml"; * give Excel 5 seconds to start; data _null_; x=sleep(5); run; %* submit dde commands for saving as regular xls and closing the file; filename xml2xls dde 'Excel|system'; data _null_; file xml2xls; __DDE_CMD = '[SAVE.AS("'||"D:\temp\myfile.xls"||'", 1)]'; put __DDE_CMD ; put '[CLOSE]'; run;
From: Savian on 30 Oct 2009 22:59 On Oct 30, 3:02 am, Alex <alexander.k...(a)iea-dpc.de> wrote: > On Oct 29, 2:19 am, j...(a)STANFORDALUMNI.ORG (Jack Hamilton) wrote: > > > > > > > Why not do it in Excel? It's simple to write a VBA routine that > > converts all the XML files in a directory into XLS format. > > > -- > > Jack Hamilton > > j...(a)alumni.stanford.org > > Caelum non animum mutant qui trans mare currunt. > > > On Oct 27, 2009, at 2:14 am, Alex wrote: > > > > Hi all, > > > I have xml files created with ODS tagsets.excelxp and would like to > > > convert them from plain xml to xls and/or xlsx. I can open them in > > > Excel and Save as..., but need to do it automatically from a SAS > > > program. The sheets in the files are not just one rectangular data set > > > each, but two or more data sets with different columns. Also the > > > sheets contain a lot of formatting. So just importing the xml and > > > exporting toxls/xlsxwouldn't work, I guess. > > > Any ideas? > > > Thanks, > > > Alex > > Thanks Alan and Jack! > While I had almost begun to write a C# program as Alan suggested, a > colleague threw this DDE solution at me. Works fine and is easier for > me to implement as a .Net app or an Excel macro: > > %* open the xml in Excel ; > x "D:\temp\myfile.xml"; > > * give Excel 5 seconds to start; > data _null_; > x=sleep(5); > run; > > %* submit dde commands for saving as regular xls and closing the file; > filename xml2xls dde 'Excel|system'; > data _null_; > file xml2xls; > __DDE_CMD = '[SAVE.AS("'||"D:\temp\myfile.xls"||'", 1)]'; > put __DDE_CMD ; > put '[CLOSE]'; > run;- Hide quoted text - > > - Show quoted text - Certainly you can use DDE but perhaps you shouldn't. Oftentimes we feel pressured to just make it work and move on to the next thing but an alternative codebase would probably suit you much better for future needs than DDE. Use DDE if you have to but consider doing it using a .NET library. SaviCellsPro, for example, does not require Excel to even be on the machine and does not use Excel to create the workbooks. There are so many issues with COM on Excel that it should cause a feeling of pause whenever it is considered. Alan
|
Pages: 1 Prev: ERROR: DBMS type EXCEL not valid for export???? Next: Data Step error handling |