Freeware - WSS30ListUpload (Sharepoint)

I recently published a solution to query Sharepoint lists/document libraries using LogParser. I then thought about how you could output LogParser results directly to Sharepoint (WSS 3.0) lists.

Some advantages that I can see of using Sharepoint as a Log Parser output is that you have a more managed location for results and additionally you could utilise the alert facilities. e.g. provide a monthly IIS statistics report that is automatically distributed to required people.

After some thinking I decided upon a solution involving using the LogParser output 'Template' format (-o:TPL) to generate the Sharepoint batch XML. Outputing the resultant XML to STDOUT (in the LogParser query) and then redirecting (using pipe i.e, ) to a small tool named WSS30ListUpload (which I wrote) which uploads to a SharePoint site list or document library. This allows for a solution utilising features within Log Parser and therefore provides a very clean solution with a great amount of flexibility.

Note: The WSS30ListUpload tool can be used without Log Parser. In essence using this tool you have a command line driven Sharepoint list uploading tool. e.g.

type c:\listdata.xml | WSS30ListUpload.exe "http://mysite/site/Lists/MyList/AllItems.aspx"

Solution overview

1. Create a SharePoint Template file that matches the SharePoint Batch XML format. e.g., Create a LogParser Template File Named C:\Template.tpl. The Template should follow the below example. You can download a working XML in below. Add more FieldName_X as per the number of columns uploaded into SharePoint.

<LPHEADER><Batch OnError="Continue" PreCalc="TRUE" ListVersion="0" ViewName=""> </LPHEADER>
<LPBODY> <Method ID="%FIELD_1%" Cmd="New">
<Field Name="%FIELDNAME_2%">%FIELD_2%</Field>
<Field Name="%FIELDNAME_3%">%FIELD_3%</Field>
<Field Name="%FIELDNAME_4%">%FIELD_4%</Field>
<Field Name="%FIELDNAME_5%">%FIELD_5%</Field>
<Field Name="%FIELDNAME_6%">%FIELD_6%</Field>
<Field Name="%FIELDNAME_7%">%FIELD_7%</Field>
<Field Name="%FIELDNAME_8%">%FIELD_8%</Field>
</Method>
</LPBODY>
<LPFOOTER></Batch>
</LPFOOTER>

2. Place WSS30ListUpload.exe in C:\Program Files\LogParser 2.2\

3. Run the LogParser query ensuring
a) that the first column is a unique number per row. e.g. RecordNumber
b) that a column [ID] is generated with the text 'New'
c) that the column names match either the SharePoint 'DisplayName' (the title in SharePoint), or the underlying Name (e.g. 'This_x0020_Field' vs 'This Field') within the list.
d) that the INTO STDOUT clause is used.
e) that -stats:off is used to ensure that only the XML is generated to STDOUT.
f) that you pipe the result to WSS30ListUpload.exe which takes a single command line parameter of the SharePoint List URL.

e.g. LogParser.exe -i:EVT "Select Top 10 RecordNumber,'New' as [ID],EventLog,RecordNumber,TimeGenerated,TimeWritten,EventID,EventType into STDOUT from System" -tpl:c:\SharePointTemplate.tpl -stats:off | WSS30ListUpload.exe "http://mysite/site/Lists/Test/AllItems.aspx"

Note: The result from the execution is the Sharepoint Lists.asmx -> UpdateListItems XML response. Any errors resulting from the upload will be in this file. If you want to capture it to file redirect to a file. e.g. add > C:\Output.xml at the end of the example above.

Features of WSS30ListUpload

• Freeware with VB.NET 2005 source code included
• Can execute any Sharepoint Batch XML file against WSS 3.0 list, wiki, or document library content. Have only tested on WSS 3.0 - it may work on earlier or later versions. Uses the web service Lists.asmx -> UpdateListItems.
• Single command line parameter is SharePoint URL to List/Document Library. Internally resolves Site and List Name based on the URL.
• STDIN supports Batch XML
• The STDOUT from WSS30ListUpload is the result of Sharepoint Lists.asmx -> UpdateListItems in XML format. This includes any errors.
• Supports both Name and DisplayName for the Sharepoint column names. To do this it automatically translates Sharepoint column DisplayNames into Name (e.g 'This Field' becomes 'This_x0020_Field'). In addition if duplicate DisplayNames are found then will abort the upload.
• Logs any errors to console and to EventLog.Application
  

*************************************************
Download VB.NET source is included in download.
*************************************************

To install:

1. Copy the WSS30ListUpload.exe to C:\Program Files\Log Parser 2.2\

2. Setup the Template file required. Ensure you set the fields names equal to the Sharepoint names in your Logparser query and the number also matches the FIELD_X values in the Template file.

3. Run your Log Parser query.

*************************************************

Syntax

{STDIN Sharepoint Batch XML} WSS30ListUpload.exe {SharePointListURL}"

e.g. TYPE c:\batch.xml | WSS30ListUpload.exe http://mysite/site/lists/MyList/AllItems.aspx

*************************************************

** Legal **

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.