Read Write to File, Excel Workbook, Access using Javascript and SQL
Javascript when used with ADO* and SQL can read write from data sources such as a file, Excel or an Access database. (* ADO = ActiveX Data Object). Since this technique relies on the use of ADO, a bit about ADO before we begin to help you understand how this works. According to wiki, Microsoft’s ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. ADO allows a developer to write programs that access data without knowing how the database is implemented. You must be aware of your database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute arbitrary SQL commands. The disadvantage of this (i.e. using SQL directly) is that it introduces a dependency upon the type of database used.
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.
How to read and write from a flat file using Javascript and ADO
The following code will help you read data from a plain text or a CSV file and write to a text/csv file.
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.
<head></head>
<body>
<script type=”text/javascript”>
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.
while (!f1.AtEndOfStream)
{
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
}
f1.Close();
f2.Close();
</script>
</body>
</html>
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.)
Ok having done a read write operation from and to a text file, now lets move on to reading and writing from a database using javascript.
How to read and write from an Excel workbook or Access database using Javascript and ADO
The following code will help you read data from excel worksheet using javascript, sql and ADO.
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 (\).
<head>
<title>Read from Excel or Access</title>
<script language=”JavaScript”>
function getCount()
{
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;”
cn.Open(strConn);
var rs = new ActiveXObject(“ADODB.Recordset”);
var SQL = “select * from [my_table_name$]“;
rs.Open(SQL, cn);
if(rs.bof)
{
document.write(‘No records available for this query’);
}
if(!rs.bof)
{
rs.MoveFirst()
while(!rs.eof)
{
for(var i=0; i!= rs.fields.count; ++i)
{
document.write(rs.fields(i).value + “, “);
}
document.write(“<br />”);
rs.MoveNext()
}
}
rs.Close();
cn.Close();
}
</script>
</head>
<body>
<input type=”button” value=”Get count” onclick=”getCount()”>
</body>
</html>
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.
cn.Open(strConn);
You may have also probably noticed the use of SQL in between javascript. I will not get in to the details for now but just keep in mind that with a bit of modification, you can do most of the operations that you’d normally do with SQL while working with a database.
rs.Open(SQL, cn);
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.
and
With these two examples, we have been able to read and write to flat files, an excel worksheet and an Access database.
Really this code is wonderfull and helped me
thanks
August 18th, 2009 at 2:27 amThanks wael and welcome to daTaB!
August 18th, 2009 at 3:58 amHi,
I want to pull some data from excel file and display it on my html page, and post some data to excel. I don’t need any sever-side programming. My network runs on shared drive only. I am new to programming. Please give me a html javascript code for it on my above Email Id.
Thanks,
August 30th, 2009 at 10:50 amIftakhar
Iftakhar,
You can find the VBA code to read and write to excel using ADO and SQL here. This will do what you are seeking without server side programming.
regards,
August 30th, 2009 at 11:31 amhi, i want to write data the user has put in the text field to microsoft access. Just like a sign up form. Can you teach me or send me some example. i am a newbie so please make it easy to understand.
November 27th, 2009 at 6:11 amThanks
@Peter – Try this code to connect to connect to Access using VBA. You’d need to modify a part of the code to make it work with a text file. I will post the VBA code to connect and write to an Access database shortly.
November 27th, 2009 at 7:28 amhi i need to read details from excel (latitude and longitude) and dispay it on map of road with moving vectors..which codes should i use??
April 9th, 2010 at 12:51 pmio solution for internet browser
May 29th, 2010 at 1:31 amtrix.exofire.net
Thank you very much for this code maaan … I was like in black hole
:D this code is really helpful … 
March 8th, 2011 at 2:29 pmI have a problem with Extract a cell from excel into my html! I have this undefined value… any ideas!?
function readFromExcel(x,y)
{
var excel = null;
var book = null;
var sheet = null;
var data = null;
var val=null;
excel = new ActiveXObject(“Excel.Application”);
book = excel.Workbooks.Open(“http://www.nte.dpep.pep.pemex.com/aipra/datosweb/planeacion/excel_data/hoja.xlsx”);
sheet = book.Worksheets.item(1);
data = sheet.Cells(x,y).Value;
return data;
excel.quit();
excel.application.quit();
CollectGarbage();
}
val = readFromExcel(5,18);
March 25th, 2011 at 11:31 amdocument.write(val);
Is there a way to set a relative path to the .exl file instead of an absolute path?
August 7th, 2012 at 3:34 amvery good
December 11th, 2012 at 3:48 amI have a question which the third part ” How to read and write from an Excel workbook or Access database using Javascript and ADO” . I downloaded and try it, it cannot run the first row?
July 3rd, 2013 at 4:25 amis it possible that IE 10 on Windows 8 will not work for this type of code ? I just tried to see result ofyour code and my page is totally blank… and my ActiveX is enable. Any idea why ?
August 14th, 2013 at 1:00 pmRegards
Really did what I needed – thanks for solving the problem of reading local files. (It worked with a csv too).
For Chrome you can install the IE Tabs Extension which allowed it to work in that along with another file enumeration code for finding local files, which also only worked in IE.
Open http://www.ietab.net/options in Chrome to install it.
September 21st, 2014 at 12:48 am