Tool to Import Data From Excel to Sql server

Question

0.00/5 (No votes)

See more:

I'm an Intern developing a tool to import data from excel with many sheets to sql server, i have been able to dynamically pass all the sheet names to combobox1 and sql server tables to combobox2. Further, combobox1.selecteditem(which is Excel Table) have passed all columns belonging to that selected table to Listview1 while combobox2.selecteditem have passed the sql table columns to listview4. How do i map the data of listview1.checkeditems(excel table columns) to the corresponding columns in listview4.checkeditems (sql table columns??=

And Option 2: I am also trying to import data directly from excel table (combobox1.selecteditem) to sql server table (combobox2.selecteditem)but it has foreign keys from many other tables. How to solve the foreign key constraint issue and what if some coulmn names are not the same from source to destination table???

Your kind and urgent response will serve my greatest interest.

I appreciates whatever effort put forth in helping me out.

Thanks

Posted 7-Aug-11 2:58am

Mcmish

Updated 7-Aug-11 6:08am

Kim Togo

v2

Add a Solution

Comments

Dr.Walt Fair, PE 7-Aug-11 12:54pm

Are you just trying to translate column names from Excel to the DB or is something more involved?

Mcmish 7-Aug-11 21:27pm

Hi Walt,

thanks for your kind interest in helping out. i'm trying to import the data rows that belong to each column in a column by column format since the column names in excel sometimes differs with the column names of sql server table.

So, when i checked d excel table column i will also checked d sql server table corresponding column in order to avoid mismatch of data, so, i want to insert column by column. Any idea???

Thanx

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Chepurin · Answer 1 · 2011-08-07T07:20:00

Solution 1

This link: Import data from a excel file into sql server through vb.net
and this one: Import from Excel to SQL Server can help you.

Posted 7-Aug-11 7:20am

Sergey Chepurin

Comments

Mcmish 8-Aug-11 4:59am

Thanx Sergey,

I really appreciates. I had tried similar methods already but its truncating the data cos of foreign keys constraint. And one of the methods will not be ideal as the column names differs at times and that will lead to either mismatch of data or truncating without inserting at all.

Any further assistance on how to solved the foreign keys issues to allow importing the selected table one after another would be highly appreciated.

Maciej Los 9-Aug-11 16:02pm

There is no mechanism which will provide auto-conversion. You should know the structure of data in ms excel file and ms sql server (tables). The only one way to achieve this is to compare data "column by column". If user choose column1 in ms excel sheet and column1 in ms sql database table, you should compare it names and types of data, before start import.

Mcmish 9-Aug-11 21:32pm

Hi Losmac,

thnx. I guess i have achieve that feat of having to compare the names, but could you in any way show me sample of the data type comparison method, so I can pick it up from there? The data types includes nvarchar, int, decimal.

Thanx in advance.

Maciej Los 11-Aug-11 14:20pm

And we have a problem, because comparison method does not exists! You need to write it. Tip: Try to convert data stored in table in ms excel file to expected format in ms sql database. If you expect help from me, i need to see an example: files, tables and code.

Mcmish · Answer 2 · 2011-08-11T17:32:00

Solution 3

Dear all,

Kindly Check the link below to help me out here.I'm running late in my project, please....

Question: Map Excel Column to SQL Column

Thanx alot

Posted 11-Aug-11 17:32pm

Mcmish