Convert Excel Spreadsheet data to XML

Here is a quick video on how to convert Excel spreadsheet data to XML for import into Flash or Flex. Don't spend time typing all that data again create a schema and convert to XML. Convert Excel was created by Mike Lively Director of A&S Instructional Design at Northern Kentucky University NKU.

---

Closed Caption:

hello this is Mike lively from Northern
Kentucky University and today we're
going to show you how to convert that
Excel spreadsheet into an xml data sheet
now why would you want to do that well
typically when you're trying to import
data into flash or in flex you need to
have it into an XML format so it's
actually pretty easy to do
i'm showing you here a spreadsheet of
about four hundred fifty pieces of data
that you do not want to retype into an
xml data page
so let me show you how to convert it
first thing you need to do is create an
xml schema is actually the form of the
excel spreadsheet and from that you'll
do it using the column names
so we've actually created an xml schema
let me show you that real quick we have
it up in Dreamweaver and what you notice
it's only a partial portion of the data
we actually have two datasets here and
it's important to have at least two one
won't work because it won't be able to
identify how the xml schemas put
together once you create those tags i
just created the names and put the data
in it
you want to check that to make sure it's
validated so i'll go to file validate as
xml and it is this case I had no errors
but we have areas just go back and
correct them and you're ready to go so
once you have the xml schema save it and
let's import it into exile and create
that page so you can convert this excel
spreadsheet and xml data so open up a
new xmldocument click on data click on
xml and go to X and L source you open up
a little space here and down below click
on xml maps and click on ADD and that's
we're going to add the scheme that you
just created an XML or XL
and open that up and say okay and you've
added it and hit ok and there's your
schema right there you can see it's the
structure of your spreadsheet
it's got the investment data it's got
the . data and all those columns
let's just grab the end drag it right
onto your new excel sheet and there you
have it there's all those columns there
only one more thing you need to do you
need to take your original sheet cut and
paste that data
so go ahead and grab it all and once you
grab all that data and you copied it
one second here it's just going to
select it and then you're gonna grab it
all and copy it and once you grab it and
copied it
one thing i like to do sometimes it's
just go ahead and paste it into a
notepad document why did I do that
well the reason I did that is sometimes
these excel spreadsheet have functions
associated with them when you cut and
paste them from one to the other you
function will be carried along with it
so I just stuck it into a notepad
document and I just kind of clean up the
data i do this sometimes to what I'm
pasting into a database I'll just throw
it into a notepad and all through all
the special characters out and you just
have that raw data and so something you
might do if you're having problems if
you're not just cut and paste will be ok
so once you've done that
I'll cut and paste it into a new
document one second let me go back and
grab that data right click and copy it
and let's cut and paste it into our new
document here right into the first lot
and there's all my data and I'm almost
ready
now I want to do two things first I want
to save that as a workbook
go ahead and say that will call it
investment game two because there's only
one there already
there's already one there already and
that's going to save as a workbook so in
case i need to go back and modified I
can do that
and now i want to create my xml document
because save as and don't save it as an
XML spreadsheet save it as xml data and
when you do that
let's continue that and let's see if
indeed we've created our financial data
so i'm going to open that up let's do a
quick document is double click on that
and open that up and dreamer
and there you have it that 458 pieces of
data are now converted on the fly and I
don't have to type in hand by hand a
real time saver when you're working with
flash or flex and bringing data in from
the outside