How to insert a column in Excel by using OleDB

Hi all, I need to add a new column in an excel file, as I jus know the
query to add a new row in excel.
string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
" values ('"+name+"', '"+id+"')";
Anyone know the query to add a new column? Thankz...
Message posted via http://www.webservertalk.comYou might try the ALTER TABLE command. Not sure if it will work for a
linked Excel file, but it's standard SQL DDL to modify your table
structure - including adding a column.
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
> --
> Message posted via http://www.webservertalk.com|||Ya...but I duno the syntax of the ALTER TABLE....I have tried
string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
but it doesn't seems to work...can you please give an example of the Alter
Table SQL? Thankx...
Message posted via http://www.webservertalk.com|||> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
In SQL Server, you should omit COLUMN. I don't know if this will work with
Excel:
string strCom = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:14c9b951bfd14ffc93557f7c6d03b877@.SQ
webservertalk.com...
> Ya...but I duno the syntax of the ALTER TABLE....I have tried
> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
> but it doesn't seems to work...can you please give an example of the
> Alter
> Table SQL? Thankx...
> --
> Message posted via http://www.webservertalk.com|||I'm using C# with OleDB, here is my code
========================================
===============================
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
fileName + ";Extended Properties=Excel 8.0" ; ;
OleDbConnection myConn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
myConn.Open();
myCommand.Fill (myDataSet, "[Sheet1$]");
myConn.Close();
========================================
===============================
I got an error of "Invalid operation"...anyway, it works fine with
UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
ALTER TABLE syntax?
Message posted via http://www.webservertalk.com|||As I said, I'm not even sure ALTER TABLE is supported when accessing XLS
files via OLEDB. That was just something to try based on standard SQL DDL.
Here's a link that has a tip, although they say it's a little complex:
http://www.eggheadcafe.com/ng/micro...490.asp

"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||> anything wrong with the ALTER TABLE syntax?
This syntax is valid for Microsoft SQL Server. I don't know what syntax Jet
expects or if it is even possible to add a column to an existing sheet using
the Jet OleDb provider
You might try posting your question to the OleDb forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
>
are you forced to use "oledb"?
object automation is very flexible way of interfacing excel:
set oxls=createobject("excel.application")
set owbk=oxls.workbooks.open("mytable.xls")
set owsht=owbk.activesheet
owsht.activecell.entirecolumn.insert
...
with such object you may use any construct supported by excel macro.|||Thanks for the suggestion, so how could I know wherether the ALTER TABLE is
supported or not?
Message posted via http://www.webservertalk.com|||Ops...I just saw the ODBC and JDBC...may I know where is the OleDB forum ?
Thankz.
Message posted via http://www.webservertalk.com

How to import several excel files using one dts package

It's very simple import, without any data modifications; from several
excel files to one table.
I tried the wisard and it gives me selection for only one file.
I am not used to create DTS packages and schedule jobs, so I would need
some help.

Thank you
InnaHi Inna,

Perhaps this article will be of help:
http://www.devx.com/getHelpOn/10MinuteSolution/18088

The article at the link above shows how to import Excel data into SQL
Server from an indeterminate number of Excel workbooks.

If the # of Excel files you have is small, you can do the following in
the DTS Designer:

1) Drag 1 Excel connection object per file into the DTS "canvas" (the
main area of the DTS Designer) and define the connections as
appropriate

2) Drag a connection object for your SQL Server and define it as
appropriate. One thing to note is that you should set the DB to connect
to the same as the DB you want to import the data into

3) Define a "Transform Data Task" (see your Tasks menu in the DTS
Designer) between each Excel connection and the SQL Server connection.

4) A line will appear between each Excel connection and the SQL Server
connection. Right click on each line and select "Properties" from the
menu that appears. Alter the properties of the task (e.g. column
mappings) as appropriate.

5) Execute the task

Hope that helps a bit.

How to import MS Excel data into SQL Server 2005 Express Ed.

I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table?

The easy way?

Open your excel spreadsheet, hit control-a (Select All), control-c (Copy).

