The SAS Output Delivery System and reporting techniques

reimporting a file that was exported through the ODS Excel tagset to XML

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 133
Accepted Solution

reimporting a file that was exported through the ODS Excel tagset to XML

OK folks-

The joke is on me but I need to import files that were exported in SAS 9.2 in ODS ExcelXP tagset as XML- 

Any ideas?

Thanks

Lawrence


Accepted Solutions
Solution
‎04-29-2015 12:20 PM
Grand Advisor
Posts: 17,393

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Please mark the question assumed answered Smiley Happy

There's also a macro on support.sas.com that will convert the XML to XLSX files.

View solution in original post


All Replies
Grand Advisor
Posts: 17,393

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Do the files have formatting/multiple tables per sheet? Or are they simple proc prints?

You might need to post a sample. If you have a variety of files that makes it harder as well.

Several options are: DDE read in, Convert file to CSV/Text and read in, or Parse the XML.  All will have gotcha's, but without knowing more it's hard to say.

Frequent Contributor
Frequent Contributor
Posts: 133

Re: reimporting a file that was exported through the ODS Excel tagset to XML

@Reeza-

What gets generated is a multi-tabbed document with six different sheets in it. It is hard to post as it has a lot of HIPAA protected data and internal data reserved only for the medical centers. By trying to mask everything takes a bit, but I will see what I can do.

Thanks Cynthia. I will take a look.

RW9-

I have the code that generates the file but I don't have the data as it grabs information from the server and then exports the files.

Long story to why I don't have the data  previous to April. I do have a program that retro-fill in the data but it takes a long time actuall to process each day.

I did try resaving as an Excel file which failed. Saving as a CSV is not possible.

Lawrence

Grand Advisor
Posts: 17,393

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Are the sheets proc print output? If so it should be relatively easy to re-input - save as xlsx and import.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Sorry, why did "I did try resaving as an Excel file which failed" it fail?

Also, why is saving as CSV not possible?

SAS Super FREQ
Posts: 8,720

Re: reimporting a file that was exported through the ODS Excel tagset to XML

HI:

  Theoretically, you might be able to use an XML MAP to import the SpreadsheetML 2003 XML back into SAS. This type of "round tripping" was described in this paper: http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf for a previous version of SAS and the TAGSETS.EXCELXP. I am not sure whether it will still work or not.

cynthia

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: reimporting a file that was exported through the ODS Excel tagset to XML

And you don't have either

A) The code used to generate the file

B) The data used to generate the file

My suggestion would be to open the file in Excel, save as CSV, then write a datastep to import the CSV data back again.

Frequent Contributor
Frequent Contributor
Posts: 133

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Reeza;

Yes saving it to a xls format in itself and doing a proc import works.

The issue is that I have some 2000 files!

RW9-Sorry if i was not more articulate-

What I had tried to do, and knew that it would fail, was to simply copy the file and rename the extension to xls.

CVS does not work in this case as the files are multi-tabbed.

In short what I was attempting to do was to find a faster way to import the data as many times I can build a pipe to import the file names and then a macro to import the data from each file, reassemble all the data. Usually it works really fast.

I have another method to get at the same data by pulling directly from the database. Unfortunately it is slow moving and will take about 2 days to process it all.

Cynthia-the XML mapping works insofar as it imports the different worksheets. However I am not sure if there is a parameter to set the row to start importing the data. Also that the some of the worksheets have names greater than 32 characters presents an issue as it won't import those sheets. 

Thanks for all your assistance on this. I consider this issue closed.

Lawrence

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Just to help on the file formats.  Saveas can easily be automated using VBA.  Here is a the first link from Google:

excel - Save each sheet in a workbook to separate CSV files - Stack Overflow

Shows a simple do loop over sheets to save each to files.  You can then also wrap around that a file open/close for each file in a directory.  A bit of code in Excel and you could convert a whole set of Excel files to a series of csv files in no time.  If the data is all the same, you could just keep streaming it out to one CSV.

As for the 32 characters, you can use: options validavarname=any;

You could also dump the data directly from the database, maybe quicker. 

Good luck with it.

Solution
‎04-29-2015 12:20 PM
Grand Advisor
Posts: 17,393

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Please mark the question assumed answered Smiley Happy

There's also a macro on support.sas.com that will convert the XML to XLSX files.

Frequent Contributor
Frequent Contributor
Posts: 133

Re: reimporting a file that was exported through the ODS Excel tagset to XML

Thanks RW9 and Reeza-

OOOh a macro that will convert over xml files to XLSX files-

I have to go check that out!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 712 views
  • 3 likes
  • 4 in conversation