Reading an Excel File with ASP and storing in database

[tvdij]

Hello all,

I've done some research on reading excel files with ASP but none seem to work. Does anybody know how to do this?

Basically what I'd like to do is have a user upload an .xls file and that file would be auto-read and certain columns of the excel spreadsheet would be read and used to update the database.

[Spudhead]

It's possible, but different connection strings work differently with different versions of Excel. This works for me however, on... just about every version onwards from Excel '97:



function readDatafile()

sFilePath = "C:\data\myfile.xls"
sDataDir = "C:\data\"
sSheetName = "Sheet1"

sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&sFilePath&";DefaultDir="&sDataDir&";"
sFileSQL = "SELECT * FROM [" & sSheetName & "$]"

dim oFileCN, oFileRS, aSourceData
set oFileCN = server.createobject("ADODB.Connection")
oFileCN.Open sFileConnectionString
set oFileRS = oFileCN.Execute(sFileSQL)
if not oFileRS.EOF then
aSourceData = oFileRS.getRows()
end if
oFileRS.Close
set oFileRS = nothing
oFileCN.Close
set oFileCN = nothing

readDatafile = aSourceData

end function

[tvdij]

This may be a dumb question but what exactly does that function return?

Do I treat "aSourceData" as I would a table once the function returns? That is I can use commands like "aSouceData.EOF," "aSourceData("ColumnName")" etc?

Thanks.

[Spudhead]

Not quite - it returns an array. Have a look at Recordset.getRows() (http://www.devguru.com/technologies/ado/8678.asp). The only thing to remember about it is that the columns are placed in the first dimension of the array and the rows are placed in the second dimension, ie: the array is on its side.

For example, say you have a spreadsheet with columns for name, date of birth and favourite brand of toothpaste, you could get at it like this:


aFileContents = readDatafile()
if isArray(aFileContents) then
for i=lBound(aFileContents,2) to uBound(aFileContents,2) 'Note that to loop through the data rows, you need to go through the arrays second dimension, not the first.
sName = aFileContents(0,i)
sDoB = aFileContents(1,i)
sToothpaste = aFileContents(2,i)
next
end if



NB: there's no reason you can't adjust that function so that it returns a recordset object if you find that easier to deal with. It's just that recordsets are expensive objects for a server to maintain and use, and your application will perform much better if you get the data out of the recordset, into something less expensive like an array, and kill off the recordset as soon as you can.

[fractalvibes]

You can also do this: (this is VB)
'excel objects
Dim oExcelApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet

'instantiate excel objects
Set oExcelApp = New Excel.Application
Set oWorkbook = oExcelApp.Workbooks.Open(dataPath)
Set oWorksheet = oExcelApp.Worksheets("MyWorksheet")

For ix = minRow To rowCount
xFunctionGroup = Trim(oWorksheet.Cells.Item(ix, 1).Value)
xProcess = Trim(oWorksheet.Cells.Item(ix, 2).Value)
xPolicyNumber = Trim(oWorksheet.Cells.Item(ix, 4).Value)
blah blah blah
Next

'dispose of excel objects

oExcelApp.Quit
Set oWorksheet = Nothing
Set oWorkbook = Nothing
Set oExcelApp = Nothing


fv

[Cap]

Hi there.

I have a comparable trouble, in fact i need to open some excel files via ASP.

The problem is that i can't know the sheet name, so i can't use the codesSheetName = "Sheet1"
...
sFileSQL = "SELECT * FROM [" & sSheetName & "$]"

Any idea ?

Thanks by advance.

[Spudhead]

Google Is Your Friend.

http://www.codeguru.com/forum/archive/index.php/t-182983.html
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257819

;)

[Cap]

Thanks, I will try !