24227

ASP Classic Text Driver For Windows 2012

Question:

We have an old ASP Classic website which has been running on Windows 2003 and needs to be moved to 2012.

Most of the site works apart from a section where we need to be able to upload CSV or Excel documents full of categories to the server.

I know no Microsoft Driver existed at the time of Windows 2008 which is why we have had to leave the site on 2003 for so long but we are trying to solve this issue now either by -finding the correct MS Text/CSV/XLS driver for Windows 2012 -buying a component to do the job for us

After searching I found this MS KB article > <a href="http://www.microsoft.com/en-us/download/details.aspx?id=13255" rel="nofollow">http://www.microsoft.com/en-us/download/details.aspx?id=13255</a>

Which says it should do the job however when I upload Excel with the following code which I have got from the KB article

Set objConnectionCSV = Server.CreateObject("ADODB.Connection") Set objCSVRecs = Server.CreateObject("ADODB.Recordset") '* Get path and extension of uploaded file - earlier on using ASPUpload strPath = objFile.Path strExt = lcase(Right(strPath,Len(strPath)-InStrRev(strPath,"."))) '* try with new driver code With objConnectionCSV .Provider = "Microsoft.ACE.OLEDB.12.0" '* it errors on this line! .Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";""" '* old code which I don't get to which required data to be on sheet 1 On Error Resume Next Set objCSVRecs = .Execute("[Sheet1$]") If Err.number <> 0 Then strErrMessage = "Error opening file: " & Err.number & " " & Err.Description & ";" Err.Clear End If End With

I get this error on the line

.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";"""

Microsoft Access Database Engine error '80004005'

Could not find installable ISAM.

Is this the right text driver?

Does something else need to be done e.g IIS settings or different code?

We have restarted IIS and played around with permissions and it's running in 64 bit mode.

Does anyone have a solution to this problem or know of a workaround e.g a component I could use/buy as I don't want to have to start writing a file parser in ASP classic myself at this point in time.

Even if I can only handle CSV files and not XLS,XLSX etc then that would be good enough but I just need to know the code to use, the correct connection string and anything to install or set in IIS.

Thanks for your help in advance.

Answer1:

It looks like OLEDB and ODBC are being used at the same time.

<strong>OLEDB</strong>

.Provider = "Microsoft.ACE.OLEDB.12.0"

<strong>ODBC</strong>

.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";"""

Since these data source APIs are entirely different and accomplish the same task. Here is a StackOverflow's <a href="https://stackoverflow.com/questions/271504/oledb-v-s-odbc" rel="nofollow">answer</a> which discusses the differences.

Remove or comment out this line.

.Provider = "Microsoft.ACE.OLEDB.12.0"

<strong>Update</strong>

Replace

Set objCSVRecs = .Execute("[Sheet1$]")

with this

Set objCSVRecs = .Execute("SELECT * FROM [Sheet1$]")

The code below was setup on a Windows 2012 R2 VM.

<strong>Given:</strong> An Excel spreadsheet with the fields id, LastName, FirstName was created for this demo.

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/M47D0.png" data-original="https://i.stack.imgur.com/M47D0.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<% Set objConnectionCSV = Server.CreateObject("ADODB.Connection") Set objCSVRecs = Server.CreateObject("ADODB.Recordset") 'Important: Be sure your strPath variable contains a value. For testing purposes, I hard coded a value. strPath = objFile.Path strExt = ".xlsx" 'Opening tags for asp page response.write "<html><body>" With objConnectionCSV .Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";""" On Error Resume Next Set objCSVRecs = .Execute("select * from [Sheet1$]") Do While Not objCSVRecs.EOF response.write "Data row: " & objCSVRecs("id").Value & " Name = " & objCSVRecs("LastName").Value & ", " & objCSVRecs("FirstName").Value & "<br/>" objCSVRecs.MoveNext Loop If Err.number <> 0 Then strErrMessage = "Error opening file: " & Err.number & " " & Err.Description & ";" Err.Clear End If End With 'End tags for asp page response.write "</body></html>" %>

The Results

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/v6KMo.png" data-original="https://i.stack.imgur.com/v6KMo.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Recommend

  • Fastest way to count folder files in .NET 4.0
  • Create a webpart page error during debbugging mode
  • Copy the two most recent files to new dir
  • Fetch Image From Sqllite Database in iphone
  • How to call a powershell script from a C code
  • VBA - select the first file from a specific folder and reply all
  • Saving a custom export from an Access 2007 script
  • How to start java program (from .jar) elevated only using VBScript
  • From MS Excel VBA, create a Standard Module in MS Access
  • Stop macro if column is blank excel vba
  • VBA run-time error ‘91’: Object variable or With block variable not set
  • How to aggregate/compile multiple Excel spreasheets (.csv) into separate sheets in one workbook?
  • HTA (Html Application) VBScript Reading Textfile line and colouring that line only
  • Import semicolon separated CSV file using VBA
  • Execute SQL Server stored procedure through VBA which does not return rows
  • Access VBA recordset string comparison not working with wildcard
  • “object not found” and “unexpected symbol” errors when timing R code with system.time()
  • Use text from record to paste into an Access form controlbox
  • Configuring an Access report to use a SQL Server stored procedure as its record source
  • How to list all the bucket types in riak?
  • How to modify search result page given by Solr?
  • JAR file: Could not find main class
  • Valums Ajax file Upload handle the up. file?
  • How To Delete All Words After X Characters
  • Removing event listeners on automatically created multiple elements
  • Save website uploads in a subdomain
  • VB.NET 2012 Property Set on Property Get
  • Excel VBA URLDownloadToFile Error for https Ressource
  • How to know which Linq statement produced the SQL on hand during runtime?
  • Easiest way to get current unix timestamp via XSL
  • How to get current document uri in XSLT?
  • Abort upload large uploads after reading headers
  • Checking free space on FTP server
  • script to move all files from one location to another location
  • ILMerge & Keep Assembly Name
  • Symfony2: How to get request parameter
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • coudnt use logback because of log4j