How do i read an Excel file

[randall2nd]

Below is some code I got from tlhawkins on Jun 26, 2001. I need it to connect to an Excel file.
this is what I changed

DSNtemp = "DRIVER={Microsoft Excel Driver (*.xls)};";
DSNtemp = DSNtemp & "DBQ=d:\data\mydb.xls";

only it will not connect

[Microsoft][ODBC Excel Driver] External table is not in the expected format.

is the error i get.

What am i doing wrong?
What would my SELECT statement look like?
Is there a better way to get at the info in an Excel file?

<cfobject type=&quot;COM&quot;
          name=&quot;MyConn&quot;
          class=&quot;ADODB.Connection&quot;
          action=&quot;CREATE&quot;>

<CFSCRIPT>
DSNtemp = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;;
DSNtemp = DSNtemp & &quot;DBQ=d:\data\mydb.mdb&quot;; 
MyConn.Open(&quot;#DSNtemp#&quot;, &quot;&quot;, &quot;&quot;, -1);

SQL = &quot;SELECT * FROM MyTable&quot;;

MyRecordset = MyConn.Execute(SQL, 0, 8);

MyFields = MyRecordset.Fields;

/* Make a variable for recordcount */

RecordCount = 0;
    while(NOT MyRecordset.EOF){
        RecordCount = RecordCount + 1;   
        MyRecordset.MoveNext();          
    }        

Columns = &quot;&quot;;    
</CFSCRIPT>

<!--- Get the Column Name from the MyFields collections --->
<CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
    <CFSET Columns = ListAppend(Columns, this.Name)>
</CFLOOP>

<!--- Populate a newly made query with columns --->
<CFSET MyRecords = QueryNew(Columns)>

<!--- Add 'RecordCount' rows to hold the data --->
<CFSET QueryAddRow(MyRecords, RecordCount)>

<!--- Go to the first row of the recordset --->
<CFSET MyRecordset.MoveFirst()>

<!--- And populate the recordset... --->
<CFLOOP FROM=&quot;1&quot; TO=&quot;#RecordCount#&quot; INDEX=&quot;i&quot;>
    <CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
        <CFSET QuerySetCell(MyRecords, Trim(this.name), this.value, i)>
    </CFLOOP>
    <CFSET MyRecordset.MoveNext()>
</CFLOOP>

<!--- Ouput the data like this: --->
<CFOUTPUT QUERY=&quot;MyRecords&quot;>
    <TR>
    <CFLOOP LIST=&quot;#MyRecords.ColumnList#&quot; INDEX=&quot;this&quot;>
        <TD>#Evaluate(this)#</TD>
    </CFLOOP>        
    </TR>
</CFOUTPUT>
</TABLE>

<!--- Close the ODBC connection (like you do in ASP)--->
<CFSET MyConn.Close()>


<!--- 
I believe IQOF188 is the author of this code. 

This is coded to work for an Access database but I believe it will work for excell as well.  Just change the database driver.

 --->

Randall2nd

 

[randall2nd]

Well I finally figured it out. it can be done two ways
the original way was fine but my Excel version was 2.1[don't ask], i just needed to change how the table was refrenced.

SQL = "SELECT * FROM [MyTable$]";

a slightly more complicated way is below,the good thing about it that you can change the version of excel that you are trying to read:

<CFSCRIPT>
DSNtemp = &quot;provider=Microsoft.Jet.OLEDB.4.0;&quot;;
DSNtemp = DSNtemp & &quot;Data Source=d:\data\mydb.xls;&quot;;
DSNtemp = DSNtemp & &quot;Extended Properties=Excel 8.0;&quot;;
MyConn.Open(&quot;#DSNtemp#&quot;, &quot;&quot;, &quot;&quot;, -1);

SQL = &quot;SELECT * FROM [MyTable$]&quot;;

**Note:lower versions of excel do not support ODBC/ADODB concections Randall2nd