Open Management Studio, right click the table you want to "import" to, and select "Open Table". Then hit control-v (Paste).

So long as the table has the same number of columns as your spreadsheet, you're done.

|||

Thans for this solution! :) I can use that!

I also want to know what kind of other options can be used.

how to import Excel spreedsheet to SQL Sever 2005

is there any easiest way to do it? thanks!

There are several possibilities.

Here is a resource that will guide you through them so that you can select the one that best fits your need: (The focus of the resource is to move data out to Excel, but the process is virtually the same to move data in from Excel.)

Export data to Excel
http://www.mssqltips.com/tip.asp?tip1202

|||

Have you tried that at express version.

Or is it only me .

If it works does anybody know good link for a noob.

How to import Excel files

I have data in excel files I want to import it to existing SQL Server database. I can use SQL Server built in import tool but before Importing, the data needs to be validated. What is the best way to do this?
Thanksimport it into a temp table, validate it, import to main table.

How to import excel data to tempdb

I am using SQL 2000. I would like to import an excel file to a tempdb
table (using # or ## methods). Can someone help me with it? I
apprecite it.Create a Linked server to the Excel spreadsheat. You'll find examples in
Books Online. See sp_addlinkedserver and sp_addlinkedsrvlogin in SQL Server
Books Online.
After configuring the linked server, I'd simply create the table in tempdb
and do an insert into the table, using the "INSERT INTO MyTable (<Column
list> ) SELECT <Column list> FROM LinkedServer...TableName" syntax.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"fl" <fzl20@.yahoo.com> wrote in message news:40EAF34B.555AE2DC@.yahoo.com...
> I am using SQL 2000. I would like to import an excel file to a tempdb
> table (using # or ## methods). Can someone help me with it? I
> apprecite it.
>

How to import excel data to tempdb

I am using SQL 2000. I would like to import an excel file to a tempdb
table (using # or ## methods). Can someone help me with it? I
apprecite it.Create a Linked server to the Excel spreadsheat. You'll find examples in
Books Online. See sp_addlinkedserver and sp_addlinkedsrvlogin in SQL Server
Books Online.
After configuring the linked server, I'd simply create the table in tempdb
and do an insert into the table, using the "INSERT INTO MyTable (<Column
list>) SELECT <Column list> FROM LinkedServer...TableName" syntax.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"fl" <fzl20@.yahoo.com> wrote in message news:40EAF34B.555AE2DC@.yahoo.com...
> I am using SQL 2000. I would like to import an excel file to a tempdb
> table (using # or ## methods). Can someone help me with it? I
> apprecite it.
>

How to import excel data to tempdb

I am using SQL 2000. I would like to import an excel file to a tempdb
table (using # or ## methods). Can someone help me with it? I
apprecite it.
Create a Linked server to the Excel spreadsheat. You'll find examples in
Books Online. See sp_addlinkedserver and sp_addlinkedsrvlogin in SQL Server
Books Online.
After configuring the linked server, I'd simply create the table in tempdb
and do an insert into the table, using the "INSERT INTO MyTable (<Column
list>) SELECT <Column list> FROM LinkedServer...TableName" syntax.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"fl" <fzl20@.yahoo.com> wrote in message news:40EAF34B.555AE2DC@.yahoo.com...
> I am using SQL 2000. I would like to import an excel file to a tempdb
> table (using # or ## methods). Can someone help me with it? I
> apprecite it.
>

How to import data in sql server 2000 from an excel(.xls) file

i want ot import data from excel .xls file to sql server 2000

into an existing table.
should i use some stored procedures or elseYou can use DTS in SQL Server (Right click on the database in Enterprise Manager, then Import) or even attach to the Excel file in Access and then use the upsizing wizard.|||Thnx fo rthe reply i tried this one its fine but i want ot do something more than that
i have a .xls file at my pc and i have to updat ethe database online
for this i know i have to write a web application first
where i put the path of the .xls file but what next how should i write the query which will get the data from .xls file into sql server
please reply asap
regards
softpioneer|||i need to get the xl file in the vb code .Also tell me how to get the hyper link's actual path
i need to get the path of the hyper links too
pls hel asap
regards
softpioneer|||Hi,
Have you got any solution for importing excel data to sql server using vb.net?|||I have seen access imported into SQL via C# and I bet the principles are the same.
Try this link.
http://www.eggheadcafe.com/forums/ForumPost.asp?ID=24776&INTID=6

How to import Data from Excel to SQL Server Express

Hi,
please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .

Your query is not clear.. you wish to copy data from Excel to SQL Server Express or You wish to read Excel data from ASP.Net application:

If you want to know, how to read Excel data in ASP.Net Application, visit the follocing web link:

http://weblogs.asp.net/scottgu/archive/2006/05/29/Reading_2F00_Writing-Excel-Spreadsheets-with-ADO.NET.aspx

|||

Hi,

You may try this SQL fonction OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

INSERT INTO MyTable

SELECT Column1, Column2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=C:\MyTable.xls',
 'SELECT * FROM [Sheet1$]')

The first row of your Excel file should coutains the colums names.

 Hope this answer your question. 

Dominic 

|||

hi, exactly i would like to copy two columns of data from Excel file instead of two columns of data in sql express,thank u

How to Import data from Excel into MS SQL in .Net?

Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.

I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work.

- Most simple and direct way I've tried is creating a oledb connection to the Excel file in the following codes...

Function TestUploadData1(ByVal vFile As String) As String
Dim Oleda As System.Data.OleDb.OleDbDataAdapter
Dim Olecn As System.Data.OleDb.OleDbConnection
Dim dt1 As DataTable

Olecn = New System.Data.OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & vFile & ";" & _ "Extended Properties=Excel 8.0;HDR=Yes")
Olecn.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.ExecuteNonQuery()

Olecn.Close()
End Function

But from the above codes I kept getting the error "Could not find installable ISAM." at the line Olecn.Open(). I've tried to set the registry to C:\Windows\System32\msexcl40.dll but to no avail...

- I've tried another method using the OpenRowSet in the following codes
Function TestUploadData2(ByVal vFile As String) As String
Dim cn As SqlConnection
Dim cm As SqlCommand
Dim strSQL As String cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")

cn.Open()

strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
cm = New SqlCommand(strSQL, cn)
cm.ExecuteNonQuery()

cn.Close()
End Function

For the above codes, I kept getting the error "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." at the line cm.ExecuteNonQuery() and I've tried to disable the Ad Hoc settings in the registry and tried to create a link server but to no avail as well...

- I've tried DTS but I'd need to DTS package to run whenever the users upload a file. Is there an easy way that I can run the designated DTS package to run through my .Net web application?

Any help would be deeply appreciated.
Thanks & Regards,
Keith Chang

Here is a very simple article which you can extend to your needs.

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

There are some solutions available for this problem. You already tried one. Have a look at this post also.

http://www.dotnetspider.com/qa/Question32454.aspx

Sometimes, even the connections string can be a problem (yours looks fine though)

|||

Here is a small exerpt from one of my projects:

If FileUpload1.HasFileThen

Dim filenameAsString

Dim cmdAs OleDbCommandDim daAsNew OleDbDataAdapter

filename = System.IO.Path.GetTempFileName

FileUpload1.SaveAs(filename)

Dim connAsNew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename &";Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")

