6 Replies Latest reply on Jun 12, 2012 10:38 PM by John Hawkinson

    I need to make a spreadsheet based on info from specific table columns and headlines above those tab

    Talyianna Level 1

      Hi all,

       

      A client of mine is requesting that I create a spreadsheet from a huge catalog that I am doing for him (this will also be done for all similar huge catalogs). The spreadsheet needs to pull information from some of the table columns, as well as the headline for each table. (This spreadsheet will then go out to some other people who will be putting this information into an online database/website for the client.) Let me be more specific and show you an example below.

       

      Information from InDesign Catalog:

       

      Tea Set

       

      Part #MFG #Description
      123-456654321Tea Cup
      789-010010987Saucer

       

      Here is how the Excel Spreadsheet needs to look:

       

      Part #MFG #Product NameDescriptionPage #
      123-456654321Tea SetTea Cup2
      789-010010987Tea SetSaucer2

       

       

      You have no idea how I have been pulling my hair out over this. Doing this manually will be a nightmare each time. My first thought was that I could assign specific cell styles to the columns (cells) and then create a TOC and then finangle with that - well, that didn't quite work, especially because of the headline I needed to pull into the spreadsheet as well. Is there ANY way I can get this information onto a spreadsheet? Knowing how wonderful InDesign is and all the tremendous capability it has, I am sure there IS a way and I just dont know how. Please PLEASE can someone point me in the right direction?

       

      Thank you very much in advance for your assistance:) Any help would be greatly appreciated.

       

      Christine

        • 1. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
          Peter Spier Most Valuable Participant (Moderator)

          Seems to me this is going in the wrong direction. Normally we take an Excel spreadsheet and import data from selected columns to make our tables in ID.

           

          You could convert the table to text in ID, perhaps, then export that as text, and Excel should be able to pick it up as tabular inforamtion to build a spreadsheet, but you'll only get columns for what's inthe table in ID. You'll have to add the rest someplace, and Excel is probably the better place to do it.

          • 2. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
            John Hawkinson Level 5

            Peter: Except that the spreadsheet needs the InDesign page numbers.

            I...don't have time to carefully think about the right approach to this problem, but I'm going to try to come back to it in a day or so when I have a breather.

            • 3. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
              Peter Spier Most Valuable Participant (Moderator)

              John Hawkinson wrote:

               

              Peter: Except that the spreadsheet needs the InDesign page numbers.

              As I said, you'll only get the data that's in the table. Adding a column for page numbers in Excel will be a pain, but at least you can copy the number and paste it into multiple cells at once.

              It also needs a new column for Product Name -- information that is impossible to get from what's shown in the example above.

               

              I know you don't want to think about it now, but perhaps if the product name is somehow associated with the table (each table a unique product name) you could script a variable to pick that up, and also add a column filled with current page markers to be added to the table before conversion to text.

              • 4. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
                Talyianna Level 1

                I am sure that this can somehow be done by making use of the styles. For example, when creating the TOC InDesign does a GREAT job of pulling in the styles (that are of course associated with the headlines and whatever needs to be included in the TOC) and associating them with the correct page numbers. I had experimented a little with creating specific paragraph styles and working these into the table and cell styles...then applying the Table Style to the table. I was halfway getting to where I wanted to be by creating a TOC that pulled in all of these styles and applied the corresponding page numbers (that I could somehow work into converting back into a table to then flow onto an Excel spreadsheet) but the wrench in the works was of course the Product Name. That said, I am VERY confident that this CAN be done somehow, by making use of the styles, using the same (or similar) methodology that is used to create the TOC. If only I were a pro at scripting...

                 

                Peter, I don't think the tables need to be converted to text and a column for the page numbers added prior to doing that. To me, this is just adding an extra step or two and would would be too much extra work.... there are probably a couple of thousand tables in the catalog. It would be easier to simply copy the table (by highlighting all the cells, with the exception of the header) and then pasting them onto an Excel spreadsheet in corresponding cells and then adding in the page numbers and Product Name in Excel (the Excel spreadsheet will of course be one long sheet to include ALL of the info from all of the tables in ID document). As I mentioned above, I am so sure there must be an easier (and automated) way to do this though.

                • 5. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
                  Talyianna Level 1

                  John,

                   

                  If you can give me some advice I would VERY very very much appreciate it ! Here is what I going to do moving forward in a new catalog that we are doing. I will add another column to each table that has the product name, but will hide this column in the actual layout, simply so that the information is there to pull from for the spreadsheet. This should make things a lot more possible:) Please can you help?

                   

                  Thank you kindly,

                  Christine

                  • 6. Re: I need to make a spreadsheet based on info from specific table columns and headlines above those tab
                    John Hawkinson Level 5

                    Sorry for the delay.

                    I am sure that this can somehow be done by making use of the styles. For example, when creating the TOC InDesign does a GREAT job of pulling in the styles (that are of course associated with the headlines and whatever needs to be included in the TOC) and associating them with the correct page numbers. I had experimented a little with creating specific paragraph styles and working these into the table and cell styles...then applying the Table Style to the table. I was halfway getting to where I wanted to be by creating a TOC that pulled in all of these styles and applied the corresponding page numbers (that I could somehow work into converting back into a table to then flow onto an Excel spreadsheet) but the wrench in the works was of course the Product Name. That said, I am VERY confident that this CAN be done somehow, by making use of the styles, using the same (or similar) methodology that is used to create the TOC. If only I were a pro at scripting...

                    Well, it's great that you're certain and confident, but I think your confidence is misplaced.

                    I had sort of hoped that running headers could be convinced to support this, but that doesn't seem to be true.

                     

                    I think you need to postprocess the data somehow. If I was doing this for real I'd probably export the document to IDML and have a seperate program read the IDML XML, find the product names and page numbers, and insert them in tables. Though...that might mess up the page numbers. And it'd be a lot of work.

                     

                    An much-faster-to-rapid-prototype solution is to have an indesign script go through the document and add in page numbers and the product names. Here's a rough prototype:

                     

                     

                    var d,s,story,table,product,page,c2,c3,c5,i,j.k;
                    
                    d=app.activeDocument;
                    s=d.stories;
                    for (i=0; i<s.length; i++) {
                        story = s[i];
                        for (j=0; j<story.tables.length; j++) {
                            table = story.tables[j];
                            product = story.chara
                    cters[table.storyOffset.index-2].paragraphs[0].contents;
                            page = table.parent.parentPage.name;
                            // $.writeln("This is table "+j+". Product name: "+product+" page "+page);
                            c2=table.columns[1];
                            c3=table.rows[0].columns.add(LocationOptions.AFTER, c2);
                            c3.contents=product;
                            c5=table.rows[0].columns.add();
                            c5.contents=page;
                        }
                    }
                    

                     

                    This doesn't handle the header rows in tables, but hopefully that's ok.

                    It may get page numbers wrong on multipage tables. Probably it would be better to just use

                    SpecialCharacters.AUTO_PAGE_NUMBER on the last full line (c5.contents=).

                    It should also probably not just look back 2 characters from the start of the table to find the paragraph, but it's probably good enough.