HOME ABOUT
I AM HERE
  • Twitter icon
  • Facebook icon
  • Technorati icon
Bookmark and Share

Generic CSV or SQL data importer for SharePoint list

September 9, 2008 17:01 by Aidan

UPDATE (09/10/09) - NEW AND IMPROVED VERSION HERE

I finally got bored of rewriting the same console application to move data from a csv or a sql table into a SharePoint list! The result is a generic console application that is capable of importing either a CSV file or some SQL data from a stored procedure. The app uses the object model so it needs to be run on the SharePoint server farm. I may get round to rewriting it using web services at some point. The CSV import parses the file so that things like line breaks and commas within fields are handled nicely. Any blank fields in the CSV do need to be filled using find and replace on blank space otherwise data ends up in the wrong columns.

To import a CSV the first row of the of the CSV file is used to define which columns the data will import to. Eg. A CSV that has one column with a header of Title will import the data into the Title column of the specified list. Modify the App.Config file to include the URL of your site collection, the list name, the path to the CSV and whether or not you want to delete all items in the list before importing.

To import SQL data create a stored procedure that returns the data as the names of the site columns

Eg. Select Name AS Title from tblPerson - will return one column of data that will import into the Title column of a list. Modify the App.Config to include the name of the stored procedure and the SQL connection string and run the app.

You can download the project from the following link:

AG.GenericSharePointListImporter.zip (41.72 kb)

This application is supplied as is and offers no guarantees feel free to use as you wish all I ask is that if you make any improvements then you share them in the comments or by email.

Technorati Profile


Tags: ,
Categories: ASP.Net | MOSS 2007 | SQL Server
Actions: E-mail | Permalink | Comments (18) | Comment RSSRSS comment feed

Comments

November 3. 2008 16:42

Hello,

your project seems to be exactly what I was looking for since....so long...
I've downloaded it, and when I try to run the compiled exe , I get an error message "Value does not fall within the expected range."
I use a SQL 2005 database, I created the stored proc, I modified the app.config ,built with Visual C# 2008 express, but no way,...I always get this error.
As I'm not a specialist about C#, could you please explain me how I can run it ??
Thanks a lot in advance,
Florence

Florence

December 15. 2008 18:07

Is there any Generic CSV or SQL data importer for wordpress, I try to find out but I could not find anything :(

Busby SEO Test

December 19. 2008 07:24

is it possible to run the list checking realtime on demend rather than importing the data into list.

the code u had a link is external application. are we supposed to generate the lists from outside. i would prefer to create them when use clicks on the list.

Developer

December 30. 2008 14:43

@Florence - You will need to alter the App.Config file to point at the list you want to import into. Currently the application is looking for a list that does not exist on your SharePoint site and throwing the error you are seeing.

@gi621 - You could wrap this application as part of a feature that creates a SharePoint list. You could run the code as part of a feature receiver to import your CSV after the list is created.

Aidan

January 15. 2009 10:34

Hi Aidan,

thanks a lot for that great tool "generic csv import". now it is working very fine. one question:
i want to import a csv into a list which is under a subweb like "http://portal/applications/. i only can import it to the root. (http://portal) what can i do to import it into my subweb???

Thanks a lot for your help!
Michael

Michael

August 18. 2009 17:04

Thank you for this great tool.
I am sure I am using the correct List Name but I recieve an error "Value does not fall within the expected range."

My CSV file has 4 columns and these are the 1st rows of the file(Date, Time, Number and Description)

In SharePoint list, I created 4 columns with the same name as the CSV has.

I know SharePoint Has a required column called "Title", in my CSV, I created a Title column but still recieve the error. What might be the cause?

OCY

August 18. 2009 18:55

Thank you for this great tool. I know I use the correct List name in App.Config but I still recieve the error "Value does not fall within the expected range." In my CSV file, I have 4 columns(Date, Time, Number and Description). I created these columns in the SharePoint list as well. What might be the issue?
Thanks again

OCY

August 18. 2009 18:59

OCY - have you formatted your date and time correctly before trying to copy them into your columns? You should also check the local settings for your site as this can cause problems for date validaton. You should also check that your list name in app.config is spelt correctly.
Hope that helps.

Aidan

August 19. 2009 07:44

Hi Aidan,
I checked the formats and the local settings. To simplify the process, I created a new CSV with one column called "Title" and one value under the column in the 2nd row. SharePoint Lists have by default required column called "Title". List name is spelt correctly. I still recieve the error "Value does not fall within the expected range." I tried several different CSV files(comma delimited, MS-DOS, etc...) Do you mind if I send you an email with screenshots, maybe you will see if there is something wrong? Thank you very much.

OCY

August 19. 2009 17:30

Hi,
The date and the time are formatted correctly. To simplify the process, I created a new CSV with one column called "Title". Sharepoint Lists have a required column called "Title" as well. In my CSV, I provided one value under the column "Title". Used the tool and still get the error "Value does not fall within the expected range." The list name in app.config is spelt properly. Do you mind if I send you an email with screenshots so you will see if there is something wrong? Thank you
OCY

OCY

September 1. 2009 13:28

This tool works only if the list is on the root. How can I get it work with the sub-sites?
Thanks

Thomas

September 9. 2009 12:47

OCY - if you send me screen shots and a copy of your app.config I will take a look.

Aidan

September 9. 2009 13:45

Hello Aidan,
i run in trouble when i try to write data from a *.csv file to a list on a subweb.
writing data to a list on the rootweb works perfect.
please help me on this matter.
thanks in advance

Patrick

September 9. 2009 13:50

The current version does not allow writing to a subweb but the code could be altered to do this quite easily.

Aidan

September 16. 2009 13:50

Hello Aidan,

is it possible to change the seperator for the csv file ... currently it's the comma, but i would like to change it to a semicolon ...

br

Patrick

September 16. 2009 14:18

A CSV file is by definition comma seperated so the short answer is no. You could change the code quite easily to seperate each row using a semicolon if you wanted though.

Aidan

September 29. 2009 09:17

Pingback from it.rss24h.com

Automatically Update Sharepoint List with the content of CSV | it.rss24h.com

it.rss24h.com

November 27. 2009 13:40


All:
If you use CSV from Excel, the default separator is ";" semicolon. This will produce  "Value does not fall within the expected range." Error.


Change the Regular expression on code to this:

Regex re = new Regex("((?<field>[^\";\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(;|(?<rowbreak>\\r\\n|\\n|$))");

Thank you for your code Aidan.

Luis

Add comment




  Country flag


  • Comment
  • Preview
Loading