' Find Sheet Names

Dim SheetsAs Generic.List(OfString)

Sheets = GetSheets(conn)

' Find Header Row

cmd =New OleDbCommand("SELECT * FROM [" & Sheets(0).ToString &"$A:A]", conn)

Dim dshAsNew DataSet

da =New OleDbDataAdapter(cmd)

da.Fill(dsh)

And the GetSheets function:

Function GetSheets(ByVal connAs OleDbConnection)As Generic.List(OfString)

Dim SheetsAsNew Generic.List(OfString)

Dim dtAs DataTable

If conn.State = ConnectionState.ClosedThen

conn.Open()

EndIf

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,NewObject() {Nothing,Nothing,Nothing,"TABLE"})

' Sheets end in $, but may have quotes around the name, this gets rid of named ranges from the list

Dim dvAsNew DataView(dt,"TABLE_NAME LIKE '%$' OR TABLE_NAME LIKE '%$'''","TABLE_NAME", DataViewRowState.Unchanged)

ForEach drAs DataRowIn dv.ToTable.Rows

If dr("TABLE_NAME").ToString.StartsWith("'")AndAlso dr("TABLE_NAME").ToString.EndsWith("'")Then

'Remove beginning quote, and ending dollar sign quote

Sheets.Add(dr("TABLE_NAME").ToString.Substring(1, Len(dr("TABLE_NAME").ToString) - 3))

