Categorized | excel download

Read Write to File, Excel Workbook, Access using Javascript & SQL




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.

read-write-to-file-excel-workbook-access-using-javascript-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.

<html>

<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 (\).

<html>
<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.

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);

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.

var SQL = “select * from [my_table_name$]“;
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.

var strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\database\\mytest\\access\\database.mdb;Persist Security Info=False”

and

var SQL = “select distinct(” + field_name + “) from democollection”

With these two examples, we have been able to read and write to flat files, an excel worksheet and an Access database.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. wael soltan wrote:

    Really this code is wonderfull and helped me

    thanks

  2. Ajay wrote:

    Thanks wael and welcome to daTaB!

  3. Iftakhar Ahmad wrote:

    Hi,
    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,
    Iftakhar

  4. Ajay wrote:

    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,

  5. Peter wrote:

    hi, 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.
    Thanks

  6. Ajay wrote:

    @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.

  7. Suvin wrote:

    hi 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??

  8. schemo wrote:

    io solution for internet browser
    trix.exofire.net

  9. Vince wrote:

    Thank you very much for this code maaan … I was like in black hole :D :D this code is really helpful … :)

  10. pisd wrote:

    I 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);
    document.write(val);

  11. bart simpson wrote:

    Is there a way to set a relative path to the .exl file instead of an absolute path?

  12. rex wrote:

  13. Hin wrote:

    I 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?

  14. Yan Desjardins wrote:

    is 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 ?
    Regards

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel