?
Solved

Transfer data from Excel to MS SQL Server database

Posted on 2011-10-06
3
Medium Priority
?
313 Views
Last Modified: 2012-05-12
Hi,
I'm trying to find on the web any good example for moving data from excel to SQL Server..
For this purpose I allready use SQL BulkCopy but what I'm interested in how to validate data types between source (excel worksheet) and destination table (on SQL Server database)...
Also, i'm get stuck on how to provide end users to manually mapp columns between 2 datasources using sqlBulcColumnMappings...
Link, code or some constructive advice would be very much appreciated...
Thanks in advance
0
Comment
Question by:alsam
  • 2
3 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36923170
0
 
LVL 1

Author Comment

by:alsam
ID: 36923305
Thanks, but I allready have seen this article before...anyway thanks....
Regarding this part
 ' The column order in the source doesn't match the order
                ' in the destination, so ColumnMappings must be defined.
                bulkCopy.ColumnMappings.Add("ProductID", "ProdID")
                bulkCopy.ColumnMappings.Add("Name", "ProdName")
                bulkCopy.ColumnMappings.Add("ProductNumber", "ProdNum")

Do you maybe have any advice how to make this "user frendly"...I dont want to fix those columns in code but I want to let end user to make this mapping as he/she wants to... Something similar like in Import/Export Wizard in SQL Server...

Also, do you have any suggesstion about data type validation between 2 sources (excel vs. sql server db table)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 36926754
To allow user to do the mapping, you can design a form with may be a grid with two columns each populated as a combobox with column names from either side. Then you can loop through this grid and add the mappings like

For i As Integer = 0 to dgv.Rows.Count - 1
     bulkCopy.ColumnMappings.Add(dgv.Rows(i).Cells(0), dgv.Rows(i).Cells(1))
Next
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

850 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