| Author |
Topic |
|
YogeshDesai
Posting Yak Master
136 Posts |
 Posted - 2007-06-12 : 03:25:49
|
| Hi Guys,I am trying to export data from database to excel 2007 file on my machine.I have downloaded the provider for office 2007 and I have XP with sp2 and SQL server 2005 with SP2.I havn't installed office 2007 on my machine,but I have a excel file which is created in office 2007.To get acess of read and write i have downloaded office compatibilty pack too,So I can read and write into the file.Now I am creating one SSIS package to export data into excel file.But I m not able to coause I am getting some errors like"test connection failed because of an error in intializing provider. Invalid UDl file""Test connection is failed because of an error in intializing provider.Not a valid file name".Please help or suggest how to export data from database to 2007 excel file.Yogesh V. Desai. | SQLDBA| |
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2007-06-13 : 02:13:38
|
| Hi guys The problem has been resolved now.I just make some changes in SSIS connection properties and it's working fine now.Yogesh V. Desai. | SQLDBA| |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-06-13 : 12:17:18
|
| YogeshCan you please let us know what changes you made...might save somebody another post..regardsPareshRegardsParesh MotiwalaBoston, USA |
|
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2007-06-27 : 03:42:53
|
| Yes Sure, I would like to share, When you will connect to office 2007 using SSIS choose the 1. Drag the OLE DB Source on the window2. Double click OleDB source task3. Select CreateNew Connection4. In the connection Manager Option Choose the provider for excel 2007 the provider is " Microsoft 12.0 Access Database Engine OLeDB Provider"5. Click on the Data links Button.6 It will open Datalink Properties7 Click on advanced tab8 Click on the proper access permissions9 Click on all tab10 Select data Source and click on the button edit value11 enter the path of your .xlsx or .xls file say okay12 then select Extended properties and click on edit the value13 Enter the following value "Excel 12.0;HDR=YES"14 Click ok and exit from all the windowsThen Click opnYogesh V. Desai. | SQLDBA| |
|
|
|
ikrava
Starting Member
1 Post |
Posted - 2008-06-17 : 09:03:44
|
| I’m trying to export data from SQL Server 2005 into MS Excel 2007. I find the instruction on this forum:1. Drag the OLE DB Source on the window2. Double click OleDB source task3. Select CreateNew Connection4. In the connection Manager Option Choose the provider for excel 2007 the provider is " Microsoft 12.0 Access Database Engine OLeDB Provider"5. Click on the Data links Button.6 It will open Datalink Properties7 Click on advanced tab8 Click on the proper access permissions9 Click on all tab10 Select data Source and click on the button edit value11 enter the path of your .xlsx or .xls file say okay12 then select Extended properties and click on edit the value13 Enter the following value "Excel 12.0;HDR=YES"14 Click ok and exit from all the windowsFile xlsx was created, but I couldn’t read it. I had the message from MS Excel : MS Excel can not open the file because the format is not valid.After that I change file name on xls. Excel opened this file successfully.Can anybody help me, please. Why I can’t open MS Excel 2007 file? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-22 : 03:26:28
|
quote:
Originally posted by ikrava
I’m trying to export data from SQL Server 2005 into MS Excel 2007. I find the instruction on this forum:1. Drag the OLE DB Source on the window2. Double click OleDB source task3. Select CreateNew Connection4. In the connection Manager Option Choose the provider for excel 2007 the provider is " Microsoft 12.0 Access Database Engine OLeDB Provider"5. Click on the Data links Button.6 It will open Datalink Properties7 Click on advanced tab8 Click on the proper access permissions9 Click on all tab10 Select data Source and click on the button edit value11 enter the path of your .xlsx or .xls file say okay12 then select Extended properties and click on edit the value13 Enter the following value "Excel 12.0;HDR=YES"14 Click ok and exit from all the windowsFile xlsx was created, but I couldn’t read it. I had the message from MS Excel : MS Excel can not open the file because the format is not valid.After that I change file name on xls. Excel opened this file successfully.Can anybody help me, please. Why I can’t open MS Excel 2007 file?
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3110742&SiteID=17 |
|
|
|
Starlet_GT
Yak Posting Veteran
81 Posts |
Posted - 2009-07-23 : 06:25:51
|
| I have established connection sucessfully with the above help ... now I am having problem in dropping excel 2007 sheet. I execute query to [drop table `Sheet1`] and it executed sucessfully but when i create table `sheet1` then it gives me error that table already exists ... its mean it delete rows only not table ... whereas in excel 2003 i do the same and it gives me no error |
|
|
|
mano_j85
Starting Member
1 Post |
Posted - 2010-11-17 : 00:19:42
|
| Hi Guys,I want to export sql report to excel 2007 using query.Can anyone help me...?I tried a new linked server and used the Microsoft office 12.0 ACE OLE DB Provider as connection string but i get the error saying "Microsoft office 12.0 ACE OLE DB Provider" is not registered.What should i do...? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-17 : 04:29:57
|
| You need to install ACE OLE DB providerMadhivananFailing to plan is Planning to fail |
|
|
|
Jinasoso
Starting Member
5 Posts |
|
|
|
Export data to excel 2007