Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Reading Excel Files in Classic ASP

Posted by karuta em outubro 25, 2015

Recently I had the need to source data from Excel files in the old Classic ASP platform. There are some good resources online which can help you with this, but I thought I’d log my little experience here which may hopefully expedite the process for someone else someday🙂

In my experience most people use VB script in their ASP environment,. the past few years I have grown to prefer using JScript. I’ll provide my testing in both.

A Little Environment Preface

In my examples I will have a file called unlocodes.xlsx placed in the directory c:\temp\
The content of the Excel file looks like this:

ASP (VB)

Here is a barebones ASP sample connecting to the Excel file.

<%
ExcelFile = c:\temp\unlocodes.xlsx
SQL = SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]
Set ExcelConnection = Server.createobject(ADODB.Connection)
ExcelConnection.Open Provider=Microsoft.ACE.OLEDB.12.0;Data Source= & ExcelFile & ;Extended Properties=“”Excel 12.0 Xml;HDR=YES;IMEX=1“”;
SET RS = Server.CreateObject(ADODB.Recordset)
RS.Open SQL, ExcelConnection
Response.Write <table border=“”1“”><thead><tr>
FOR EACH Column IN RS.Fields
Response.Write <th> & Column.Name & </th>
NEXT
Response.Write </tr></thead><tbody>
IF NOT RS.EOF THEN
WHILE NOT RS.eof
Response.Write <tr>
FOR EACH Field IN RS.Fields
Response.Write <td> & Field.value & </td>
NEXT
Response.Write </tr>
RS.movenext
WEND
END IF
Response.Write </tbody></table>
RS.close
ExcelConnection.Close
%>
view rawexcel-vb.asp hosted with ❤ by GitHub

This resulted in:

ASP (JScript)

Here is a JScript sample connecting to the Excel file. In my current environment I never use serverside JScript to render HTML, rather only to serve in a JSON based API format. I trimmed down the scaffolding into the bare necessities: an Excel interface and a JSON polyfill that works in ASP JScript.

<%@ Language=JScript %>
<!–#include file=”excel.asp” –><%/* https://gist.githubusercontent.com/patcullen/096a79ca8000b367537e/raw/7a806439bdf388335f2403ba89d8f51f039fb6a8/excel.asp */%>
<!–#include file=”json.asp” –><%/* https://raw.githubusercontent.com/douglascrockford/JSONjs/master/json2.js */%>
<%
excelFile = c:/temp/unlocodes.xlsx
sql = SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]
excel.open(excelFile).query(sql, {}, function(codes) {
Response.write(
JSON.stringify(codes.asArray())
);
excel.close();
});
%>
view rawexcel-js.asp hosted with ❤ by GitHub

This resulted in:

Some Extra Notes

I crossed paths with two errors, both of which were resolved by simply choosing the correct connection string.
This error:

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

and this error:

Microsoft JET Database Engine error '80004005'

External table is not in the expected format.

The ConnectionStrings.com website is a great resource for finding a connection string compatible with your installed version of Excel. I found that on my machine with Excel 2010 this connection string worked:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Whereas on our production server we have 2013 installed:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

If you continue to have problems finding the correct driver, or it complaining it’s not installed, then be sure to download and install the Microsoft Access Database Engine 2010 Redistributable. This includes the latest ACE drivers which come in 32 and 64 bit flavors. For posterity you may want to try install the 64bit version in command line using the follwoing syntax:

AccessDatabaseEngine_X64.exe /passive

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

 
%d blogueiros gostam disto: