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 > http://www.microsoft.com/en-us/download/details.aspx?id=13255

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.

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

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

OLEDB

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

ODBC

.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 answer which discusses the differences.

Remove or comment out this line.

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

Update

Replace

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

with this

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

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

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

enter image description here

<%
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

enter image description here

edited May 23 '17 at 11:49

Community

1
1

answered Feb 03 '15 at 17:11

WorkSmarter

3,738
3
29
34

I tired that and still got an error "-2147217900 [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'" – MonkeyMagix Feb 04 '15 at 14:01
I have tried using CommandText as the article suggest I should and removed the Provider and tried all 3 of these statements as I don't know what syntax I should use. Just that on the old driver I had to select from sheet1 for it to work >>>> With objConnectionCSV .Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";""" '* errors here '.CommandText = "SELECT * FROM [" & objFile.FileName & "];" '.CommandText = "SELECT * FROM [" & objFile.FileName & "][Sheet1$];" .CommandText = "SELECT * FROM [Sheet1$];" Set objCSVRecs = .Execute End With – MonkeyMagix Feb 04 '15 at 16:23
I am getting "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Excel Driver] 'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." – MonkeyMagix Feb 04 '15 at 17:02
and now using this code, splitting the command object up to have an ADO connection string object as well which is pointed at it e.g >>> Set objCommandCSV = Server.CreateObject("ADODB.Command") Set objConnectionCSV = Server.CreateObject("ADODB.Connection") Set objCSVRecs = Server.CreateObject("ADODB.Recordset") With objConnectionCSV .Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strPath & ";""" End With With objCommandCSV .ActiveConnection = objConnectionCSV .CommandText = "SELECT * FROM [Sheet1$];" Set objCSVRecs = .Execute End With – MonkeyMagix Feb 04 '15 at 17:03
I was able to recreate the error message below when my strPath was invalid. "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Excel Driver] 'Sheet1$' is not a valid name...." Be sure to verify it is valid e.g. c:\somefolder\mydata.xlsx. – WorkSmarter Feb 05 '15 at 08:41
Do you know what it was? The test file I had been given by the boys, they didn't name the sheet with the data on Sheet1 so no wonder the SQL SELECT * FROM [Sheet1$] didnt' work DOH! Now it works fine but I have been told I need to also handle the upload of .txt or .CSV files that work in 2003 with the old connection string objConnectionCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "HDR=yes;" &_ "Dbq=" & strPath & ";" & _ "Extensions=asc,csv,tab,txt;" & _ "Persist Security Info=False" --- but it doesn't work on 2012 - do you the correct connection string to use for TXT or CSV? – MonkeyMagix Feb 05 '15 at 10:37
The best place to find information about connection strings is http://www.connectionstrings.com. Here is their example for text driver. Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\; Extensions=asc,csv,tab,txt; Textfile. The link to the example is here http://www.connectionstrings.com/microsoft-text-odbc-driver/standard/ – WorkSmarter Feb 05 '15 at 12:09
I tried that but I get the error > Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when I use this code >> With objConnectionCSV '* errors here .Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strPath & "; Extensions=asc,csv,tab,txt;" strSQL = "SELECT * FROM [" & objFile.FileName & "];" Set objCSVRecs = .Execute(strSQL) End With >>> it doesn't get as far as executing the SQL into the recordset object. I visited the page - do I need to install another driver for CSV/TXT? – MonkeyMagix Feb 05 '15 at 14:17
also I went to the links to download the driver > http://www.connectionstrings.com/microsoft-text-odbc-driver/info-and-download/ and it says "There is no 'Microsoft Text ODBC Driver' installation package to download" - there are some other drivers on the left e.g Microsoft Jet OLE DB 4.0 so I wonder if I need to install that then use that driver. We had to install a new driver to get the XLS upload to work but this doesn't seem to work with text files / CSV – MonkeyMagix Feb 05 '15 at 14:21
I performed the same test and came up with the same results. Have you given any thought to using something like this http://stackoverflow.com/questions/7695999/how-to-read-text-file-in-classic-asp? – WorkSmarter Feb 05 '15 at 18:52
I was hoping to avoid having to do my own file parsing script, especially in ASP Classic. The problem is that there are 4 columns, ID, ParentID, Category, OrderNo and Category can have commans in it e.g "Sales, Manager" or "HR, Recruitment" so I would have to handle that without being able to know that the words have quotes around them as the files they use don't so I would need to handle rows with odd number of commas and then treat those rows as ones with categories with commas inside so it's a little more complicated. If you have a script that does that then that would be great! – MonkeyMagix Feb 06 '15 at 13:13
This script will address your comma parsing needs. http://stackoverflow.com/questions/28374540/classic-asp-how-to-preserve-commas-while-parsing-a-csv-file/28374541#28374541 – WorkSmarter Feb 06 '15 at 20:51

ASP Classic Text Driver For Windows 2012

1 Answers1

Linked