Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: XML data importing

    Hello;

    I am attempting to import data in XML format into Access. I have about 30 tables, each with approximately 100 fields of data. Currently I am importing the data into Excel using an add-in for conversion to XLS. I then Import the XLS spreadsheet into Access as a table. I then create a make-table query in Access to add a date field to the data (each exported file is void of an actual date, however each group of data represents data from a specific day). After the new table is created in Access (complete with the date field added), I then create an Append query to add the new data to the main table. In the end I want one large table in Access that can be queried by date (as well as other fields of course). Once all the previous data is appended into the main table, I will need to process each future days data into the main table.

    My question is… this seems like a crude way of accomplishing this task. Is there an easier way?

    Larry

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe there is and it is not called Access.
    There is a database (or databases) out there that support XML as a datatype!

    Alternatively - if you're staying with access - what does your XLS file look like after conversion?

    A sample XML and XLS might come in handy
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm able to import XML files into MSAccess 2003. It allows me to select XML as the file type for importing.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are looking for a way to automate the XML import, there is a method called XMLImport. It is part of the Application object. I don't know too much about it, but I used its counterpart XMLExport yesterday to export to an XML file.

  5. #5
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks all...

    I was pretty sure Access (my Access) would not import an XML file, however I will give it another go tomorrow morning. Even if it does, I might not be able to utilize such because after importing into Excel I need to delete the first row and the first column or Access will not recognize the column headings or PK field (otherwise the data structure is fine).

    It just seems like such a convoluted way of adding data to a table and simply adding a date field also. The way I did it works (I only did 2 files so far) but it is a major pain. I had hoped there might be an easier way of maybe adding the date field while appending the data to the main table. Maybe some sort of add-in like I did with Excel. All together I have 120+ exports (4x30) so I am dredding the tedious and repetative work.

    Thanks...

    Larry

  6. #6
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    UPDATE:

    Yes, Access 2003 can import XML files, however when it does it makes multiple tables of the data (about 12 tables in all)(perhaps due to the previously set up schema of the data being imported). I need the data in 1 main table. So the import function of XML to Access will not work for me in this instance.

    I am making due with the Excel>Access>Make Table>Append data function until something better comes along. I figure by Christmas I will be done importing all the files !

    Thanks...

    Larry

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you JOIN your tables in one big query?
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Sounds like a good idea Georgev... I'll give it a try.

    Thanks...

    Larry

  9. #9
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    UPDATE: I got the developer of the application to include the two fields I needed (Date and Status) in the export. Access will import the XML file and it creates multiple tables. Here's the problem... Access imports both the Date field and the Status field into the first table, however only the Date field is filled will data. The Status field is blank for all records. (three other fields are populated also - with data)

    When I import the XML into Excel via an add-in conversion tool, it imports both the Date data AND the Status data. I can also see the data in both the Date and Status fields in the schema of the XML file. Access just won't populate the field it creates for the Status data. I checked the properties of both the Date field and the Status field and the Date field is set to date/time, and the Status field is set to text with 255 field size. (the Status data is text). The status field data is no greater than maybe 30 characters at best.

    Has anyone see anything like this? Would you have any suggestions on how to import the Status data as does the Date data? Any help would be appreciated.

    Thanks...

    Larry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •