easiest way to read data from excel spreadsheet with javascript?

Question

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:

The spreadsheet has over 17,000 entries
The function alluded to above may be called up to 8 times in row
I don't have to use an Excel Spreadsheet thats just what I have now
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.

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
– Wesley Smith, Commented Apr 23, 2013 at 14:59
D3 is able to parse CSV files github.com/mbostock/d3/wiki/CSV However I would prefer to use a JSON file. — jantimon
– 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
– 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
– Wesley Smith, Commented Apr 24, 2013 at 5:44

tagurit · Accepted Answer · 2024-12-04 16:50:58Z

5

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.

edited Dec 4, 2024 at 16:50

tagurit

5786 silver badges16 bronze badges

answered Jan 17, 2015 at 2:01

Wesley Smith

19.6k22 gold badges93 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wesley Smith Over a year ago

Why on earth would anyone downvote this? My question was extremely clear and this was in fact the simplest solution for my needs

Mesh · Accepted Answer · 2013-04-24 12:02:44Z

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>

Aiyush Dhar · Accepted Answer · 2024-12-04 17:23:04Z

1

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

answered Dec 4, 2024 at 17:23

Aiyush Dhar

213 bronze badges

Collectives™ on Stack Overflow

easiest way to read data from excel spreadsheet with javascript?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related