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

[LB]

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

[Reeza]

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.

[LB]

@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

[Reeza]

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

[RW9]

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

Also, why is saving as CSV not possible?

[Cynthia_sas]

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

[RW9]

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.

[LB]

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

[RW9]

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.

[Reeza · Accepted solution]

Please mark the question assumed answered

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

[LB]

Thanks RW9 and Reeza-

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

I have to go check that out!