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.

------------------------------------------------------------------------------------------------------------------------------------
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(
    '    
    SELECT Name,ID
    FROM
    OPENROWSET
    (
    '''+@service_provider+''',
    '''+@datasource+''',
    '''+@query+ ''') a')

------------------------------------------------------------------------------------------------------------------------------------

0
harshalasith
3/18/2009 6:26:23 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
947 Views

Similar Articles

[PageSpeed] 30

Hi

 I too have faced too many problems to impliment this, Finally i achived....

Check the following code... carefully ... it will help you!!!

 

This is a sample ....

 

//C# code//


 
1    if (FileUpload1.HasFile && FileUpload1.PostedFile.FileName.EndsWith(".xls"))
2                    {
3                        string path = Server.MapPath("Upload") + "\\" + FileUpload1.FileName;//saves temporaryly to this folder
4                        string filename = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\") + 1);
5                        FileUpload1.SaveAs(path);
6    
7    				    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
8                using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
9                        {
10                           OleDbDataAdapter command1 = new OleDbDataAdapter("Select * FROM [User List$]", connection);
11                           DataSet ds_tbl = new DataSet();
12                           try
13                           {
14                               command1.Fill(ds_tbl);
15   
16                           }
17                           catch (Exception ex)
18                           {
19                               lblError.Visible = true;
20                               lblError.Text = "Sorry, uploaded file data is invalid. Please upload valid data file.";
21                               if (ds_tbl.Tables.Count != 0)
22                               {
23                                   //some code
24                               }
25                           }
26                           System.Data.DataTable dt = new System.Data.DataTable();
27                           if (ds_tbl.Tables.Count > 0)
28                           {
29                               dt = ds_tbl.Tables[0];
30                               
31                               
32                               
33                                   foreach (DataRow dr in dt.Rows)
34                                   {   
35                                       //checking mandatory fields, if empty delete that row
36                                       if (dr[0].ToString().Trim() == "")
37                                       {
38                                           dr.Delete();
39                                       }
40                               
41   
42                                   }
43                                   dt.AcceptChanges();
44                                   if (dt.Rows.Count > 0)
45                                   {
46                                       
47                                       string Name = "";                   //00
48                                       string add = "";          //01
49                                       
50   									                DS_Global = new DataSet();
51   
52                                       
53                                       for (int row = 0; row < dt.Rows.Count; row++)
54                                       {
55                                           
56   										                #region Concatinate
57   
58                                           
59                                         _name = (dt.Rows[row][0].ToString().Trim() != "") ? dt.Rows[row][0].ToString().Trim() : _name;
60                                         Name = Name + _name + "!$@";
61   								
62   								                      _add = (dt.Rows[row][1].ToString().Trim() != "") ? dt.Rows[row][1].ToString().Trim() : _add;
63                                         Add = Add + _add + "!$@";
64                                               
65                                          
66   
67                                           #endregion Concatinate
68   
69                                           #region InsertIntoDatabase=0
70   										                      SqlCommand comm_insert = new SqlCommand("mySP_Test", con);
71   										                      comm_insert.CommandType = CommandType.StoredProcedure;
72   										                      comm_insert.Parameters.Add("@Name", SqlDbType.NVarChar, 4000).Value = Name.Trim();
73   										                      comm_insert.Parameters.Add("@Add", SqlDbType.NVarChar, 4000).Value = Add.Trim();
74   										                      con.Open();
75   
76   										                      try
77   										                      {
78   											                      DataSet ds = new DataSet();
79   											                      SqlDataAdapter da = new SqlDataAdapter(comm_insert);
80   											                      da.Fill(ds);
81                                                                 }
82   										                      catch (Exception ex)
83   										                      {
84   											                      //some code
85   										                      }
86   										                      finally
87   										                      {
88   											                      con.Close();
89   										                      }
90                                           #endregion InsertIntoDatabase=0
91   
92                                       }
93   
94   }
95   
96                                       

  

// SQL query //

 

 

 

 

CREATE procedure [dbo].[mySP_Test]

 

 

@Name
nvarchar(MAX),

@Add nvarchar(MAX)

 

as

 

 

 

--@Name

declare @separator1_position int

declare @array1_value nvarchar(MAX)

declare @Name1 nvarchar(MAX)

--@Add

declare @separator2_position int

declare @array2_value nvarchar(MAX)

declare @Add2 nvarchar(MAX)

 

 

 

while patindex('%' + '!$@' + '%' , @Name) <> 0 begin

 

 

-- @Name

select @separator1_position =patindex('%' + '!$@' + '%',@Name)

select @Name1=left(@Name,@separator1_position -1)

-- @Add

select @separator2_position =patindex('%' + '!$@' + '%',@Add)

select @Add2=left(@Add,@separator2_position -1)

 

INSERT INTO URTableNAme(Name,Add ) values(@Name1,@Add2)

 

 

 

select @Name = stuff(@Name, 1, @separator1_position, '') select @Add = stuff(@Add, 1, @separator2_position, '')

 

 

 

end

 

Thanks

 

 

 

 


Thanks and Regards
Avinash

avicool08@gmail.com
0
avicool08
3/18/2009 9:40:55 AM
Reply:

Similar Artilces:

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 2Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.Please tell me How I do itThanks,Harsha Hi harsha  Can you post your code  If this post helps you, please mark it as Answer.Gnana Prakash SSoftware EngineerCoimbatorehttp://www.sgnanaprakash.com  Hi Gnana,this is My...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

importing an excel spreadsheet into a sql database using sql server 2005 express
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?    Try this thread for many options.  Hope this helps. http://forums.asp.net/thread/1442470.aspxKind regards,Gift Peddie thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. FrankBollard:thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. I am glad I could help.Kind regards,Gift Peddie...

Sql server or sql client
Hi   I am using sql server 2005 on my machine. But I have no idea whether I am using sql server or client on my machine.   Is there any way to see that   Regards KaranRegardsKaran GuptaFor .net,Javascript and Sql server articles and interview question visithttp://guptakaran21.googlepages.com/main   karang:I am using sql server 2005 on my machine. But I have no idea whether I am using sql server or client on my machine. It has been a while since I used only the client tools, but if i recall correctly, in ADD/REMOVE programs it does say CLIENT TOOLS so check t...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

Excel to SQL and SQL to Excel
I wanted to know if there is a better way to do the following.  Whenever I query a SQL Server database, I connect using Access through the ODBC because I can write the query using the query builder and export the results to Excel very easily.  Also, when I import data from an Excel spreadsheet to a SQL Server, I import to Access first, then write an append query to get the data into SQL from Access. I would like to learn how to bypass Access in both directions. Yes totally .. you can directly export and import data from excel to sql server. To do that one option is to ...

How to connect to Remote SQL Server database using SQL Server 2005 Express
Hi,I've an account with a hosting service provider online for SQL Server database. I've downloaded SQL Server 2005 Express from ASP.Net. How can I use it to connect to my SQL Server Database which is sitting on remote server? The hosting provider gave me following things to connect to the remote database.Server NameDatabase NameUser NamePasswordRegards,A.K.R    Anyone? I don't want to ask the obvious, but is there not a Read Me file included with the download of SQL Express which would explain how to connect? Sorry I can't be more helpful, but I've only ever briefly ...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

SQL Attached mdf database files VS database imported into or created in SQL Server 2005
 Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases. AFAIK it is only for deployment conveniance, I have always imported the database directly into SQL Server and set up the connection string accordingly.Don't forget to click "Mark as Answer" on the...

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'WinFM SQL PFS'. Login fails.
HiASP pages were properly connecting to SQL DB until I restored from a backup DB.  As soon as I finished restoring from the backup DB, I am getting the following error message.   I did not make any changes on ASP side.  IIS Server and SQL 2K Server is running on a WIN2K Server Box.Please help.Thank you.HTTP 500.100 - Internal Server Error - ASP errorInternet Information ServicesTechnical Information (for support personnel)Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login...

SQL Server Database File vs SQL Server Database -VWD Express
VWD Express 2008, SQL Server Express 2005. I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE. I am getting started with .NET. Working through a VWD Express book tutori...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

SQL Express
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Cou...

Web resources about - How to import excel sheet into SQL Database in SQL Server from Remote SQL Client - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

ERISA and All Claim databases
... laws, on the books in 18 states, requiring self-insured employers to report data about the prices they pay to “all payer claims databases.” ...

Sanders’ campaign looses access to N.H. Democratic voter database - The Boston Globe Sanders’ campaign ...
... the presidential campaign of US Senator Bernie Sanders has been denied access to the New Hampshire Democratic Party’s valuable voter database ...


Under pressure, DNC restores Bernie Sanders campaign's access to their database - Videos - CBS News
A strange twist in the race for the democratic presidential nomination: the campaign of Vermont Senator Bernie Sanders has filed a complaint ...

Over 680TB of data exposed in MongoDB databases
There are at least 35,000 publicly accessible and insecure MongoDB databases on the Internet, and their number appears to be growing. Combined ...

Database error publishes data of 3 million Hello Kitty fans
Online researcher Chris Vickery uncovered a database this weekend containing the personal information of 3.3 million accounts associated with ...

MacKeeper Leak Highlights Danger of Misconfigured Databases
The Shodan port-scanning service finds at least 35,000 MongoDB databases accessible without a password.

OracleVoice: Oracle Challenges SAP On In-Memory Database Claims
Companies that run SAP’s applications should know that most of those applications can perform much better on Oracle Database than on any other ...

Resources last updated: 12/24/2015 4:33:15 AM