Create a Recordset from Excel Data?

[RadarBob]

Is it possible to create a recordset from Excel? Excel can save a spreadsheet in many different formats.

[Roy Sinclair]

You can create a DSN to an Excel file and open it and read the sheet using SQL. I did this once a few years ago but that page is now long gone so I can't give you anything more than encouragement that it's possible.

[allida77]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj01/html/asp0193.asp

[whammy]

I will second Roy's post - I know it's possible as I've seen MANY references to it while programming, but I've never had the need to query an Excel file; usually this kind of stuff is imported into SQL Server for "safekeeping", if you may.

[grungebit]

HERE'S A CODE SAMPLE TO CREATE A LIBRARY THAT CAN IMPORT RECORDSET TO EXCEL:

library rsToExcel;

uses
SysUtils,
Classes,
StrUtils,
ExcelXP,
OleServer,
Variants,
Windows;

{$R *.res}

var
xlApp : TExcelApplication;

procedure RecordsetToExcel(ConnStr:PChar; SQLcmd:PChar); stdcall;
var
ws : ExcelWorksheet;
cnStr,queryStr : String;
qt : _QueryTable;
begin
{init xl application}
if xlApp = nil then
begin
xlApp := TExcelApplication.Create(nil);
xlApp.ConnectKind := ckRunningOrNew;
xlApp.Tag := 0;
end;

with xlApp do
begin
Visible[0] := true;
Cursor[0] := xlWait;
StatusBar[0] := 'MOHON TUNGGU...';

if Workbooks.Count = 0 then Workbooks.Add(EmptyParam,0);
Tag := Tag + 1;
ws := (Sheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as ExcelWorksheet);
try
ws.Name := '^ASEP^ #' + IntToStr(Tag);
except else
Randomize;
ws.Name := '^ASEP^ #' + IntToStr(Random(1000));
end;

try

ws.Range['A1', 'IV1'].Font.Bold := true;
ws.Range['A1', 'IV1'].HorizontalAlignment := xlHAlignCenter;
ws.Range['A1', 'IV1'].Font.Size := 12;
ws.Range['A1', 'IV1'].Font.Name := 'TAHOMA';
ws.Range['A1', 'IV1'].Font.Color := RGB(0,0,255);
//ws.Range['A1', 'IV1'].Borders.Color := RGB(0,0,0);

cnStr := String(ConnStr);
queryStr := String(SQLcmd);

qt := ws.QueryTables.Add(
cnStr,
ws.Range['A1',EmptyParam],
queryStr
);

qt.Refresh(0); //0 = do not execute query in the background
qt.SaveData := true;
qt.EnableRefresh := false;
qt.EnableEditing := false;
qt.FillAdjacentFormulas := false;

Columns.AutoFit;
Rows.AutoFit;

except else
StatusBar[0] := 'Export failed!';
end;
Cursor[0] := xlDefault;
StatusBar[0] := 'Ready';
ActiveWindow.Activate;
MessageBeep(MB_ICONINFORMATION);
end; //with
end;

exports
RecordsetToExcel;

begin
end.

[Spudhead]

I don't know what that is, grungebit, but it's certainly not going to run as an ASP page.

Here's a function I use to read excel data when I need to:

function readDatafile(sFilePath,sSheetName)
' return CSV or Excel file contents in array
'===========================

'Open ODBC connection to data file
dim sDataDir, sFileName, sFileExtension, sFileConnectionString, sFileSQL
sFileName = getFileName(sFilePath)
sFileExtension = getFileExt(sFilePath)
sDataDir = replace(sFilePath, sFileName, "")

select case ucase(sFileExtension)
case "XLS"
sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&sFilePath&";DefaultDir="&sDataDir&";"
sFileSQL = "SELECT * FROM [" & sSheetName & "$]"
case "TXT", "CSV"
sFileConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDataDir & ";Extended Properties=""text;HDR=No;FMT=Delimited"""
sFileSQL = "SELECT * FROM [" & sFileName & "]"
end select

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

[demtron]

@spudhead, that's a nice piece of code there. Going to bookmark this page for future reference.