Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012

    Unanswered: Help w/ uploading folder of multiple xml files

    Hello all,

    I have a folder with ~200 xml files. File sizes range from ~2,000 - 10,000 kb. Files have exactly the same number of columns; the number of records/rows in each file range from 100 to 3,000. I'd like to upload all files and create a single table. It would be good to include to create in this table a column with the original xml file name; this would be nice, but it is not necessary.

    Is this possible to do in Access 2010? I assume it'd be using an SQL script, but I haven't been able to find an example by searching the web.

    If this isn't possible via Access 2010, could I do it somehow first in SQL Server and then export the resulting table into Access?

    Additional details: Data types of columns include currency, date, integer numbers, text, and memo (or an equivalent of >255 text characters). There are a bunch of excessive spaces and repeated instances of the pesky vertical lines that separate data within a single cell:

    e.g., in column named "Co-Partners" there could be
    "Jerry Jobs |Bob Cohem |Mark John |"

    where the lines separate the three co-partners corresponding to the record.

    Eventually, that formatting (extra spaces and multiple data in one cell) I'll have to address. But my first task is to get all those xml files into a single table without having to import 1 at a time manually and go through the append table process.

    Thanks in advance for any help that can be provided!


  2. #2
    Join Date
    Aug 2004
    Cary, NC
    You can write a vba routine that would loop thru all the files in the directory and then import them to an existing table.

    If you leave an extra field in the table for the source file you can run an update sql on the table between every load with the name of the file.

    Once you get everything into the database you should do some design changes and separate out the info into the proper table structure and cleanup the pipes at the same time.


Tags for this Thread

Posting Permissions

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