Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Batch import of data into SQL server via Sharepoint 2007

Posted on 2010-08-19
2
Medium Priority
?
477 Views
Last Modified: 2012-05-10
I am trying to determine the best technologies/methods for the following.

Here are the scenario points:

We have end users who need to "batch" submit invoicing data into our SQL Server 2008 AP tables.
 
This user data exists in Excel files.  (i.e. we can make them provide it in Excel via a provided template)

These Excel spreadsheets sometimes have tens of thousands of rows, way to much to enter manually.  

The user would "submit" this data via a Sharepoint 2007 portal.

When the user submits this batch of data, we need to do several levels of fairly complex validation before it can be accepted into our AP tables:

Is the data the correct data type
Is it within valid ranges etc.
The "valid ranges" validation parameters are stored in our SQL Server 2008 database as they are subject to change. Example: they may be based on weather conditions on the day of service, for that specific location etc., so the validation parameters cannot be included in the Excel template.

If any row of data fail validation, the user then needs to do one of three things, delete that data, change their data or provide an explanation of the difference. So, these validations need to allow an interactive interface.

We have considered a file upload, SSIS import process, but that seems a bit cumbersome. Even though we have a template, it is very easy for the user to modify it, or change it, etc, killing the SSIS process.  SSIS does not always provide an error message that the end user could understand.

We have considered Excel services, but Excel data connections do not allow you to update SQL from the Excel sheet.

We are looking at the use of SharePoint lists (can you do copy paste into them from Excel?).

We are open to any of the above, however, would like to hear how you do it. Any suggestions or lessons learned here?

The best answer gets the points!  :>)
0
Comment
Question by:TriadX1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Expert Comment

by:MohammedU
ID: 33537141
I don't know it can be done using sharepoint but you can use OPENROWSET to read the contents of your Excel from SQL before inserting/updating data.
Or you have to to write VB script to read the excel and write to SQL.
Check the following thread...
http://bytes.com/topic/sql-server/answers/486783-updating-sql-server-table-using-excel
0
 
LVL 1

Accepted Solution

by:
TriadX1 earned 0 total points
ID: 33649462
We ended up allowing users to enter data (or copy and paste from an excel template) into a dynamically created SharePoint list.  The list has the first level of validation such as data types etc.

This is then dumped into SQL using SSIS and the SharePoint list adapter. We can then process the data in standard SQL where we do the complex validations.  If there are issues, it details them and sends a detailed list back to the user to correct.  If everything is ok, it sends them on to an approval process where they make there final "thumbs up" of the data.  This process work well...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question