How to Import data from excel sheet within SQL Client into SQL Server

 Hi All,

   I need to  import data from excel sheet to SQL Database in SQL Server.I already done it in machine which SQL Server run.but I cant do it within SQL Client machine.while try to do it this error was occure

Server: Msg 7302, Level 16, State 1, Line 2
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.

Please tell me How I do it

Thanks,

Harsha

0
harshalasith
3/3/2009 8:15:28 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

3 Replies
1060 Views

Similar Articles

[PageSpeed] 44
Get it on Google Play
Get it on Apple App Store

Hi harsha

 Can you post your code

 


If this post helps you, please mark it as Answer.


Gnana Prakash S
Software Engineer
Coimbatore
http://www.sgnanaprakash.com
0
gnanam02
3/3/2009 10:26:19 AM

 Hi Gnana,

this is My code.

 

create table #MYTABLE(ROW_ID int identity,Name varchar(8),ID varchar(5))


declare

@service_provider varchar(300),
@path varchar(300),
@dbtype varchar(500),
@query varchar(300),
@datasource varchar(300)

set @service_provider='Microsoft.Jet.OLEDB.4.0'  
set @dbtype='Excel 8.0;DATABASE='
set @path='C:\Documents and Settings\HARSHA\My Documents\ToExportExcel\Book1.xls;HDR=yes'
set @query='Select Name,ID from [Personal$]'
set @datasource=@dbtype+@path


