×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

write Sharepoint List data collection to text file

write Sharepoint List data collection to text file

write Sharepoint List data collection to text file

(OP)
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
-------------------------------------------------

RE: write Sharepoint List data collection to text file

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

http://www.sharepointed.com/

RE: write Sharepoint List data collection to text file

(OP)
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.

RE: write Sharepoint List data collection to text file

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

RE: write Sharepoint List data collection to text file

(OP)
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

RE: write Sharepoint List data collection to text file

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/

RE: write Sharepoint List data collection to text file

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/

RE: write Sharepoint List data collection to text file

(OP)
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.

RE: write Sharepoint List data collection to text file

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/

RE: write Sharepoint List data collection to text file

(OP)
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

RE: write Sharepoint List data collection to text file

(OP)
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.

RE: write Sharepoint List data collection to text file

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close