So what we are going to do in our examples is to take a plain HTML page and equip it with an ActiveX object. Once that happens, the HTML page can be used to read plain text files, Excel workbooks or even databases like Access. Just bear in mind that interacting with a database will require working knowledge of basics of SQL.
Instructions to follow:
1. Download the file given above and extract contents to a local drive. (The input file is given with the download.)
2. Edit the location of the input and output file. Ensure that the directories are separated by a double backslash (\\) rather than a single backslash (\).
3. Ensure the the location of the output file is writable.
var ForReading = 1, ForWriting = 2, ForAppending = 8;
var TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0;
var fso = new ActiveXObject(“Scripting.FileSystemObject”);
// Open the files for input and output
//CHANGE THESE LOCATIONS TO ENSURE THAT :
// a) testfile1.txt exists and
// b) testfile2.txt can be created and is writable
var filename1 = “D:\\database\\mytest\\testfile1.txt”;
var filename2 = “D:\\database\\mytest\\testfile2.txt”;
var f1 = fso.OpenTextFile(filename1, ForReading, true);
// Open the file for input.
f2 = fso.OpenTextFile(filename2, ForWriting);
f2.WriteLine(“The following have been copied from input file :-“);
// Read from the file and display the results.
var r = f1.ReadLine(); //This line will read the data to the input file
document.write (r + “<br />”); //This line will display the data on the screen
f2.WriteLine(r); //This line will write the data to the output file
Essentially what this code does is to create an ADO (active data object) and then uses that object to open files for reading and writing from and to a local drive. (This code will work only with Internet Explorer. If the browser blocks ActiveX, you will need to instruct the browser to allow ActiveX components access to content from local drive. This can simply be done by right clicking in the message appearing at the top of the browser window and clicking the ‘Allow blocked content’ option.)
Instructions to follow:
1. Download the file given above.
2. Extract the contents to a local drive.
3. Open the html file in a text editor and edit the location of the input Excel workbook or Access database. Ensure that the directories are separated by a double backslash (\\) rather than a single backslash (\).
<title>Read from Excel or Access</title>
var cn = new ActiveXObject(“ADODB.Connection”);
var strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\database\\mytest\\demo.xls;Persist Security Info=False;Extended Properties=Excel 8.0;”
var rs = new ActiveXObject(“ADODB.Recordset”);
var SQL = “select * from [my_table_name$]”;
document.write(‘No records available for this query’);
for(var i=0; i!= rs.fields.count; ++i)
document.write(rs.fields(i).value + “, “);
<input type=”button” value=”Get count” onclick=”getCount()”>
Look carefully at the line that establishes the connection with the database or the excel workbook. Ensure that you only edit portion where we set the path of the input file and not any other portion of the connection string.
Carefully note the use of table name in the SQL statement when connecting to an Excel workbook. This format is a little different from a normal SQL statement. The work sheet name is enclosed in brackets ‘[—]’ and immediately followed by a ‘$’ sign. When working with a proper database, that would need to be replaced with the named of the table without brackets or the $ sign. (By default, an Excel file is opened by ADO in a read-only mode. With a bit of hacking, we can write to it as well. )
When working with Access, you need to change to connection string and the SQL statement as shown below.
With these two examples, we have been able to read and write to flat files, an excel worksheet and an Access database.