Can I get Excel to import XML like Access?

Discussion in 'Microsoft Access' started by Ed from AZ, Nov 14, 2007.

  1. Ed from AZ

    Ed from AZ Guest

    (Windows XP Pro, Office 2003) I work with a Java-based program that
    creates an XML file. Normally, we do not work directly with the XML
    files. Sometimes, though, information needs to be transferred between
    XML docs, and I've been trying to find a way to do that.

    The Java program consists of forms to fill out, and some of them are
    repeating, or child, forms. This is the data that I am most
    interested it - seeing all the data for each of the child forms in a
    group where I can edit, add, delete, etc as I wish.

    When I try to open an XML file with Excel (either by right-clck on the
    XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    a schema that doesn't present the information in a way I can logically
    work with it. When I import the file into Access, thoguh, it creates
    a separate table for each major section.

    I am not familiar with Access, except to know that I can't manipulate
    data in an Access table like I can in an Excel spreadsheet. I would
    like to be able to get my XML data to import into Excel either as
    separate worksheets for each major section or to be able to choose
    just the one section to import and work with. Failing that, I'll
    probably see about importing into Access, exporting the one table out
    and Excel, working with it, importing that table back into Access, and
    then saving the file.

    Any pointers or suggestions would be greatly appreciated!

    Ed
     
    Ed from AZ, Nov 14, 2007
    #1
    1. Advertisements

  2. You can easily export data from Access to Excel, but remember that the data
    in Access is relational, while Excel is a flat file. If you have referential
    integrity enforced, it won't be as simple as exporting then reimporting
    back. Depending upon what you need to do, you may be able to do your
    manipulation in Access. If you need an analysis tool, Excel is just the
    ticket. For storing, manipulating, and retrieving large amounts of data,
    Access is the best choice.
    --
    Arvin Meyer, MCP, MVP
    http://www.datastrat.com
    http://www.mvps.org/access
    http://www.accessmvp.com

    "Ed from AZ" <> wrote in message
    news:...
    > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > creates an XML file. Normally, we do not work directly with the XML
    > files. Sometimes, though, information needs to be transferred between
    > XML docs, and I've been trying to find a way to do that.
    >
    > The Java program consists of forms to fill out, and some of them are
    > repeating, or child, forms. This is the data that I am most
    > interested it - seeing all the data for each of the child forms in a
    > group where I can edit, add, delete, etc as I wish.
    >
    > When I try to open an XML file with Excel (either by right-clck on the
    > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > a schema that doesn't present the information in a way I can logically
    > work with it. When I import the file into Access, thoguh, it creates
    > a separate table for each major section.
    >
    > I am not familiar with Access, except to know that I can't manipulate
    > data in an Access table like I can in an Excel spreadsheet. I would
    > like to be able to get my XML data to import into Excel either as
    > separate worksheets for each major section or to be able to choose
    > just the one section to import and work with. Failing that, I'll
    > probably see about importing into Access, exporting the one table out
    > and Excel, working with it, importing that table back into Access, and
    > then saving the file.
    >
    > Any pointers or suggestions would be greatly appreciated!
    >
    > Ed
    >
     
    Arvin Meyer [MVP], Nov 14, 2007
    #2
    1. Advertisements

  3. Ed from AZ

    Ed from AZ Guest

    Thanks for the reply, Arvin.

    The main manipulation I would want to accomplish with this is moving
    chunks of data from one file to another.

    One of the Java forms is a grid-style data entry. There can be
    multiple forms like this (these are "child" forms??) in one XML file.
    The grid reminds me of an Access table in that the left column auto-
    numbers, and several of the cells have drop-down lists. Within the
    Java form grid, though, I can not copy rows, either singly or in a
    block, to move to another XML file or even another child form in the
    same file.

    I have been able to accomplish this by opening the files in Word and
    using cut and paste, but that doesn't seem like the best way to work
    with a grid. This would work great in Excel, where I can simply copy
    a hug block of rows and move them. If I had a template set up with
    the drop-down validations to import this into, I could add new lines
    and do a few other things as well.

    As I said, I am unfamiliar with Access. I've managed to create a few
    tables, queries, and generic forms and reports, but not really get
    into a table and manipulate the information like this.

    Where should I go to learn more about how to accomplish this?

    Ed

    On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    > You can easily export data from Access to Excel, but remember that the data
    > in Access is relational, while Excel is a flat file. If you have referential
    > integrity enforced, it won't be as simple as exporting then reimporting
    > back. Depending upon what you need to do, you may be able to do your
    > manipulation in Access. If you need an analysis tool, Excel is just the
    > ticket. For storing, manipulating, and retrieving large amounts of data,
    > Access is the best choice.
    > --
    > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
    >
    > "Ed from AZ" <> wrote in messagenews:...
    >
    >
    >
    > > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > > creates an XML file. Normally, we do not work directly with the XML
    > > files. Sometimes, though, information needs to be transferred between
    > > XML docs, and I've been trying to find a way to do that.

    >
    > > The Java program consists of forms to fill out, and some of them are
    > > repeating, or child, forms. This is the data that I am most
    > > interested it - seeing all the data for each of the child forms in a
    > > group where I can edit, add, delete, etc as I wish.

    >
    > > When I try to open an XML file with Excel (either by right-clck on the
    > > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > > a schema that doesn't present the information in a way I can logically
    > > work with it. When I import the file into Access, thoguh, it creates
    > > a separate table for each major section.

    >
    > > I am not familiar with Access, except to know that I can't manipulate
    > > data in an Access table like I can in an Excel spreadsheet. I would
    > > like to be able to get my XML data to import into Excel either as
    > > separate worksheets for each major section or to be able to choose
    > > just the one section to import and work with. Failing that, I'll
    > > probably see about importing into Access, exporting the one table out
    > > and Excel, working with it, importing that table back into Access, and
    > > then saving the file.

    >
    > > Any pointers or suggestions would be greatly appreciated!

    >
    > > Ed- Hide quoted text -

    >
    > - Show quoted text -
     
    Ed from AZ, Nov 14, 2007
    #3
  4. Is it possible to walk through the XML data and extract it to a plain CSV
    text file? If you can get rid of all the extraneous XML junk, you can do
    almost anything you want. Word, Excel, and Access (and almost anything else)
    can read a plain text file without the encumbrance of having to use an xlt
    template.
    --
    Arvin Meyer, MCP, MVP
    http://www.datastrat.com
    http://www.mvps.org/access
    http://www.accessmvp.com

    "Ed from AZ" <> wrote in message
    news:...
    > Thanks for the reply, Arvin.
    >
    > The main manipulation I would want to accomplish with this is moving
    > chunks of data from one file to another.
    >
    > One of the Java forms is a grid-style data entry. There can be
    > multiple forms like this (these are "child" forms??) in one XML file.
    > The grid reminds me of an Access table in that the left column auto-
    > numbers, and several of the cells have drop-down lists. Within the
    > Java form grid, though, I can not copy rows, either singly or in a
    > block, to move to another XML file or even another child form in the
    > same file.
    >
    > I have been able to accomplish this by opening the files in Word and
    > using cut and paste, but that doesn't seem like the best way to work
    > with a grid. This would work great in Excel, where I can simply copy
    > a hug block of rows and move them. If I had a template set up with
    > the drop-down validations to import this into, I could add new lines
    > and do a few other things as well.
    >
    > As I said, I am unfamiliar with Access. I've managed to create a few
    > tables, queries, and generic forms and reports, but not really get
    > into a table and manipulate the information like this.
    >
    > Where should I go to learn more about how to accomplish this?
    >
    > Ed
    >
    > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    >> You can easily export data from Access to Excel, but remember that the
    >> data
    >> in Access is relational, while Excel is a flat file. If you have
    >> referential
    >> integrity enforced, it won't be as simple as exporting then reimporting
    >> back. Depending upon what you need to do, you may be able to do your
    >> manipulation in Access. If you need an analysis tool, Excel is just the
    >> ticket. For storing, manipulating, and retrieving large amounts of data,
    >> Access is the best choice.
    >> --
    >> Arvin Meyer, MCP,
    >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
    >>
    >> "Ed from AZ" <> wrote in
    >> messagenews:...
    >>
    >>
    >>
    >> > (Windows XP Pro, Office 2003) I work with a Java-based program that
    >> > creates an XML file. Normally, we do not work directly with the XML
    >> > files. Sometimes, though, information needs to be transferred between
    >> > XML docs, and I've been trying to find a way to do that.

    >>
    >> > The Java program consists of forms to fill out, and some of them are
    >> > repeating, or child, forms. This is the data that I am most
    >> > interested it - seeing all the data for each of the child forms in a
    >> > group where I can edit, add, delete, etc as I wish.

    >>
    >> > When I try to open an XML file with Excel (either by right-clck on the
    >> > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    >> > a schema that doesn't present the information in a way I can logically
    >> > work with it. When I import the file into Access, thoguh, it creates
    >> > a separate table for each major section.

    >>
    >> > I am not familiar with Access, except to know that I can't manipulate
    >> > data in an Access table like I can in an Excel spreadsheet. I would
    >> > like to be able to get my XML data to import into Excel either as
    >> > separate worksheets for each major section or to be able to choose
    >> > just the one section to import and work with. Failing that, I'll
    >> > probably see about importing into Access, exporting the one table out
    >> > and Excel, working with it, importing that table back into Access, and
    >> > then saving the file.

    >>
    >> > Any pointers or suggestions would be greatly appreciated!

    >>
    >> > Ed- Hide quoted text -

    >>
    >> - Show quoted text -

    >
    >
     
    Arvin Meyer [MVP], Nov 14, 2007
    #4
  5. Ed from AZ

    Ed from AZ Guest

    > Is it possible to walk through the XML data and extract it to a plain CSV
    > text file?


    Is this a function found in Word, Excel, or Access? I could probably
    write a VBA macro in Word or Excel to take out all the tags. But
    then, I could just aas easily make the macro find all the appropriate
    tags and create a table or spreadsheet. But then I have to write it
    all back into the XML file.

    This file doesn't have a schema recognized by either Excel or Access
    when I directly open a file, so writing it back is a head-scratcher at
    the moment. (I obviously don't know much about XML, either!)

    Whatever I do, I have to wind up with everything back in an XML file
    with all the right tags in the right place. When I saw that Access
    opened it all up in separate tables, I thought I might have found my
    answer.

    Ed

    On Nov 14, 8:35 am, "Arvin Meyer [MVP]" <> wrote:
    > Is it possible to walk through the XML data and extract it to a plain CSV
    > text file? If you can get rid of all the extraneous XML junk, you can do
    > almost anything you want. Word, Excel, and Access (and almost anything else)
    > can read a plain text file without the encumbrance of having to use an xlt
    > template.
    > --
    > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
    >
    > "Ed from AZ" <> wrote in messagenews:...
    >
    >
    >
    > > Thanks for the reply, Arvin.

    >
    > > The main manipulation I would want to accomplish with this is moving
    > > chunks of data from one file to another.

    >
    > > One of the Java forms is a grid-style data entry. There can be
    > > multiple forms like this (these are "child" forms??) in one XML file.
    > > The grid reminds me of an Access table in that the left column auto-
    > > numbers, and several of the cells have drop-down lists. Within the
    > > Java form grid, though, I can not copy rows, either singly or in a
    > > block, to move to another XML file or even another child form in the
    > > same file.

    >
    > > I have been able to accomplish this by opening the files in Word and
    > > using cut and paste, but that doesn't seem like the best way to work
    > > with a grid. This would work great in Excel, where I can simply copy
    > > a hug block of rows and move them. If I had a template set up with
    > > the drop-down validations to import this into, I could add new lines
    > > and do a few other things as well.

    >
    > > As I said, I am unfamiliar with Access. I've managed to create a few
    > > tables, queries, and generic forms and reports, but not really get
    > > into a table and manipulate the information like this.

    >
    > > Where should I go to learn more about how to accomplish this?

    >
    > > Ed

    >
    > > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    > >> You can easily export data from Access to Excel, but remember that the
    > >> data
    > >> in Access is relational, while Excel is a flat file. If you have
    > >> referential
    > >> integrity enforced, it won't be as simple as exporting then reimporting
    > >> back. Depending upon what you need to do, you may be able to do your
    > >> manipulation in Access. If you need an analysis tool, Excel is just the
    > >> ticket. For storing, manipulating, and retrieving large amounts of data,
    > >> Access is the best choice.
    > >> --
    > >> Arvin Meyer, MCP,
    > >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > >> "Ed from AZ" <> wrote in
    > >> messagenews:...

    >
    > >> > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > >> > creates an XML file. Normally, we do not work directly with the XML
    > >> > files. Sometimes, though, information needs to be transferred between
    > >> > XML docs, and I've been trying to find a way to do that.

    >
    > >> > The Java program consists of forms to fill out, and some of them are
    > >> > repeating, or child, forms. This is the data that I am most
    > >> > interested it - seeing all the data for each of the child forms in a
    > >> > group where I can edit, add, delete, etc as I wish.

    >
    > >> > When I try to open an XML file with Excel (either by right-clck on the
    > >> > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > >> > a schema that doesn't present the information in a way I can logically
    > >> > work with it. When I import the file into Access, thoguh, it creates
    > >> > a separate table for each major section.

    >
    > >> > I am not familiar with Access, except to know that I can't manipulate
    > >> > data in an Access table like I can in an Excel spreadsheet. I would
    > >> > like to be able to get my XML data to import into Excel either as
    > >> > separate worksheets for each major section or to be able to choose
    > >> > just the one section to import and work with. Failing that, I'll
    > >> > probably see about importing into Access, exporting the one table out
    > >> > and Excel, working with it, importing that table back into Access, and
    > >> > then saving the file.

    >
    > >> > Any pointers or suggestions would be greatly appreciated!

    >
    > >> > Ed- Hide quoted text -

    >
    > >> - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    Ed from AZ, Nov 14, 2007
    #5
  6. Access can write it back to XML, but you'll need to use the template. It
    cannot create those templates for you.
    --
    Arvin Meyer, MCP, MVP
    http://www.datastrat.com
    http://www.mvps.org/access
    http://www.accessmvp.com

    "Ed from AZ" <> wrote in message
    news:...
    >> Is it possible to walk through the XML data and extract it to a plain CSV
    >> text file?

    >
    > Is this a function found in Word, Excel, or Access? I could probably
    > write a VBA macro in Word or Excel to take out all the tags. But
    > then, I could just aas easily make the macro find all the appropriate
    > tags and create a table or spreadsheet. But then I have to write it
    > all back into the XML file.
    >
    > This file doesn't have a schema recognized by either Excel or Access
    > when I directly open a file, so writing it back is a head-scratcher at
    > the moment. (I obviously don't know much about XML, either!)
    >
    > Whatever I do, I have to wind up with everything back in an XML file
    > with all the right tags in the right place. When I saw that Access
    > opened it all up in separate tables, I thought I might have found my
    > answer.
    >
    > Ed
    >
    > On Nov 14, 8:35 am, "Arvin Meyer [MVP]" <> wrote:
    >> Is it possible to walk through the XML data and extract it to a plain CSV
    >> text file? If you can get rid of all the extraneous XML junk, you can do
    >> almost anything you want. Word, Excel, and Access (and almost anything
    >> else)
    >> can read a plain text file without the encumbrance of having to use an
    >> xlt
    >> template.
    >> --
    >> Arvin Meyer, MCP,
    >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
    >>
    >> "Ed from AZ" <> wrote in
    >> messagenews:...
    >>
    >>
    >>
    >> > Thanks for the reply, Arvin.

    >>
    >> > The main manipulation I would want to accomplish with this is moving
    >> > chunks of data from one file to another.

    >>
    >> > One of the Java forms is a grid-style data entry. There can be
    >> > multiple forms like this (these are "child" forms??) in one XML file.
    >> > The grid reminds me of an Access table in that the left column auto-
    >> > numbers, and several of the cells have drop-down lists. Within the
    >> > Java form grid, though, I can not copy rows, either singly or in a
    >> > block, to move to another XML file or even another child form in the
    >> > same file.

    >>
    >> > I have been able to accomplish this by opening the files in Word and
    >> > using cut and paste, but that doesn't seem like the best way to work
    >> > with a grid. This would work great in Excel, where I can simply copy
    >> > a hug block of rows and move them. If I had a template set up with
    >> > the drop-down validations to import this into, I could add new lines
    >> > and do a few other things as well.

    >>
    >> > As I said, I am unfamiliar with Access. I've managed to create a few
    >> > tables, queries, and generic forms and reports, but not really get
    >> > into a table and manipulate the information like this.

    >>
    >> > Where should I go to learn more about how to accomplish this?

    >>
    >> > Ed

    >>
    >> > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    >> >> You can easily export data from Access to Excel, but remember that the
    >> >> data
    >> >> in Access is relational, while Excel is a flat file. If you have
    >> >> referential
    >> >> integrity enforced, it won't be as simple as exporting then
    >> >> reimporting
    >> >> back. Depending upon what you need to do, you may be able to do your
    >> >> manipulation in Access. If you need an analysis tool, Excel is just
    >> >> the
    >> >> ticket. For storing, manipulating, and retrieving large amounts of
    >> >> data,
    >> >> Access is the best choice.
    >> >> --
    >> >> Arvin Meyer, MCP,
    >> >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >>
    >> >> "Ed from AZ" <> wrote in
    >> >> messagenews:...

    >>
    >> >> > (Windows XP Pro, Office 2003) I work with a Java-based program that
    >> >> > creates an XML file. Normally, we do not work directly with the XML
    >> >> > files. Sometimes, though, information needs to be transferred
    >> >> > between
    >> >> > XML docs, and I've been trying to find a way to do that.

    >>
    >> >> > The Java program consists of forms to fill out, and some of them are
    >> >> > repeating, or child, forms. This is the data that I am most
    >> >> > interested it - seeing all the data for each of the child forms in a
    >> >> > group where I can edit, add, delete, etc as I wish.

    >>
    >> >> > When I try to open an XML file with Excel (either by right-clck on
    >> >> > the
    >> >> > XML file and Open With or XL >> Data >> XML >> Import), Excel
    >> >> > creates
    >> >> > a schema that doesn't present the information in a way I can
    >> >> > logically
    >> >> > work with it. When I import the file into Access, thoguh, it
    >> >> > creates
    >> >> > a separate table for each major section.

    >>
    >> >> > I am not familiar with Access, except to know that I can't
    >> >> > manipulate
    >> >> > data in an Access table like I can in an Excel spreadsheet. I would
    >> >> > like to be able to get my XML data to import into Excel either as
    >> >> > separate worksheets for each major section or to be able to choose
    >> >> > just the one section to import and work with. Failing that, I'll
    >> >> > probably see about importing into Access, exporting the one table
    >> >> > out
    >> >> > and Excel, working with it, importing that table back into Access,
    >> >> > and
    >> >> > then saving the file.

    >>
    >> >> > Any pointers or suggestions would be greatly appreciated!

    >>
    >> >> > Ed- Hide quoted text -

    >>
    >> >> - Show quoted text -- Hide quoted text -

    >>
    >> - Show quoted text -

    >
    >
     
    Arvin Meyer [MVP], Nov 14, 2007
    #6
  7. Ed from AZ

    Ed from AZ Guest

    I just took a sample file, imported it into Access, took one of the
    tables and modified it, then tried to export the file back into XML.
    All it did was take just the one table and export it, rendering the
    rest of the XML useless.

    So I need to learn about how to create an Access template for these
    files that will accept a change to one table and recreate a useable
    XML file?

    I'm beginning to think I would be better off in a VBA macro in Excel,
    parsing this out as text and writing it to a worksheet, then writing
    it back with the tags thrown in.

    Ed

    On Nov 14, 9:46 am, "Arvin Meyer [MVP]" <> wrote:
    > Access can write it back to XML, but you'll need to use the template. It
    > cannot create those templates for you.
    > --
    > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com
    >
    > "Ed from AZ" <> wrote in messagenews:...
    >
    >
    >
    > >> Is it possible to walk through the XML data and extract it to a plain CSV
    > >> text file?

    >
    > > Is this a function found in Word, Excel, or Access? I could probably
    > > write a VBA macro in Word or Excel to take out all the tags. But
    > > then, I could just aas easily make the macro find all the appropriate
    > > tags and create a table or spreadsheet. But then I have to write it
    > > all back into the XML file.

    >
    > > This file doesn't have a schema recognized by either Excel or Access
    > > when I directly open a file, so writing it back is a head-scratcher at
    > > the moment. (I obviously don't know much about XML, either!)

    >
    > > Whatever I do, I have to wind up with everything back in an XML file
    > > with all the right tags in the right place. When I saw that Access
    > > opened it all up in separate tables, I thought I might have found my
    > > answer.

    >
    > > Ed

    >
    > > On Nov 14, 8:35 am, "Arvin Meyer [MVP]" <> wrote:
    > >> Is it possible to walk through the XML data and extract it to a plain CSV
    > >> text file? If you can get rid of all the extraneous XML junk, you can do
    > >> almost anything you want. Word, Excel, and Access (and almost anything
    > >> else)
    > >> can read a plain text file without the encumbrance of having to use an
    > >> xlt
    > >> template.
    > >> --
    > >> Arvin Meyer, MCP,
    > >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > >> "Ed from AZ" <> wrote in
    > >> messagenews:...

    >
    > >> > Thanks for the reply, Arvin.

    >
    > >> > The main manipulation I would want to accomplish with this is moving
    > >> > chunks of data from one file to another.

    >
    > >> > One of the Java forms is a grid-style data entry. There can be
    > >> > multiple forms like this (these are "child" forms??) in one XML file.
    > >> > The grid reminds me of an Access table in that the left column auto-
    > >> > numbers, and several of the cells have drop-down lists. Within the
    > >> > Java form grid, though, I can not copy rows, either singly or in a
    > >> > block, to move to another XML file or even another child form in the
    > >> > same file.

    >
    > >> > I have been able to accomplish this by opening the files in Word and
    > >> > using cut and paste, but that doesn't seem like the best way to work
    > >> > with a grid. This would work great in Excel, where I can simply copy
    > >> > a hug block of rows and move them. If I had a template set up with
    > >> > the drop-down validations to import this into, I could add new lines
    > >> > and do a few other things as well.

    >
    > >> > As I said, I am unfamiliar with Access. I've managed to create a few
    > >> > tables, queries, and generic forms and reports, but not really get
    > >> > into a table and manipulate the information like this.

    >
    > >> > Where should I go to learn more about how to accomplish this?

    >
    > >> > Ed

    >
    > >> > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    > >> >> You can easily export data from Access to Excel, but remember that the
    > >> >> data
    > >> >> in Access is relational, while Excel is a flat file. If you have
    > >> >> referential
    > >> >> integrity enforced, it won't be as simple as exporting then
    > >> >> reimporting
    > >> >> back. Depending upon what you need to do, you may be able to do your
    > >> >> manipulation in Access. If you need an analysis tool, Excel is just
    > >> >> the
    > >> >> ticket. For storing, manipulating, and retrieving large amounts of
    > >> >> data,
    > >> >> Access is the best choice.
    > >> >> --
    > >> >> Arvin Meyer, MCP,
    > >> >> MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > >> >> "Ed from AZ" <> wrote in
    > >> >> messagenews:...

    >
    > >> >> > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > >> >> > creates an XML file. Normally, we do not work directly with the XML
    > >> >> > files. Sometimes, though, information needs to be transferred
    > >> >> > between
    > >> >> > XML docs, and I've been trying to find a way to do that.

    >
    > >> >> > The Java program consists of forms to fill out, and some of them are
    > >> >> > repeating, or child, forms. This is the data that I am most
    > >> >> > interested it - seeing all the data for each of the child forms in a
    > >> >> > group where I can edit, add, delete, etc as I wish.

    >
    > >> >> > When I try to open an XML file with Excel (either by right-clck on
    > >> >> > the
    > >> >> > XML file and Open With or XL >> Data >> XML >> Import), Excel
    > >> >> > creates
    > >> >> > a schema that doesn't present the information in a way I can
    > >> >> > logically
    > >> >> > work with it. When I import the file into Access, thoguh, it
    > >> >> > creates
    > >> >> > a separate table for each major section.

    >
    > >> >> > I am not familiar with Access, except to know that I can't
    > >> >> > manipulate
    > >> >> > data in an Access table like I can in an Excel spreadsheet. I would
    > >> >> > like to be able to get my XML data to import into Excel either as
    > >> >> > separate worksheets for each major section or to be able to choose
    > >> >> > just the one section to import and work with. Failing that, I'll
    > >> >> > probably see about importing into Access, exporting the one table
    > >> >> > out
    > >> >> > and Excel, working with it, importing that table back into Access,
    > >> >> > and
    > >> >> > then saving the file.

    >
    > >> >> > Any pointers or suggestions would be greatly appreciated!

    >
    > >> >> > Ed- Hide quoted text -

    >
    > >> >> - Show quoted text -- Hide quoted text -

    >
    > >> - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    Ed from AZ, Nov 14, 2007
    #7
  8. Ed from AZ

    ilia Guest

    So wouldn't something like:

    INSERT INTO File2 SELECT * FROM File1 WHERE myCondition=True

    do the trick for your data manipulation?


    On Nov 14, 10:22 am, Ed from AZ <> wrote:
    > Thanks for the reply, Arvin.
    >
    > The main manipulation I would want to accomplish with this is moving
    > chunks of data from one file to another.
    >
    > One of the Java forms is a grid-style data entry. There can be
    > multiple forms like this (these are "child" forms??) in one XML file.
    > The grid reminds me of an Access table in that the left column auto-
    > numbers, and several of the cells have drop-down lists. Within the
    > Java form grid, though, I can not copy rows, either singly or in a
    > block, to move to another XML file or even another child form in the
    > same file.
    >
    > I have been able to accomplish this by opening the files in Word and
    > using cut and paste, but that doesn't seem like the best way to work
    > with a grid. This would work great in Excel, where I can simply copy
    > a hug block of rows and move them. If I had a template set up with
    > the drop-down validations to import this into, I could add new lines
    > and do a few other things as well.
    >
    > As I said, I am unfamiliar with Access. I've managed to create a few
    > tables, queries, and generic forms and reports, but not really get
    > into a table and manipulate the information like this.
    >
    > Where should I go to learn more about how to accomplish this?
    >
    > Ed
    >
    > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:
    >
    >
    >
    > > You can easily export data from Access to Excel, but remember that the data
    > > in Access is relational, while Excel is a flat file. If you have referential
    > > integrity enforced, it won't be as simple as exporting then reimporting
    > > back. Depending upon what you need to do, you may be able to do your
    > > manipulation in Access. If you need an analysis tool, Excel is just the
    > > ticket. For storing, manipulating, and retrieving large amounts of data,
    > > Access is the best choice.
    > > --
    > > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > > "Ed from AZ" <> wrote in messagenews:...

    >
    > > > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > > > creates an XML file. Normally, we do not work directly with the XML
    > > > files. Sometimes, though, information needs to be transferred between
    > > > XML docs, and I've been trying to find a way to do that.

    >
    > > > The Java program consists of forms to fill out, and some of them are
    > > > repeating, or child, forms. This is the data that I am most
    > > > interested it - seeing all the data for each of the child forms in a
    > > > group where I can edit, add, delete, etc as I wish.

    >
    > > > When I try to open an XML file with Excel (either by right-clck on the
    > > > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > > > a schema that doesn't present the information in a way I can logically
    > > > work with it. When I import the file into Access, thoguh, it creates
    > > > a separate table for each major section.

    >
    > > > I am not familiar with Access, except to know that I can't manipulate
    > > > data in an Access table like I can in an Excel spreadsheet. I would
    > > > like to be able to get my XML data to import into Excel either as
    > > > separate worksheets for each major section or to be able to choose
    > > > just the one section to import and work with. Failing that, I'll
    > > > probably see about importing into Access, exporting the one table out
    > > > and Excel, working with it, importing that table back into Access, and
    > > > then saving the file.

    >
    > > > Any pointers or suggestions would be greatly appreciated!

    >
    > > > Ed- Hide quoted text -

    >
    > > - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    ilia, Nov 14, 2007
    #8
  9. Ed from AZ

    Ed from AZ Guest

    Um, I have no idea, Ilia. How would I set that up?

    Ed

    On Nov 14, 11:55 am, ilia <> wrote:
    > So wouldn't something like:
    >
    > INSERT INTO File2 SELECT * FROM File1 WHERE myCondition=True
    >
    > do the trick for your data manipulation?
    >
    > On Nov 14, 10:22 am, Ed from AZ <> wrote:
    >
    >
    >
    > > Thanks for the reply, Arvin.

    >
    > > The main manipulation I would want to accomplish with this is moving
    > > chunks of data from one file to another.

    >
    > > One of the Java forms is a grid-style data entry. There can be
    > > multiple forms like this (these are "child" forms??) in one XML file.
    > > The grid reminds me of an Access table in that the left column auto-
    > > numbers, and several of the cells have drop-down lists. Within the
    > > Java form grid, though, I can not copy rows, either singly or in a
    > > block, to move to another XML file or even another child form in the
    > > same file.

    >
    > > I have been able to accomplish this by opening the files in Word and
    > > using cut and paste, but that doesn't seem like the best way to work
    > > with a grid. This would work great in Excel, where I can simply copy
    > > a hug block of rows and move them. If I had a template set up with
    > > the drop-down validations to import this into, I could add new lines
    > > and do a few other things as well.

    >
    > > As I said, I am unfamiliar with Access. I've managed to create a few
    > > tables, queries, and generic forms and reports, but not really get
    > > into a table and manipulate the information like this.

    >
    > > Where should I go to learn more about how to accomplish this?

    >
    > > Ed

    >
    > > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:

    >
    > > > You can easily export data from Access to Excel, but remember that the data
    > > > in Access is relational, while Excel is a flat file. If you have referential
    > > > integrity enforced, it won't be as simple as exporting then reimporting
    > > > back. Depending upon what you need to do, you may be able to do your
    > > > manipulation in Access. If you need an analysis tool, Excel is just the
    > > > ticket. For storing, manipulating, and retrieving large amounts of data,
    > > > Access is the best choice.
    > > > --
    > > > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > > > "Ed from AZ" <> wrote in messagenews:...

    >
    > > > > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > > > > creates an XML file. Normally, we do not work directly with the XML
    > > > > files. Sometimes, though, information needs to be transferred between
    > > > > XML docs, and I've been trying to find a way to do that.

    >
    > > > > The Java program consists of forms to fill out, and some of them are
    > > > > repeating, or child, forms. This is the data that I am most
    > > > > interested it - seeing all the data for each of the child forms in a
    > > > > group where I can edit, add, delete, etc as I wish.

    >
    > > > > When I try to open an XML file with Excel (either by right-clck on the
    > > > > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > > > > a schema that doesn't present the information in a way I can logically
    > > > > work with it. When I import the file into Access, thoguh, it creates
    > > > > a separate table for each major section.

    >
    > > > > I am not familiar with Access, except to know that I can't manipulate
    > > > > data in an Access table like I can in an Excel spreadsheet. I would
    > > > > like to be able to get my XML data to import into Excel either as
    > > > > separate worksheets for each major section or to be able to choose
    > > > > just the one section to import and work with. Failing that, I'll
    > > > > probably see about importing into Access, exporting the one table out
    > > > > and Excel, working with it, importing that table back into Access, and
    > > > > then saving the file.

    >
    > > > > Any pointers or suggestions would be greatly appreciated!

    >
    > > > > Ed- Hide quoted text -

    >
    > > > - Show quoted text -- Hide quoted text -

    >
    > > - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    Ed from AZ, Nov 14, 2007
    #9
  10. Ed from AZ

    Ed from AZ Guest

    Okay - an Append Query, according to Access Help files.

    That would probably be okay, once I got the hang of making things
    happen.

    Right now, though, I'm really stuck on getting all these tables back
    into an XML file. If I can't do that, then I really need to consider
    another method.

    Thanks for chiming in with help. I appreciate the interest in getting
    a newbi un-lost!

    Ed

    On Nov 14, 1:48 pm, Ed from AZ <> wrote:
    > Um, I have no idea, Ilia. How would I set that up?
    >
    > Ed
    >
    > On Nov 14, 11:55 am, ilia <> wrote:
    >
    >
    >
    > > So wouldn't something like:

    >
    > > INSERT INTO File2 SELECT * FROM File1 WHERE myCondition=True

    >
    > > do the trick for your data manipulation?

    >
    > > On Nov 14, 10:22 am, Ed from AZ <> wrote:

    >
    > > > Thanks for the reply, Arvin.

    >
    > > > The main manipulation I would want to accomplish with this is moving
    > > > chunks of data from one file to another.

    >
    > > > One of the Java forms is a grid-style data entry. There can be
    > > > multiple forms like this (these are "child" forms??) in one XML file.
    > > > The grid reminds me of an Access table in that the left column auto-
    > > > numbers, and several of the cells have drop-down lists. Within the
    > > > Java form grid, though, I can not copy rows, either singly or in a
    > > > block, to move to another XML file or even another child form in the
    > > > same file.

    >
    > > > I have been able to accomplish this by opening the files in Word and
    > > > using cut and paste, but that doesn't seem like the best way to work
    > > > with a grid. This would work great in Excel, where I can simply copy
    > > > a hug block of rows and move them. If I had a template set up with
    > > > the drop-down validations to import this into, I could add new lines
    > > > and do a few other things as well.

    >
    > > > As I said, I am unfamiliar with Access. I've managed to create a few
    > > > tables, queries, and generic forms and reports, but not really get
    > > > into a table and manipulate the information like this.

    >
    > > > Where should I go to learn more about how to accomplish this?

    >
    > > > Ed

    >
    > > > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:

    >
    > > > > You can easily export data from Access to Excel, but remember that the data
    > > > > in Access is relational, while Excel is a flat file. If you have referential
    > > > > integrity enforced, it won't be as simple as exporting then reimporting
    > > > > back. Depending upon what you need to do, you may be able to do your
    > > > > manipulation in Access. If you need an analysis tool, Excel is just the
    > > > > ticket. For storing, manipulating, and retrieving large amounts of data,
    > > > > Access is the best choice.
    > > > > --
    > > > > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > > > > "Ed from AZ" <> wrote in messagenews:...

    >
    > > > > > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > > > > > creates an XML file. Normally, we do not work directly with the XML
    > > > > > files. Sometimes, though, information needs to be transferred between
    > > > > > XML docs, and I've been trying to find a way to do that.

    >
    > > > > > The Java program consists of forms to fill out, and some of them are
    > > > > > repeating, or child, forms. This is the data that I am most
    > > > > > interested it - seeing all the data for each of the child forms in a
    > > > > > group where I can edit, add, delete, etc as I wish.

    >
    > > > > > When I try to open an XML file with Excel (either by right-clck on the
    > > > > > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > > > > > a schema that doesn't present the information in a way I can logically
    > > > > > work with it. When I import the file into Access, thoguh, it creates
    > > > > > a separate table for each major section.

    >
    > > > > > I am not familiar with Access, except to know that I can't manipulate
    > > > > > data in an Access table like I can in an Excel spreadsheet. I would
    > > > > > like to be able to get my XML data to import into Excel either as
    > > > > > separate worksheets for each major section or to be able to choose
    > > > > > just the one section to import and work with. Failing that, I'll
    > > > > > probably see about importing into Access, exporting the one table out
    > > > > > and Excel, working with it, importing that table back into Access, and
    > > > > > then saving the file.

    >
    > > > > > Any pointers or suggestions would be greatly appreciated!

    >
    > > > > > Ed- Hide quoted text -

    >
    > > > > - Show quoted text -- Hide quoted text -

    >
    > > > - Show quoted text -- Hide quoted text -

    >
    > > - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    Ed from AZ, Nov 14, 2007
    #10
  11. Ed from AZ

    ilia Guest

    Just open a new query, select SQL view, and paste that text. Replace
    myCondition=True with your actual condition and File1/File2 with
    actual table names.


    On Nov 14, 3:48 pm, Ed from AZ <> wrote:
    > Um, I have no idea, Ilia. How would I set that up?
    >
    > Ed
    >
    > On Nov 14, 11:55 am, ilia <> wrote:
    >
    >
    >
    > > So wouldn't something like:

    >
    > > INSERT INTO File2 SELECT * FROM File1 WHERE myCondition=True

    >
    > > do the trick for your data manipulation?

    >
    > > On Nov 14, 10:22 am, Ed from AZ <> wrote:

    >
    > > > Thanks for the reply, Arvin.

    >
    > > > The main manipulation I would want to accomplish with this is moving
    > > > chunks of data from one file to another.

    >
    > > > One of the Java forms is a grid-style data entry. There can be
    > > > multiple forms like this (these are "child" forms??) in one XML file.
    > > > The grid reminds me of an Access table in that the left column auto-
    > > > numbers, and several of the cells have drop-down lists. Within the
    > > > Java form grid, though, I can not copy rows, either singly or in a
    > > > block, to move to another XML file or even another child form in the
    > > > same file.

    >
    > > > I have been able to accomplish this by opening the files in Word and
    > > > using cut and paste, but that doesn't seem like the best way to work
    > > > with a grid. This would work great in Excel, where I can simply copy
    > > > a hug block of rows and move them. If I had a template set up with
    > > > the drop-down validations to import this into, I could add new lines
    > > > and do a few other things as well.

    >
    > > > As I said, I am unfamiliar with Access. I've managed to create a few
    > > > tables, queries, and generic forms and reports, but not really get
    > > > into a table and manipulate the information like this.

    >
    > > > Where should I go to learn more about how to accomplish this?

    >
    > > > Ed

    >
    > > > On Nov 14, 7:29 am, "Arvin Meyer [MVP]" <> wrote:

    >
    > > > > You can easily export data from Access to Excel, but remember that the data
    > > > > in Access is relational, while Excel is a flat file. If you have referential
    > > > > integrity enforced, it won't be as simple as exporting then reimporting
    > > > > back. Depending upon what you need to do, you may be able to do your
    > > > > manipulation in Access. If you need an analysis tool, Excel is just the
    > > > > ticket. For storing, manipulating, and retrieving large amounts of data,
    > > > > Access is the best choice.
    > > > > --
    > > > > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

    >
    > > > > "Ed from AZ" <> wrote in messagenews:...

    >
    > > > > > (Windows XP Pro, Office 2003) I work with a Java-based program that
    > > > > > creates an XML file. Normally, we do not work directly with the XML
    > > > > > files. Sometimes, though, information needs to be transferred between
    > > > > > XML docs, and I've been trying to find a way to do that.

    >
    > > > > > The Java program consists of forms to fill out, and some of them are
    > > > > > repeating, or child, forms. This is the data that I am most
    > > > > > interested it - seeing all the data for each of the child forms in a
    > > > > > group where I can edit, add, delete, etc as I wish.

    >
    > > > > > When I try to open an XML file with Excel (either by right-clck on the
    > > > > > XML file and Open With or XL >> Data >> XML >> Import), Excel creates
    > > > > > a schema that doesn't present the information in a way I can logically
    > > > > > work with it. When I import the file into Access, thoguh, it creates
    > > > > > a separate table for each major section.

    >
    > > > > > I am not familiar with Access, except to know that I can't manipulate
    > > > > > data in an Access table like I can in an Excel spreadsheet. I would
    > > > > > like to be able to get my XML data to import into Excel either as
    > > > > > separate worksheets for each major section or to be able to choose
    > > > > > just the one section to import and work with. Failing that, I'll
    > > > > > probably see about importing into Access, exporting the one table out
    > > > > > and Excel, working with it, importing that table back into Access, and
    > > > > > then saving the file.

    >
    > > > > > Any pointers or suggestions would be greatly appreciated!

    >
    > > > > > Ed- Hide quoted text -

    >
    > > > > - Show quoted text -- Hide quoted text -

    >
    > > > - Show quoted text -- Hide quoted text -

    >
    > > - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    ilia, Dec 5, 2007
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pow
    Replies:
    0
    Views:
    190
  2. Guest
    Replies:
    1
    Views:
    279
    MacDermott
    Sep 30, 2005
  3. Can XML be selected like this?

    , Oct 4, 2006, in forum: Microsoft Access
    Replies:
    0
    Views:
    212
  4. Ed from AZ
    Replies:
    0
    Views:
    374
    Ed from AZ
    Mar 27, 2009
  5. Ed from AZ
    Replies:
    16
    Views:
    275
Loading...

Share This Page