4 Replies Latest reply on Mar 6, 2010 3:16 PM by Robert Heist II

    Extracting a particular data from a spreadsheet

    archimaxx

      Is there a way (or a plugin) to extract particular cell data from an excel speadsheet uploaded to server? I want a piece of text on the webpage to be replaced dynamically with a data from a certain cell in the speadsheet, but can't find a way how to do it.

       

      Basically I want to create something like this:

      <p> "import A16 from .../spreadsheets/table1.xls" </p>

      <p> "import C15 from .../spreadsheets/table1.xls" </p>

      etc.

        • 1. Re: Extracting a particular data from a spreadsheet
          Ben M Adobe Community Professional

          Nope, that's what databases are for.  If this is a small table that is not being updated often/regularly, then you can consider making an HTML table and setting up a Spry Data Set.

          • 2. Re: Extracting a particular data from a spreadsheet
            BCDoherty Level 3

            Yes, indeed, you would need a database, but you can load a data into the database from an Excel spreadsheet - saving it as a comma-separated-value (.csv) document. Updating the database  dynamically is another matter.

            • 3. Re: Extracting a particular data from a spreadsheet
              bregent Most Valuable Participant

              It is possible to use a spreadsheet as a datasource for a dynamic site, but it's really not pratical as it leads to concurency problems.  You could however, set it up as a datasource locally to generate a static page. This doesn't sound like what you want however.

              • 4. Re: Extracting a particular data from a spreadsheet
                Robert Heist II Level 3

                Well, actually you can connect to an Excel spreadsheet as a datasource using the proper ODBC connection, either the Microsoft Jet OLE DB Provider -or- Microsoft OLE DB Provider for ODBC Drivers http://support.microsoft.com/kb/257819

                 

                BUT

                 

                That will only work if you are running on a Windows server (IIS) which may or maynot be the case.

                 

                Even if you are on IIS and they have the proper ODBC drivers installed, I am not sure what the query would look like to get a particular cell's data since it may not use SQL.  Though I would think you could make a query to return just the one row's record and then call the single field (column) out of the dataset.  Hmm, that would probably work.  You would have to continually reupload the Excel file every time there was a change to the data though.

                 

                Bottom line is, it is unlikey you are in the right environment to even create the connection so the next best option is to export the file to csv, import it into whatever db your server supports and create a server-side script to query the db and get that single piece of data. The export to csv and import to your server's db is also going to have to be done every time the data changes.

                 

                Making a xml file out of the data which could be manipulated in a Spry Dataset could work too...but again you would have to reupload the xml file every time your data changed

                 

                LOTS of work, for one piece of data.