Import multiple xml files, update/append SharePoint List (1 Viewer)

saswiss

New member
Local time
Tomorrow, 00:45
Joined
Jan 7, 2011
Messages
9
Hi,
I have a folder on a shared drive that contains .xml files (created by various users in Excel 2007). The contents of these files (with different names) contain data regarding tracking/progression of user requests on a SharePoint 2007 list. Once a request is created by user 1, an xml file is created (with a docID in one of the fields) and stored in the folder. This request is then sent (via Outlook) to the next person in line, who then has to approve/reject the request. Once he has done this, another xml file (using the same docID) is created in the same folder.

I need to import these xml files (one after the other) into Access 2007 (where the SharePoint 2007 list is available as a linked table) and add the content of the file to a temporary table. If a record with the docID already exists, the existing data in the SharePoint list should be updated. If none exists, the record should be appended to the SharePoint list.

Now for the tricky part...
This should all be done automatically (scheduled without human interface) at regular intervals
No messages (confirmation, warnings, etc.) should appear
The processed xml file should be moved to a sub-folder of the original shared folder. Then loop again to start until all xml files have been processed.

Attached is a very simple drawing to describe (hopefully) how I see this being done...

At the moment this is a manual process with macros by way of a button...

Hope someone can help!
Thanks!!!
 

Attachments

  • RequestUpdateProcess.pdf
    10.5 KB · Views: 415

Guus2005

AWF VIP
Local time
Tomorrow, 00:45
Joined
Jun 26, 2007
Messages
2,645
So what you request is a way to automate this process.
There are various ways to accomplish this.

1- Use a form and use the timer event to search for new XML files and start the process.
2- Use the dos AT command (or any other time driven task scheduler like SQL Agent or Control-M) to start Access and provide a parameter to start the macro which you have allready written.
3- Use SQL Server Integrated Services (SSIS) or Data Transformation Services (DTS) to perform the task. Depending on the SQL Server version.

Each of these options have their own advantages and disadvantages.

HTH:D
 

Users who are viewing this thread

Top Bottom