Else

'Remove ending dollar sign

Sheets.Add(dr("TABLE_NAME").ToString.Substring(0, Len(dr("TABLE_NAME").ToString) - 1))

EndIf

Next

Return Sheets

EndFunction

|||

Thanks for replying Motley... But I believe your scripts were to extract the data from excel into a datatable. Well I can do that, no problem... Problem is how do I put this data into my MS SQL server?! Is there any way that I can just upload this dataset or datatable straight into the designated table in the MS SQL server?

I'm using .Net 1.1 so I can't use the SQLBulkCopy which is for 2.0 only... but still thanks bullpit.

|||

Once the data is in a dataset, you can then use a sqldataadapter or just iterate through the rows and issue the insert commands.

|||I have thought of that... but problem is that I've around 56 columns and around 600-700 records which will take quite some time to import. And I have around 15 files that need to go through this process... It'd take half a day just to import all these files...|||

Using sqldataadapter in batch mode will be the fastest way to import the data.

|||

Thanks for all help. But I've finally found my own solution to it. I used the DTS and the following code to execute the DTS package prepared to do the transfer in asp.net which did not take more than 2 seconds. Just that I need to open the excel file and adjust the formatting to assure correct importing which takes much longer time around 30 seconds.

'Update respective table (add reference to Microsoft DTSPackage Object Library)
vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

Hope this is useful to whoever facing the same problem as me.

|||

Hi KeithChang,

I am getting same problem as you mentioned on the first post message. I tried the OPENROWSET AND OPENDATASORUCE Command in .Net 1.1. But its not working.

Please tell me, how to implement this above code .

vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

This is helpful for me

Thank you

Sadheesh

How to import data from Excel file to SDF database

Hi Guys,

Can anyone tell me how can I achieve the file import of XLS file and read the data inside to insert into the respective table in my SDF database in the mobile devices?

Thanks.

Regards,

Jenson

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

|||

Ilya Tumanov wrote:

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

Hi IIya,

Thanks for replying to my question, I'm still have some doubts. First of all, how would the parser like, what is the processes need to be taken care of? And I have a form that allow users to upload the Excel file, do I check whether the file has been finished uploaded, and then run the parser to parse all the data out into a text file, from that file insert into SQLCE? Please pardon me for my dumb questions, as I have no prior experience dealing with SQLCE in this way, I only did simple storing and retrieving of data on SQLCE.

Thanks.

Regards,

Jenson

|||anyone could help me with this?|||Jenson, you have to explain a bit more about your requirements for us to help you. Do you want to import Excel Mobile files that reside on the mobile device via an app on the mobile device or ?|||

Hi ErikEJ,

I have found out that I can't do that =p

So I give up on that and have a workaround, now I have problem exporting data from SDF to Excel file. I will make a new thread for this, hope you and the rest would be able to help me out, very urgent.

Thanks.

Regards,

Jenson

How to import data from Excel file to SDF database

Hi Guys,

Can anyone tell me how can I achieve the file import of XLS file and read the data inside to insert into the respective table in my SDF database in the mobile devices?

Thanks.

Regards,

Jenson

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

|||

Ilya Tumanov wrote:

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

Hi IIya,

Thanks for replying to my question, I'm still have some doubts. First of all, how would the parser like, what is the processes need to be taken care of? And I have a form that allow users to upload the Excel file, do I check whether the file has been finished uploaded, and then run the parser to parse all the data out into a text file, from that file insert into SQLCE? Please pardon me for my dumb questions, as I have no prior experience dealing with SQLCE in this way, I only did simple storing and retrieving of data on SQLCE.

