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
