[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Importing data from Excel in Database SQL2005

Posted on 2006-11-27
10
Medium Priority
?
216 Views
Last Modified: 2010-04-16
Hi,

I am working in C# and making Windows Application.

I have an excel file to which I want to import in the database SQL 2005

How can i do this, also I do not want to make dsn, but i can make file DSN

Can anyone help me in this regard

Thanks in Advance
0
Comment
Question by:dinesh_bali
[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
  • 3
  • 2
  • 2
10 Comments
 
LVL 12

Expert Comment

by:AGBrown
ID: 18020919
Would it be possible to use Integration Services to do this in your situation? It won't be as easy to do it on-demand, but you could schedule the SQl agent to run the SSIS job every minute, and get that job to pick up any spreadsheets sitting in a certain directory? It really depends on whether this is a local application, or a web application, and how SQL server is setup (and which version you are running).

A.
0
 

Author Comment

by:dinesh_bali
ID: 18024023
Ok, If you have the solution with DSN or some other,

that if user click on the button then the filtered data goes into sql 2005

Thanks in Advance
0
 
LVL 12

Expert Comment

by:AGBrown
ID: 18024031
Is this a web application, or a windows application?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 18025478
You can import excel file into SqlServer using SqlServer Enterprize Manager try following:

Select Tools tab.
In that select Data Transformation Services.
In that select import Data
Then you follow the wizard.

Hope this helps.

Another approach I used some time ago : I read data from Excel to DataSet, just need appropriate connection string
Then changed connection and saved into database. Can't remember details exactly but it worked well and was quite easy.
0
 

Author Comment

by:dinesh_bali
ID: 18032439
I want to do using C# code.

My excel format and database format is not same.

So I need to filter the data from C# SQL Query and then insert the data in the database.

Can you please help me in this context
0
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 2000 total points
ID: 18033285
I believe it's not so hard.

See the link how to read data from Excel. Very simple and easy to follow.
You may also filter the data at this stage using SQL:

http://zamov.online.fr/EXHTML/CSharp/CSharp4.html

Once you have your data in the DataSet I believe it's pretty easy to write the data to database.
You may also want to create another DataSet and copy necessary data from the first one to the second one, etc.
Or e.g. create new tables in the second dataset and fill them with data from the first dataset, and save second dataset into database.
0
 
LVL 12

Expert Comment

by:AGBrown
ID: 18033657
It is certainly possible, but there's all sorts of inherent problems with this. For one, Excel is a user application, so it might flash up "dialog boxes" in the "user space" of the account running your code. If your code is a web application, this can freeze your web application. Secondly, Jet is pretty bad at recognising data types. If the first 5 rows of data in one column look like numbers, but there is a text string somewhere in that column, it will cause errors.

So it really comes back to what the end-use of this is; server (web or other) application, or a user application? Is the SQL server a local install, or a centrally managed server?

A
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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