Thanks.

Regards,

Jenson

|||anyone could help me with this?|||Jenson, you have to explain a bit more about your requirements for us to help you. Do you want to import Excel Mobile files that reside on the mobile device via an app on the mobile device or ?|||

Hi ErikEJ,

I have found out that I can't do that =p

So I give up on that and have a workaround, now I have problem exporting data from SDF to Excel file. I will make a new thread for this, hope you and the rest would be able to help me out, very urgent.

Thanks.

Regards,

Jenson

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
Ben
Ben
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
BenBen
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

how to import an excel table into sql server express?

Hi,
i have a table in an excel file. How can i import it into sql server
express?
Thanks
BenBen
1) Linked Servers
2) OPENDATASOURCE command
INSERT INTO tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ben" <b@.bn> wrote in message
news:%23hK40fbvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i have a table in an excel file. How can i import it into sql server
> express?
> Thanks
> Ben
>
>

How to Import a Excel Datas to SQL Server Database

How to Import a Excel Datas to SQL Server Database

Open Enterprise Manager and right click on the database name into which you want to import the data.

Go to All Tasks > Import Data > Click Next on the DTS Import/Export Screen. On the Choose a Data Source screen select Microsoft Excel 97-2000 in the Data Source dropdown.

Browse to the excel file (browse button beside the File Name text box) click next and Select the destination (the database in which you want to import data) click next on the next dialog keep 'Copy table(s) and view(s) from the source database' > click next select the worksheets that you want to import click next > On the next screen keep Run Immediately checked click Next and then Finish

How to hide the print button on the toolbar?

I want to force users to export my report into PDF or Excel then print from
those applications. So I want to hide the print button on the toolbar. Does
anybody know how to hide the print button on the toolbar?
Thanks a lot.Set the RS property EnableClientPrinting. The only way to set this property
is by making a SOAP request. You will then need to restart IIS to ensure
that RS picks up the new property.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"BF" <BF@.discussions.microsoft.com> wrote in message
news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
>I want to force users to export my report into PDF or Excel then print from
> those applications. So I want to hide the print button on the toolbar.
> Does
> anybody know how to hide the print button on the toolbar?
> Thanks a lot.|||Hi Daniel,
Thanks for the reply. But can you give me some detail information about how
to set the RS property EnableClientPrinting through SOAP request?
Thanks.
"Daniel Reib (MSFT)" wrote:
> Set the RS property EnableClientPrinting. The only way to set this property
> is by making a SOAP request. You will then need to restart IIS to ensure
> that RS picks up the new property.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BF" <BF@.discussions.microsoft.com> wrote in message
> news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
> >I want to force users to export my report into PDF or Excel then print from
> > those applications. So I want to hide the print button on the toolbar.
> > Does
> > anybody know how to hide the print button on the toolbar?
> >
> > Thanks a lot.
>
>|||Please read the following KB article:
http://support.microsoft.com/kb/897121/
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BF" <BF@.discussions.microsoft.com> wrote in message
news:E4CF5F41-E688-46BD-A8D1-C0D6BDE1C188@.microsoft.com...
> Hi Daniel,
> Thanks for the reply. But can you give me some detail information about
> how
> to set the RS property EnableClientPrinting through SOAP request?
> Thanks.
>
> "Daniel Reib (MSFT)" wrote:
>> Set the RS property EnableClientPrinting. The only way to set this
>> property
>> is by making a SOAP request. You will then need to restart IIS to ensure
>> that RS picks up the new property.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "BF" <BF@.discussions.microsoft.com> wrote in message
>> news:1EE8F83A-365C-4279-8D82-ED612459DAF2@.microsoft.com...
>> >I want to force users to export my report into PDF or Excel then print
>> >from
>> > those applications. So I want to hide the print button on the toolbar.
>> > Does
>> > anybody know how to hide the print button on the toolbar?
>> >
>> > Thanks a lot.
>>