easiest way to read data from excel spreadsheet with javascript?

I have a list of airport codes, names, and locations in an Excel Spreadsheet like the below:

+-------+----------------------------------------+-------------------+
|  Code |               Airport Name             |      Location     |
+-------+----------------------------------------+-------------------+
|  AUA  |   Queen Beatrix International Airport  |  Oranjestad, Aruba|
+-------+----------------------------------------+-------------------+

My Javascript is passed a 3 character string that should be an airline code. When that happens I need to find the code on the spreadsheet and return the Airport Name and Location.

Im thinking something like:

var code = "AUA";

console.log(getAirportInfo(code));

function getAirportInfo(code) {

// get information from spreadsheet
//format info (no help needed there)

return airportInfo;
}

Where the log would write out:

Oranjestad, Aruba (AUA): Queen Beatrix International Airport

What is the easiest method to get the data I need from the spreadsheet?

Extra Info:

  1. The spreadsheet has over 17,000 entries
  2. The function alluded to above may be called up to 8 times in row
  3. I don't have to use an Excel Spreadsheet thats just what I have now
  4. I will never need to edit the spreadsheet with my code

I did search around the web but everything I could find was much more complicated than what Im trying to do so it made it hard to understand what Im looking for.

Thank you for any help pointing me in the right direction.

Answers:

Answer

I've used a plain text file(csv, or tsv both of which can be exported directly from Excel)

Loaded that into a string var via xmlhttprequest. Usually the browsers cache will stop having to download the file on each page load.

Then have a Regex parse out the values as needed.

All without using any third party....I can dig the code out if you wish.

Example: you will need to have the data.txt file in the same web folder as this page, or update the paths...

 <html>
      <head>
        <script>

          var fileName = "data.txt";
          var data = "";

          req = new XMLHttpRequest();
          req.open("GET", fileName, false);

          req.addEventListener("readystatechange", function (e) {
            data = req.responseText ;
          });

          req.send();

          function getInfoByCode(c){
            if( data == "" ){
              return 'DataNotReady' ;
            } else {
              var rx = new RegExp( "^(" + c + ")\\s+\\|\\s+(.+)\\s+\\|\\s+\\s+(.+)\\|", 'm' ) ;

              var values = data.match(rx,'m');
              return { airport:values[2] , city:values[3] };
            }
          }

          function clickButton(){
            var e = document.getElementById("code");
            var ret = getInfoByCode(e.value);

            var res = document.getElementById("res");

            res.innerText = "Airport:" + ret.airport + " in " + ret.city;

          }

        </script>
       </head>
       <body>
        <input id="code" value="AUA">
        <button onclick="clickButton();">Find</button>
        <div id="res">
        </div>

       </body>
    </html>
Answer

I ended up using a tool at shancarter.com/data_converter to convert my flie to a JSON file and linked that to my page. Now I just loop through that JSON object to get what I need. This seemed like the simplest way for my particular needs.

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.