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.

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.

• javascript
• excel

• Server side or client side? XLS, XLSX or CSV? – jantimon Commented Apr 23, 2013 at 14:56
• client side, and i can use whatever format will make it easier, or a different kind of file altogether, the file itself will be kept on the server if thats what you mean – Wesley Smith Commented Apr 23, 2013 at 14:59
• 1 D3 is able to parse CSV files github.com/mbostock/d3/wiki/CSV However I would prefer to use a JSON file. – jantimon Commented Apr 23, 2013 at 15:02
• reading through the docs for D3 now thank you, looks promising ill let ya know how it works out – Wesley Smith Commented Apr 23, 2013 at 15:20
• @jantimon I ended up using a tool at shancarter.com/data_converter to convert my flie to a JSON file and can cet what I need from there, throw up an answer and ill accept it, thanks for your help! – Wesley Smith Commented Apr 24, 2013 at 5:44

3 Answers 3

I ended up using a tool at https://shancarter.github.io/mr-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.

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>

I was always a big fan of Alasql for things like this. It works both on the browser as well as on serverside node.

Code to read from xlsx looks like

 alasql.promise('select City, Population from xlsx("cities.xlsx") where Population > 100000')
        .then(function(data){
             console.log(data);
        }).catch(function(err){
             console.log('Error:', err);
        });