Powershell script to export list data into excel at a scheduled interval


I am looking for a powershell script that will export SP list data into a spreadsheet at regular intervals. How do I write script for this and how do I schedule this job? Please note that I want all the fields from the list to be exported.

Any help is appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie McAllister MVPSharePoint ConsultantCommented:
I would use Task Scheduler on a Windows Server to schedule this. It's great in terms of being easy to use, robust, and has a credentials store for the script to run under.

I don't have a script to hand, but the export part should be relatively easy. As for the spreadsheet formatting it's possible to write excel spreadsheets as XML (in the correct schema) which is the easiest option for powershell. I've done dynamic spreadsheet creation before in C#.

I'll have a look in the community to see if there are scripts to get you started.

One other possibility would be to use the same export to excel function you get from lists and libraries by somehow using one of the .iqy files in a scheduler. I'll have to look into this, as I speculate it might be possible.
Jamie McAllister MVPSharePoint ConsultantCommented:
Task Scheduler can be made to work like this;
Download excel from SP
But you'd have to put in a bit more work to make it slick and get around the security prompts by turning them off. Then a Macro to save the file somewhere. Perhaps faster than trying to code something though...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jamie McAllister MVPSharePoint ConsultantCommented:
OK, here's some Powershell to get the data out of the list as CSV, which will be loadable by Excel;

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.