 |
 |
I don't use Excel but a client did who wanted to use XSL I had created ONIX to MARC to transform bibliographic metadata in Excel to XML. The built in Excel XML converter was not very helpful since empty cells were skipped so that it was impossible to use that result. There is an add on that allow you to map your data to XML elements by creating a schema which is pretty cool. http://bit.ly/1jpwtqM This might be helpful. regards, dana On Fri, Jun 13, 2014 at 6:53 PM, Terry Brady <[log in to unmask]> wrote: > The current version of Excel offers a save as XML option. > > It will produce something like this. There is other wrapping metadata, but > the table is pretty easy to parse. > > <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="7" > x:FullColumns="1" > x:FullRows="1" ss:DefaultRowHeight="15"> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 1</Data></Cell> > <Cell><Data ss:Type="String">question 1</Data></Cell> > <Cell><Data ss:Type="String">answer 1</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 2</Data></Cell> > <Cell ss:Index="3"><Data ss:Type="String">answer 2</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 3</Data></Cell> > <Cell ss:Index="3"><Data ss:Type="String">answer 3</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 4</Data></Cell> > <Cell><Data ss:Type="String">question 2</Data></Cell> > <Cell><Data ss:Type="String">answer 1</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 5 </Data></Cell> > <Cell ss:Index="3"><Data ss:Type="String">answer 2</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"><Data ss:Type="String">row 6</Data></Cell> > <Cell><Data ss:Type="String">quest </Data></Cell> > <Cell><Data ss:Type="String">answer 3</Data></Cell> > </Row> > <Row> > <Cell ss:StyleID="s62"/> > </Row> > </Table> > > > On Fri, Jun 13, 2014 at 2:28 PM, Ryan Engel <[log in to unmask]> wrote: > > > Hello - > > > > I have an Excel spreadsheet that, for the purposes of an easy import into > > a Drupal site, I'd like to convert to XML. I know people more > > knowledgeable than I could code up something in Python or Perl to > convert a > > CSV version of the data to XML (and I have a colleague who offered to do > > just that for me), but I am looking for recommendations for something > more > > immediately accessible. > > > > Here's an idea of how the spreadsheet is structured: > > > > Row1 Question1 Q1Answer1 > > Row2 Q1Answer2 > > Row3 Q1Answer3 > > Row4 Question2 Q2Answer1 > > Row5 Q2Answer2 > > Row6 Question3 Q3Answer1 > > etc. > > > > How do other people approach this? Import the data to an SQL database, > > write some clever queries, and then export that to XML? Work some > wizardry > > in GoogleRefine/OpenRefine? Are scripting languages really the best all > > around solution? Excel's built in XML mapping function wasn't able to > > process the one-to-many relationship of questions to answers, though > maybe > > I just don't know how to build the mapping structure correctly. > > > > In the interest immanent deadlines, I have handed the spreadsheet off to > > my Perl-writing colleague. But as a professional growth opportunity, I'm > > interested in suggestions from Libraryland about ways others have > > approached this successfully. > > > > Thanks! > > > > Ryan Engel > > Web Stuff > > UW-Madison > > > > > > -- > Terry Brady > Applications Programmer Analyst > Georgetown University Library Information Technology > https://www.library.georgetown.edu/lit/code > 425-298-5498 > -- Dana Pearson dbpearsonmlis.com Metadata and Bibliographic Services for Libraries