Reading data from Excel

ColinM · Post by **ColinM** » Wed Oct 01, 2008 9:38 am

Hi,

I have an Excel spreadsheet with one column of data, each cell in this column containing a string of text. I would like to open this spreadsheet as a data source, and read each cell in the single column as a record.

I have tried creating both OleDB and ODBC connections, as follows:

OleDB: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Reports\Terms.xls;Extended Properties="Excel 8.0;HDR=No;IMEX=1";

ODBC: Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Reports\Terms.xls;DefaultDir=C:\Reports;

... but neither method returns any data when I attach a data source to it (using the SQL string "SELECT * FROM [Sheet1$]").

Attempting to display the data in a data band results in a blank page, and "View Data" returns no results.

Any help in this would be greatly appreciated.

Many thanks,
Colin

Brendan · Post by **Brendan** » Wed Oct 01, 2008 9:50 am

The OleDb connection looks fine.

Is the Excel sheet you are trying to query actually called 'Sheet1' ?

For example, If I wanted to query the BalanceSheet I would Add a new 'Datasource based on an OleDbConnection'
and inside the query textbox type 'SELECT * FROM [BalanceSheet$]'

ColinM · Post by **ColinM** » Wed Oct 01, 2008 9:54 am

Yes, certainly is. Strange one isn't it?

Post by **Edward** » Wed Oct 01, 2008 9:59 am

Hello.

Could you get the data through OLE Db Connection in Visual Studio?

If yes, please send that excel file to support[at]stimulsoft.com for analysis.

Thank you.

ColinM · Post by **ColinM** » Wed Oct 01, 2008 10:03 am

Ah, just changed the SQL to "SELECT * FROM [Sheet1$]" (upper case "S" on "Sheet1"), and it worked ...

ColinM · Post by **ColinM** » Wed Oct 01, 2008 10:38 am

Actually, I think editing the SQL just caused a refresh, so one step forward in that I can now "View Data", but it will still not display in a data band (still blank) ...

Post by **Edward** » Wed Oct 01, 2008 11:21 am

Please see the report template and example of the xls file in the attachment.

Thank you.

ColinM · Post by **ColinM** » Thu Oct 02, 2008 4:36 am

Thanks Edward. That works fine. I can now preview the Excel data within the designer when displayed in a data band on a single page. However, it seems that the issue is tied to the VB.NET program used as a wrapper by my client (and written by them) to launch the report and display it in a preview window. When the report is run via this wrapper, it is blank ... I don't have access to the program, but if I run the report via my own VB.NET wrapper it is fine. I need to investigate further, but it may be that this is not actually a Stimulsoft issue at all.

Many thanks,
Colin

anshulmehta · Post by **anshulmehta** » Tue May 19, 2020 7:18 am

Hi

With 64 bit machine even if we change the connection string to
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Anshul\Desktop\ResultData.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

It still does not fetch anything.

SELECT * FROM [Sheet1$]

It is giving "keyword not supported" error.

Any idea to resolve this ?

Post by **Lech Kulikowski** » Tue May 19, 2020 1:57 pm

Hello,

Please check that designer is also started as x64 application.

Thank you.