EXEC(
    '
    Insert Into #MYTABLE(Name,ID)
    SELECT Name,ID
    FROM
    OPENROWSET
    (
    '''+@service_provider+''',
    '''+@datasource+''',
    '''+@query+ ''') a')


select * from #MYTABLE
 

0
harshalasith
3/3/2009 11:22:22 AM

Hi harshalasith,

You can take a look at this article for importing data from excel sheet to SQL Server.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
3/5/2009 5:54:38 AM
Reply:

Similar Artilces:

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.---------------------------------------------------------------------...

Import data from excel sheet data to Sql Server 2008 database
 Hello,  I am doing an project in which i am going to get the data from the excel sheet and insert it into My SQL Server 2005 database.How can i do it,can any one help me out to get me out of this problem with an example. You could use SQL Server Integration Services (SSIS) to do this.  If you need to repeat the process remember that you can host an SSIS package as a SQL Agent job and run it repeatedly, and you can kick off the job from code. A walkthrough of this is here: http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-us...

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installedhttp://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

Import data from excel sheet to SQL Server
How do I import the data from the excel sheel to the database table that has different fields compared to that of the excel sheet programatically or as a SysAd?I never lose, some people are just better than me at winning. INSERT INTO [dbo].[products]  ( [Stock Number], [Registration Number] )SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\stock.xls','SELECT * FROM [Expoted_Stock$]')    When i run this query it is giving error Msg 7357, Level 16, State 2, Line 2Cannot process the object "SELECT * FROM [Expoted_Stock...

Importing data from excel sheets to SQL server 2000
HiI need to transfer data frm a bunch of excel sheets to SQL server using ASP.net. Can u plz help me in doing tht?Thanx!  Hi, You can create a DTS on SQL Server that has an XML file as input that stores this information into tables. In your ASP.Net application you can configure the excel as parameter, exceute the DTS and know in which step is.Please mark the most helpful reply/replies as "Answer". My Blog Hi Thanx for the reply but I don't hv the slightest idea hw to do thtSo if u can help me wid some code samples i would be really greatful.Thanx.  This link should...

Importing Data from Excel Sheets into SQL Server Database
Hi, Would like some help on how do I go about coverting an Excel File with columns of info into my SQL Server Database. The excel file will be uploaded from a user from my web application. I completely have no idea on where to start so any form of help is much appreciated thanks. bump bump Any help at all? Just to get me headed in the right direction....

Import data from Excel to sql server 2005 not working on server
I am trying to import data from an Excel file into SQL Server 2005 in a webform.  The code below works on my local machine, but on the Production server it throws an unspecified OLEDB error at OleDbConn.Open when attempting to open the connection.  I cannot figure out what is required on the server to make this work, as the error gives no details.  I do not have Excel installed on the webserver and do have it on my local, but don't know if that is the problem, because I get the same error when I change the connectionstring to open a CSV file.   Any help wo...

how do i insert data into sql server using data from textboxes and sql connection and sql command
anyone can give me a simple explaination and code.. Thanx in advance Take a look @ Sample Code: How to add...Sushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb There are a lot of tutorials out there, but I know they can be a bit confusing as they all approach things slightly differently. Basically a nice simple version would go like this:' *** First set up your connectionDim conConnection As New SqlConnection("server=servername;database=yourdatabase;uid=yourname;pwd=yourpassword;")' *** This line takes care of your sommand text and command connection in one ...

How to Data Transfer From Excel to Microsoft SQL Server Data base Using .Net.
Hai All, How to Data Transfer From Excel to Microsoft SQL Server Data base Using .Net. I have one Excel Sheet in that sheet 15 Fields (attributes) and 3000 records are there. In this 3000 records i want to store this (Excel) data into  5 related tables on Sql Server Database Using .Net.   How to store Excel Sheet data into Sql Server database. Please help me as soon as possible. Thank You Very Much...Have a Good Day... With RegardsSridhar...

Importing datas from Excel to Sql Server
Hi, I am developping in asp.net, c#. I am importing datas from an excel sheet and I am saving them in some tables in my Sql Server. This works for these value types : SqlServerType            .aspx pages            Statestring                            varchar            &n...

Import Excel data to SQL Server
Hi, I have a problem of importing excel data into the SQL database. When I import the database (finish all importing steps), it tell me "Fail to copy 1 table" and I go to view the error message say that "Error at Source for Row number 19. Errors encountered so fat in the task: 1. Data for source column 2 ('Notes') is too large for the specified buffer size." I try to remove that line record but still have many lines have the same problem. So, can I change the importing buffer size in SQL Server to make me import data becomes successful?? Thanks a lot. you're pr...

How to import data from Excel to SQL Server
 I have an excel sheet and a table in sql server 2005 with the same field as in excel sheet .Please let me know how do import all data from excel sheet to sql server 2005.there are some extra rows in the table.. the easiest way is to open your table with right click- "open table",select and copy all cells from  Excel ,on opened table click the star in last row.. and past.Other way is :Right click on Database--> tasks --> Import data... and follow the wizard Regards,Stefan Uzunov I would use DTS to do the import, there are lots of examples out there showi...

how to import data from excel to sql server 2005
hi all,         how to import data from excel to sql server 2005 using asp.net 2.0 and c#    AshokAshok Is this something that you going regularly. Do a data transformation, DTS from sql, into a table. and use it from there SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\1A.xls', 'SELECT * FROM [Sheet1$]')Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever. hi all~i have an other question here..can i apply the same ...

How To: Import Complex Excel Data into Sql Server
Hello All, Following is the part of the data we are getting from various schools in the region on monthly basis and we are following this data collection and feeding technique since 2001. This is a single region(abc) school from a worksheet of more than ten regions's within an excell workbook. Format is the same for all the worksheep within the workbook. Name of School Level Teachers     NURSERY IA I II III IV V TOTAL PRIMARY     M F Total M F T M  F T M  F T M&...

Web resources about - How to Import data from excel sheet within SQL Client into SQL Server - asp.net.object-datasource

List of countries by imports - Wikipedia, the free encyclopedia
This is a list of countries by imports , based on the World Trade Organization and The World Factbook . For comparison purposes, some non-sovereign ...

Perfect Audience Performance Reporting API Allows Facebook Advertisers To Import Data
Advertisers that use Facebook Exchange can now import data on their ads, campaigns, and conversions into their own customized reporting dashboards ...

Oakland Opens the Wallet for Cuban Import
Looks like Billy Beane really is sticking to that Window. After spending the offseason trading away two of the better young starting pitchers ...

How to Import Your Blog Into Facebook
Inside Facebook has recently written about simplifying how you use Facebook with other social media tools in order to make life a little easier. ...

Export Import Business Group
Here we are, people in the business of export and import from all over the world. I invite you people also to join for more info of the business ...

#1 Cheat for Ruzzle ~ find the best words with free auto game import! on the App Store on iTunes
Get #1 Cheat for Ruzzle ~ find the best words with free auto game import! on the App Store. See screenshots and ratings, and read customer reviews. ...

Secretary Kerry Delivers Remarks at the Export-Import Bank Annual Conference - Flickr - Photo Sharing ...
U.S. Secretary of State John Kerry delivers remarks at the Export-Import Bank Annual Conference in Washington, D.C., on April 24, 2014. [State ...

Christmases come at once for Canberra Cavalry imports at Percy households
When a Canberra Cavalry player is billeted by ground announcer Matt Percy and his family, the unofficial initiation is usually a big spoonful ...

Apple adds iPhones to Lightning to USB Camera Adapter compatibility list, photo import works under iOS ...
... reading at 9to5Mac . What do you think? Discuss "Apple adds iPhones to Lightning to USB Camera Adapter compatibility list, photo import works ...

Import brands dominate list of finalists for North American Car and Truck of the Year
Coverage of advertising spending, campaigns and agency changes in the automotive industry.

Resources last updated: 1/7/2016 2:02:16 AM