Excel As DataSource

Please give me the steps to retrieve data from a .xls file..
Thanks.You should be able to add that in as an ODBC datasource then query as you normally would.
"Vidhya" <varadarv@.deshaw.com> wrote in message news:uALR6BRWEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Please give me the steps to retrieve data from a .xls file..
> Thanks.
>|||I am trying it out but an unhandled exception occurs and the IDE restarts
when i try to execute the query..
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:%23LTc1PTWEHA.584@.TK2MSFTNGP09.phx.gbl...
> You should be able to add that in as an ODBC datasource then query as you
normally would.
> "Vidhya" <varadarv@.deshaw.com> wrote in message
news:uALR6BRWEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > Please give me the steps to retrieve data from a .xls file..
> >
> > Thanks.
> >
> >
>|||In report designer:
From Data Link Properties dialog -> Provider tab, choose OLEDB provider for
ODBC drivers.
Go to the Connection tab and click on the "Use connection string" radio
button.
Click on "Build" button
Click on "New" button in Select Data Source dialog.
Choose Microsoft Excel Driver (*.xls) from the list and click Next.
Enter the full path of the Excel file.
Click Next.
Finish.
Click OK in the ODBC Microsoft Excel setup dialog
In Select Workbook dialog, choose the relevant file.
Click OK.
Choose the newly created .dsn file.
Click OK on the Select Data Source dialog.
Exit the Data Link Properties dialog.
You can now run queries against the Excel data source. You could so
something like:
SELECT * FROM [sheet1$]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vidhya" <varadarv@.deshaw.com> wrote in message
news:%23%23lMG1aWEHA.3012@.tk2msftngp13.phx.gbl...
> I am trying it out but an unhandled exception occurs and the IDE restarts
> when i try to execute the query..
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:%23LTc1PTWEHA.584@.TK2MSFTNGP09.phx.gbl...
> > You should be able to add that in as an ODBC datasource then query as
you
> normally would.
> >
> > "Vidhya" <varadarv@.deshaw.com> wrote in message
> news:uALR6BRWEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > > Please give me the steps to retrieve data from a .xls file..
> > >
> > > Thanks.
> > >
> > >
> >
> >
>|||I am sorry I am not very comfortable with this yet...Thanx a ton for the
response..
Now this works on my report designer. But my report server is on a different
machine. I created the dsn locally on that machine too... This is not only
for Excel..when I try connecting to Sybase too, it works on my designer but
does not on the server..Guess this must be some permissions problem...
What all should I be taking care of here..?
"Ravi Mumulla (Microsoft)" <ravimu@.microsoft.com> wrote in message
news:eMluyHbWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> In report designer:
> From Data Link Properties dialog -> Provider tab, choose OLEDB provider
for
> ODBC drivers.
> Go to the Connection tab and click on the "Use connection string" radio
> button.
> Click on "Build" button
> Click on "New" button in Select Data Source dialog.
> Choose Microsoft Excel Driver (*.xls) from the list and click Next.
> Enter the full path of the Excel file.
> Click Next.
> Finish.
> Click OK in the ODBC Microsoft Excel setup dialog
> In Select Workbook dialog, choose the relevant file.
> Click OK.
> Choose the newly created .dsn file.
> Click OK on the Select Data Source dialog.
> Exit the Data Link Properties dialog.
> You can now run queries against the Excel data source. You could so
> something like:
> SELECT * FROM [sheet1$]
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Vidhya" <varadarv@.deshaw.com> wrote in message
> news:%23%23lMG1aWEHA.3012@.tk2msftngp13.phx.gbl...
> > I am trying it out but an unhandled exception occurs and the IDE
restarts
> > when i try to execute the query..
> >
> > "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in
message
> > news:%23LTc1PTWEHA.584@.TK2MSFTNGP09.phx.gbl...
> > > You should be able to add that in as an ODBC datasource then query as
> you
> > normally would.
> > >
> > > "Vidhya" <varadarv@.deshaw.com> wrote in message
> > news:uALR6BRWEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > > > Please give me the steps to retrieve data from a .xls file..
> > > >
> > > > Thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Ravi Mumulla \(Microsoft\)" <ravimu@.microsoft.com> wrote ...
> From Data Link Properties dialog -> Provider tab, choose OLEDB provider for
> ODBC drivers.
Why would you recommend to use a depreciated component?!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mdacsdk/htm/mdac_deprecated_components.asp
You should've advised to use the OLE DB Provider for Jet:
From Data Link Properties dialog Provider tab, choose Microsoft Jet
4.0 OLE DB Provider.
Go to the Connection tab.
Type the name of the workbook into the 'Select or enter a database
name' box (or select it by clicking the 'ellipses' button, changing
the 'File of Type' dropdown to 'All Files (*.*)' and navigating the
workbook and clicking OK).
On the 'All' tab, select 'Extended Properties' in the list and click
'Edit value'.
Enter the value Excel 8.0 (Excel<space>8<dot><zero>).
On the Connection tab, click the 'Test Connection' button.
Exit the Data Link Properties dialog.
Jamie.
--|||This too works fine on my report designer..What all should I take care of to
make the report run when deployed if my report server is on a different
machine?
Thanks..
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0406240422.7b53652@.posting.google.com...
> "Ravi Mumulla \(Microsoft\)" <ravimu@.microsoft.com> wrote ...
> > From Data Link Properties dialog -> Provider tab, choose OLEDB provider
for
> > ODBC drivers.
> Why would you recommend to use a depreciated component?!
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mdacsdk/htm/mdac_deprecated_components.asp
> You should've advised to use the OLE DB Provider for Jet:
> From Data Link Properties dialog Provider tab, choose Microsoft Jet
> 4.0 OLE DB Provider.
> Go to the Connection tab.
> Type the name of the workbook into the 'Select or enter a database
> name' box (or select it by clicking the 'ellipses' button, changing
> the 'File of Type' dropdown to 'All Files (*.*)' and navigating the
> workbook and clicking OK).
> On the 'All' tab, select 'Extended Properties' in the list and click
> 'Edit value'.
> Enter the value Excel 8.0 (Excel<space>8<dot><zero>).
> On the Connection tab, click the 'Test Connection' button.
> Exit the Data Link Properties dialog.
> Jamie.
> --|||You will most likely need to change the connection string after you publish
the report to reference a location on the server.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newbie" <varadarv@.deshaw.com> wrote in message
news:uDnPrrgWEHA.808@.tk2msftngp13.phx.gbl...
> This too works fine on my report designer..What all should I take care of
> to
> make the report run when deployed if my report server is on a different
> machine?
> Thanks..
> "Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
> news:2ed66b75.0406240422.7b53652@.posting.google.com...
>> "Ravi Mumulla \(Microsoft\)" <ravimu@.microsoft.com> wrote ...
>> > From Data Link Properties dialog -> Provider tab, choose OLEDB provider
> for
>> > ODBC drivers.
>> Why would you recommend to use a depreciated component?!
>>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mdacsdk/htm/mdac_deprecated_components.asp
>> You should've advised to use the OLE DB Provider for Jet:
>> From Data Link Properties dialog Provider tab, choose Microsoft Jet
>> 4.0 OLE DB Provider.
>> Go to the Connection tab.
>> Type the name of the workbook into the 'Select or enter a database
>> name' box (or select it by clicking the 'ellipses' button, changing
>> the 'File of Type' dropdown to 'All Files (*.*)' and navigating the
>> workbook and clicking OK).
>> On the 'All' tab, select 'Extended Properties' in the list and click
>> 'Edit value'.
>> Enter the value Excel 8.0 (Excel<space>8<dot><zero>).
>> On the Connection tab, click the 'Test Connection' button.
>> Exit the Data Link Properties dialog.
>> Jamie.
>> --
>