I AM HERE

Easily import data from CSV and SQL to a SharePoint list

October 9, 2009 12:28 by Aidan

This SharePoint feature allows you to quickly import data from a CSV file or a SQL stored procedure to any custom SharePoint list.

Once you have added the solution to your farm and activated it on your site collection an additional menu option will be available on the Action menu of each custom list that will take you to the import CSV or SQL page.

Simply select which option you want - either import from a CSV file or import from a SQL stored procedure. Next, browse to the CSV file or enter the SQL connection and name of the stored procedure and hit the Import button.

There is a check box option to delete all items from the list before doing the import which is not selected by default.

IMPORTANT - the first line of the CSV file must contain the names of the SharePoint list columns you want to import the data to. Eg. If you want to import data into a list that has two columns called Title and Description then the first row of the CSV file will be Title, Description.

If you are using SQL then the stored procedure for the example above would need to be something like: SELECT Title, Description from [TableName]

Tags:
Categories: MOSS 2007 | SQL Server
Actions: E-mail | Permalink | Comments (15) | Comment RSS RSS comment feed

Generic CSV or SQL data importer for SharePoint listUPDATE (09/10/09) - NEW AND IMPROVED VERSION HERE I finally got bored of rewriting the same co...Downloading and uploading documents to SharePoint with metadata I need to copy some documents from a SharePoint 2003 server to a MOSS 2007 server to make them ava...Using centrally managed SharePoint data connection files with InfoPath 2007 When deploying InfoPath forms with data connections between environments it is possible to use cen...

Comments

October 20. 2009 05:43

Aidan,
Loved your previous Generic CSV importer, found it very useful. Was excited to see a updated version, though after installation and atempting to test it out there is a error (request failed) appearing upon clicking the menu button. I have tested on two test servers with the same result.
Matt

Matt

October 21. 2009 10:38

Matt,
Thanks for the feedback it is great to know people are finding this useful. I have rebuilt the wsp so it deploys to the GAC which should solve the issue. Let me know how you get on.

Aidan

October 21. 2009 22:41

Aidan,
Worked like a charm, thankyou. Its a great solution.

Matt

November 3. 2009 09:11

Pingback from petestilgoe.com

Import data from CSV and SQL to a SharePoint list « PeteStilgoe.com

petestilgoe.com

November 19. 2009 18:48

Aldan,

I installed the solution, Deployed it (Globally), but none of my sites with lists have the extra option in the action menu as you described. Any thoughts? Testing with an Administrator / Full access to the farm. Created few dummy sites as well. Thanks for your work on this, will be great when I get it running.

Joe

November 20. 2009 10:28

Joe - thanks for the feedback. It turns out that you still needed to activate the feature at site collection level. I will update the blog post to make this clearer for future users.

Aidan

February 1. 2010 15:27

Hi Aidan,

Thanks for the great solution. I would like to have your guidance. Once I upload my CSV data can you tell me how can I make one column as Primary key. Can you just give me guideline for programming steps.

Thanks,
Preet

Preet Patel

February 1. 2010 21:58

Preet,
SharePoint lists are different to SQL tables - one major difference is that they do not have primary keys.

Aidan

February 3. 2010 20:02

Aidan,

This is exactly what I've been looking for!  So simple but brilliant.  I've added the solution to my farm and deployed it globally...however I'm not following you when you say I need to 'Activate' it at the site collection level?  Can you elaborate a bit?

I've done the following:

1.  stsadm -o addsolution -filename C:\templates\template_name.wsp
2.  stsadm -o deploysolution -name template_name.wsp -immediate -allowgacdeployment

When I go to Site Settings > Site Features.....I'm unable to see the 'CSVImporter' for activation?

Any help would be greatly appreciated....can't wait to use this.

Thanks,

Vikas

Vikas

February 4. 2010 11:22

Vikas - you need to make sure that you are looking in site collection features. This feature deploys at the site collection level.

Aidan

February 4. 2010 20:14

Aidan,

Worked like a charm...I was logged in with an account that wasn't a 'Farm Administrator'. Once I did that saw 'Site Collection Features' and activated the importer. Works like a charm....thanks for this!

Vikas

February 15. 2010 13:37

Hi,

I've installed your solution with success.

When I try tu use it, I get the following error "Value does not fall within the expected range".

I try to import a csv file with 3 columns : 2 string columns and 1 date column.

Have you any idea of the problem ?

Thanks

Eli

eli

February 15. 2010 15:50

Hi,

Can you tell me how I have to set datetime field in my csv file to import them ?

Thanks

eli

February 18. 2010 19:19

Hi Aidan,

Thanks for the letting me know fact about primary keys. I knew about it though. I would like to ask you that had you uploaded project file ( solution ) for reference for new version. If not can you do so for guidance. Thanks for sharing such posts.

Regards,
PP

Preet Patel

March 3. 2010 14:38

Eli - the project is currently only set up to handle strings which is why you are getting this issue.

Aidan

Add comment

Name*
E-mail*
Website
Country Country flag

Comment
Preview

Notify me when new comments are added

About Aidan Garnish

Aidan is a SharePoint consultant focusing on maximising the business benefit of pointing and sharing. When not pointing or sharing he can be found suspended several feet off the sea attached to an unpredictable power source.

Email Aidan >>

Easily import data from CSV and SQL to a SharePoint list

Categories

Archives