SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  03:56:51  Show Profile  Reply with Quote
reinstalling the MDAC package will reslove this issue.can you tell me what exactly went wrong?
what i am trying to do is i want to import the excel sheet data into the sql server.
so i use the openrowset method.earlier i was getting syntax error and later i am getting the above said error. i check about the error in the site, everyone suggested to reinstall the OLEDB Driver tp reslove it. will it works after reinstalling.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  04:09:29  Show Profile  Reply with Quote
if i download the MDAC Package, its saying that it contains
Overview
Microsoft Data Access Components (MDAC) 2.8 SP1 contains core Data Access components such as the Microsoft SQL Serverâ„¢ OLE DB provider and ODBC driver. This redistributable installer for the MDAC 2.8 SP1 release installs the same Data Access components as Microsoft Windows XP SP2.

This release does not include Microsoft Jet, the Microsoft Jet OLE DB Provider or ODBC driver, the Desktop Database ODBC Drivers, or the Visual FoxPro ODBC Driver.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  05:22:59  Show Profile  Reply with Quote
i am getting an error when i try to download the MDAC. its saying that the connection failed. is there any other way to download it??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/01/2006 :  08:05:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now that is not an Excel problem, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/01/2006 :  08:13:42  Show Profile  Reply with Quote
when i click the link its trying to download an .exe file . thats not possible to download?

what does that mean not an excel problem?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/01/2006 :  10:50:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
As an alternative, try using Linked Server. Read about it in sql server help file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/02/2006 :  02:04:15  Show Profile  Reply with Quote
madhi, can you send me the link of sql server help file
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/02/2006 :  02:09:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
For SQL Server 2000
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

For SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

For SQL Server 7
http://www.microsoft.com/technet/prodtechnol/sql/70/downloads/books.mspx


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/02/2006 02:12:11
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/05/2006 :  07:35:15  Show Profile  Reply with Quote
hi madhi,

i have gone through the linked server.can you tell me how can apply linked server in this scenerio? using linked server, can i join multiple tables?

shybi
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/08/2006 :  02:14:00  Show Profile  Reply with Quote
hi all,

how can i write a sql query to export data from excel sheet to sql server(which is installed on a different machine), here i am not using the local sql server.so how can i write the query ????

INSERT INTO UserDetail
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Database=F:Employee.xsl;Excel 8.0;HDR=YES',
'SELECT [FirstName], [LastName]
FROM [Employee1$]') Rowset_1

how can i modify the sql statement so to include the path for the sql server on a different machine.

Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/08/2006 :  05:07:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
you must specify a relative path to the folder that your server can access and has read/write rights on it
instead of c:\myfile.xls use
\\OtherServerName\PublicFolder\myfile.xls



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/08/2006 :  05:31:32  Show Profile  Reply with Quote
if my excel sheet is on C:\Employee.xsl, i need to replace it with \\otherServerName\PublicFolder\Employee.xsl? what should i write instead of otherservername? is it the servername where the excelsheet is located? what about the publicfolder?what should i write instead of it? i made the excel sheet read/write.how can i specify relative path to the folder?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/08/2006 :  05:38:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The path to you file must always be relative to the server where you run your code.

If the file is on the server where you run your code, use 'C:\SomeLocation\SomeXL.xls'.
If the file is not on the server where you run your code, use '\\SomeOtherMachine\SomeShare\SomeXL.xls'.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/08/2006 05:38:37
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/08/2006 :  06:01:33  Show Profile  Reply with Quote
my application is running on 110.100.24.170 and i am accesing the sqlserver which is on 110.100.24.172.my excel sheet is on C:\Employee.xsl on 170. so how can i write the path ?

As you said, SomeOtherMachine here is 110.100.24.172 and
what is SomeShare means ?

my sqlstatement looks like this:
INSERT INTO UserDetail
SELECT FirstName, LastName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Database=C:\Employee.xsl;Excel 8.0;',
'SELECT [FirstName], [LastName]
FROM [Employee1$]') Rowset_1

its showing error:[OLE/DB provider returned message: Could Not find installable ISAM ]

shybi
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/08/2006 :  08:26:21  Show Profile  Reply with Quote
Is it possible to insert values into different tables using openrowset? say like

INSERT INTO UserDetail,LocationDetail,CompanyDetail,GroupDetail,DeptDetail
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Database=C:\Employee.xsl;Excel 8.0;',
'SELECT * FROM [Employee1$]') Rowset_1

here, UserDetail,LocationDetail,CompanyDetail,GroupDetail,DeptDetail, are different tables. i want to insert excelsheet values into different table.is it possible to use openrowset????

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/08/2006 :  13:18:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You need to use theree different queries for each table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/09/2006 :  01:15:56  Show Profile  Reply with Quote
i have written different queries for each table like

string sql = "Insert into UserDetail Select UserID,FirstName,LastName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT UserID,FirstName,LastName FROM [Employee1$]')";

string sql1 = "Insert into LocationDetail Select Location FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT Location FROM [Employee1$]')";

string sql2 = "Insert into CompanyDetail Select Company FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT Company FROM [Employee1$]')";

string sql3 = "Insert into GroupDetail Select Group FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT Group FROM [Employee1$]')";

string sql4 = "Insert into DeptDetail Select Dept FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT Dept FROM [Employee1$]')";

when i try to execute its showing the error

OLE DB error trace [OLE/DB 'provider Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The Provider didnot give any information about the error.
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/09/2006 :  02:35:18  Show Profile  Reply with Quote
when i try to execute sql1,sql2,sql3,sql4. its working fine. i have aproblem with sql.
the problem i am facing is, i want to add only 3 columns from the excel sheet and the remaining 4 colums, i want to get the data from the other 4 sql statement executed.the remaining 4 colums is the Foreign key to the other 4 sql statement. so how can i write the query for sql statement.

string sql = "Insert into UserDetail Select UserID,FirstName,LastName,DeptID,LocationID,GroupID,CompanyID FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\Employee.xls;HDR=YES','SELECT UserID,FirstName,LastName FROM [Employee1$]' AND SELECT DeptID from DeptDetail AND etc etc)";

can i use the AND statement here? or what should i do to fetch the remaining 4 values and put it in UserDetail table?

shybi
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/09/2006 :  06:23:32  Show Profile  Reply with Quote
i have 5 tables and i want to insert the values into another table? whats the syntax for insert statement?

Insert into tablename(col1,col2,col3,....) select col1,col2,col3, from table1,table2,table3,table4,table5.

while i am using the above sql statement. the rows are getting duplicated ?
can anyone tell me the syntax for inserting value into one table from selecting from another 5 tables?



Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/09/2006 :  07:23:01  Show Profile  Reply with Quote
i have 5 table

table 1 = UserDetails
columns = UserID,FirstName,LastName

table 2 = LocationDetail
columns = LocationID(PK),Location

table 3 = CompanyDetail
columns = CompanyID(PK),Company

table 4 = GroupDetail
columns = GroupID(PK),Groups

table 5 = DeptDetail
columns = DeptID(PK), Dept

now what i have to do is i want to insert the values from these 5 tables into another table named UserDetail with columns

UserID,FirstName,LastName,DeptID(FK),LocationID(FK),CompanyID(FK),GroupID(FK).

how can i select the values from 5 tables and insert into UserDetail Table.??



Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000