write Sharepoint List data collection to text file

[Coyote2]

Hi,
I have this script that writes the number of records of list collection to a text file but I'm not sure how I can write the values to the file. can you help?
thank you.
--------------------------
Option Explicit

Dim url
Dim list
Dim viewFields
Dim request
Dim xmlDoc
Dim elements
dim myFSO
dim writestuff

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False

url = "

http://sharepoint/sites/SCxxFA/_vti_bin/Lists.asmx"

list = "Action"
viewFields = "<viewFields><FieldRef Name='ows_ID'/></viewFields>"

request = "<?xml version='1.0' encoding='utf-8'?>" + _
"<soap:Envelope xmlns:xsi='

http://www.w3.org/2001/XMLSchema-instance'"

+ _
" xmlns:xsd='

http://www.w3.org/2001/XMLSchema'"

+ _
" xmlns:soap='

http://schemas.xmlsoap.org/soap/envelope/'>"

+ _
" <soap:Body>" + _
" <GetListItems xmlns='

http://schemas.microsoft.com/sharepoint/soap/'>"

+ _
" <listName>" & list & "</listName>" + _
" <ViewFields>" & viewFields & "</ViewFields>" + _
" </GetListItems>" + _
" </soap:Body>" + _
"</soap:Envelope>"

'post it up and look at the response
with CreateObject("Microsoft.XMLHTTP")
.open "Get", url, False, null, null
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "SOAPAction","

http://schemas.microsoft.com/sharepoint/soap/GetListItems"

.send request

xmlDoc.loadXML(.responseText)

Set elements = xmlDoc.getElementsByTagName("rs:data")
'wscript.echo "Number of items in list: " + elements(0).getAttribute("ItemCount")

Set myFSO = CreateObject("Scripting.FileSystemObject")
Set WriteStuff = myFSO.OpenTextFile("correct.txt", 8, True)
'WriteStuff.WriteLine(elements(0).getAttribute("ItemCount"))
WriteStuff.WriteLine(elements(0).getAttribute("value"))
WriteStuff.Close
SET WriteStuff = NOTHING
SET myFSO = NOTHING
end with
-------------------------------------------------

 

[ehaze]

What are you trying to accomplish by downloading the List to a text file?

http://www.sharepointed.com/

 

[ehaze]

http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/bb1e18a1-9248-456e-889f-ca375f18428e

http://www.sharepointed.com/

 

[Coyote2]

ehaze,
the requirement is a weekly report that get the data and email it to recepients. I wish there is an easier way that I can think of..

thank you.

 

[ehaze]

Assume the recipients don't have access to your SharePoint environment?

To get data into / out of SharePoint, I use SSIS.

http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx

Using SSIS, you can simple export the List to a text file, Excel,....

Google: SSIS SharePoint

http://www.sharepointed.com/

 

[Coyote2]

ehaze,
what about automating the process. any idea how I can accomplish that?
thanks

 

[ehaze]

http://geekepisodes.com/sqlbi/2010/schedule-ssis-package-execution-with-windows-task-scheduler/

This should get you rolling.

http://www.sharepointed.com/

 

[Coyote2]

ehaze,
I was able to download SharePointListAdaptersSetup.msi but the the file for SqlServer2005.msi is no longer avaialble.

after the install I went to enable the Sharepoint list source/destination from file -->choose items --> SSIS Data Flow Items but I didn't find them.

I'm not sure why the Sqlserver file is no longer exist on the web.

any suggestions?
thanks

 

[ehaze]

Control Flow Items:
double click Data Flow Task

Once you have the Data Flow Task in your Control Flow window, double click it.

Once it opens, you will have some options on left.
Data Flow Sources, select SharePoint List Source.

List list source is where you will be pulling data from.


Images:
Create Data Flow Task

Add SharePoint List Source to the Task.

http://www.sharepointed.com/

 

[ehaze]

one more idea.

you can also do this file export form Access.

link to your SharePoint List from Access, and write a macro or VBA to export the List to a file.

http://www.sharepointed.com/

 

[Coyote2]

ehaze,
thanks for all your help. it's getting complicated now. I'm able to export the data to flat file or excel but I'm running into the problem of appending to the file if I run in again as of overrighting. my research say that I have to either create and excel with specific name on the fly then dump the data in it OR I have to execute a command to clear the data..

thank you.

 

[ehaze]

select your Flat File Destination, look at the Properties window on the right side of the screen. Now scroll down, do you see Custom Properties --> Overwrite?

Set this to True.

I just tested it, and every time i run my task, it truncates and appends data to the file.

http://www.sharepointed.com/

 

[Coyote2]

thank you very much. I was hoping I there is a similar property for the xsl file destination. are you aware of any?

many thanks

 

[Coyote2]

Ehaze,
there is a calculation in one sharepoint column and when I exported to flat file then to excel it will not formate properly so the best way is to export straight to Excel.

I will try to use SSIS for the whole process of
Import Sharepoint Date --> export to Excel sheet --> email
then maybe another vb script to open the existing excel file delete all data, except the first row (column names), then close it.

the funny thing is even if I manually clear or detete the excel data rows then when ssis tries to write to that sheet it detects these deteled/cleared rows and NOT overright the rows but add the data to the bottom..

thank you.

 

[ehaze]

I was shocked to see that the SSIS excel process doesn't truncate the excel file.

found another post where someone talks about deleting the 'table' / sheet in excel, then recreate it.

http://dbaspot.com/ms-sqlserver/357322-excel-template-required-export-ssis.html

On the Control Flow panel, drop an "Execute SQL Task" and change it's
Connection Type to "EXCEL" and specify your Source/Destination as Connection.
For the SQL Statement, you can use...

DROP TABLE {WorksheetName}

Now, since you've eliminated the "table structure" for the Destination (or
Source), you'll need to also... drop another "Execute SQL Task" object on
the Control Flow to (re)create the worksheet/table structure...

For example...

CREATE TABLE `{YourWorksheetName}` (
`SOURCE_HEADER_ID` DOUBLE PRECISION,
`SOURCE_LINE_ID` DOUBLE PRECISION,
`CREATION_DATE` DATETIME,
`LAST_UPDATE_DATE` DATETIME,
`ERROR_CODE` NVARCHAR(240),
`ERROR_EXPLANATION` NVARCHAR(240),
`REQUEST_ID` DOUBLE PRECISION
....blah, blah...
)

So, to recap... On your Control Flow... you'd have a SQL Task (Drop table)
which ties to another SQL Task (create table) which then ties to your Data
Flow object.

For each worksheet involved with the Data Flow, you'd need a Drop/Create SQL
Task pairing.

http://www.sharepointed.com/