7 Replies Latest reply on Apr 14, 2015 3:28 PM by MW Design

    Workflow for exporting xml from Excel and importing into table in InDesign

    nickb13178246

      Hello,

       

      I have worked out how to get data from a .xlsx into a table in InDesign. However, the workflow is still a little clunky and I am looking for way to automate the process further. My current workflow is as follows (files can be download here: http://visual360.co.uk/xml.zip)

       

      1. xml is exported from .xlsx by mapping the schema (see what_I_get.xml)
      2. assign tags to table and individual cells in InDesign
      3. This is the clunky bit: edit the what_I_get.xml to:
        • remove the indentation (this seems necessary as otherwise when I get to stage 4, all the imported text is also indented)
        • add tag for table in InDesign (p10_table)
        • add tags for individual cells (p10_c11 etc.)
      4. import the produced what_I_want.xml in the structure panel of InDesign.

       

      Can anyone point me in the direction of how to automate step 3 above?

       

      Thanks in advance,

       

      Nick

        • 1. Re: Workflow for exporting xml from Excel and importing into table in InDesign
          MW Design Level 4

          Hello Nick,

           

          I didn't have much time today to do much with this. I have a link below that has your what_I_get.xml and also a modified one--with the name of what_I_get-mod.xml.

           

          There are also two different XSL files in there. with your original xml file, when you import your XML file, use this XSL file:

          capture-003096.png

           

          With the other XML file, use this one:

          capture-003097.png

           

          I don't know if you use an XML editor or are familiar with XSL or not. I prefer to use XSL/XSLT files in an editor, as well as other functions (replacing elements, tabs, wrapping elements, etc.).

           

          In the second XML file, I created a new element to wrap the bullet points. So all 4 bullet points will end up in the same cell. Another difference is I didn't see the point of the <module_description_title> element thinking it might be just a column heading.

           

          Hope this helps. Any questions, feel free to respond here. I may not get right to a response, but I will at some point.

           

          Mike

          Dropbox - NickB.zip

          • 2. Re: Workflow for exporting xml from Excel and importing into table in InDesign
            nickb13178246 Level 1

            Hi Mike,


            Thanks very much for the reply. I am not at all familiar with XSL/XSLT. I have given it a quick google and it seems like it could be quite a steep learning curve to master. Maybe if I explain the whole context here, it may allow you to point me in the direction of a tutorial that provides a step by step approach to achieve my final goal. Alternatively, you may be able to suggest a whole new workflow.


            I am in the process of updating this document: http://www.ncl.ac.uk/students/insessional/assets/documents/IS_Brochure_2014-15.pdf


            The tables on pages 10-21 contain lots and lots of repeated information. We want the layout to in this format so that each group of students has all the information relevant to them on a single double spread. However, the challenge of using this approach is that it is very high maintenance to ensure the duplicated information is kept in sync - even using a copy / paste approach.


            I was hoping to overcome this by using a single source of data (eg. a spreadsheet) which would then feed into the InDesign. This workflow seems to be overcomplicated by the apparent need to use xml as the "middle-man".


            Ideally, I would like to press the export button in Excel and get this information into the the correct cells in the tables on pages 10-21 without a significant amount of manual labour - after all, my reasons for investigating an automated approach in the first place was to avoid the manual entering of data ie. the scope for error...


            An alternative approach maybe to skip Excel altogether and just use .xml. However, I am not sure that this is possible as Indesign only seems to allow you to import each element once. Maybe you could correct me here...


            I am happy to invest a certain amount of time in developing this approach as once it is in place, it will save a huge of proof-reading time year after year, especially as inconsistencies still persist despite our best efforts to weed them out.


            If you could let me know your thoughts on this and maybe identify a tutorial that would help me, I would be extremely grateful.


            Thanks again!


            Nick

            • 3. Re: Workflow for exporting xml from Excel and importing into table in InDesign
              MW Design Level 4

              Hi Nick,

               

              From where does the data come from? That is, what form is it delivered to you?

               

              The XSL files I provided in my previous post (below my signature) simply create CALs tables. Plain, no formatting. However, they can be styled by selecting a given table and styling with well-built table styles in conjunction with cell styles.

               

              Aside from a couple deviations, I count 7 different table formats. While XSL/XSLT processing files could be written for each style, if you want pre-formatted tables, then it is more work to make them spit out ID formatted tables. Doable, but I think outside the scope of the forum. And it still depends more or less on what you are actually given.

               

              There is precious little in the way of XML tutorials. There are a few books. There would still quite a bit of correlating what they use for examples to your specific needs.

               

              Have you tried having the data for each of these tables on different sheets and/or ranges and linking the Excel files into already formatted tables?

               

              Other than the above, there would be catalog plug-ins that likely would work at a price. Even so I have no idea what to recommend as, again, it all hinges on how you receive the data itself.

               

              Mike

              • 4. Re: Workflow for exporting xml from Excel and importing into table in InDesign
                nickb13178246 Level 1

                HI Mike, Thanks for getting back to me again. I input the data. Previously, I typed straight into InDesign but this seems very high maintenance with so much repeated information. For example, we might decide to change the duration of a course or the dates and we have to ensure it is updated everywhere in the document. Thanks, Nick

                • 5. Re: Workflow for exporting xml from Excel and importing into table in InDesign
                  MW Design Level 4

                  Hi Nick,

                   

                  No worries. I ended up with more free time available today than I thought I would.

                   

                  For any XML or Tagged Text type of automation to work at its best, it is best as an (mostly) all or nothing type of thing. If you are manually keying information presently for only portions of the catalog, I fail to understand any gains hoped to be achieved by using two applications.

                   

                  I use to do two community college course offering catalogs. One was sub 100 pages, the other well over. Both had a mix of "free" text, tables and images. The only reason it made sense to use XML at the time (both have been moved t using tagged text) was because I was given the access database in one case (which I exported the XML from), and an XML file that was extracted directly from the SQL database. The data provided was over 90% of the catalogs' respective content. This was the same data as was used to drive the websites. It made sense.

                   

                  I would suggest in your case, if you really want to use Excel for the table data, is to investigate what I had mentioned, linked Excel sheets/ranges. It will simplify the two-application work-flow without the need to learn entirely new things that I do not believe would save time/effort.

                   

                  Mike

                  • 6. Re: Workflow for exporting xml from Excel and importing into table in InDesign
                    nickb13178246 Level 1

                    Hi Mike, One final question: is there a way to clone cells in a table to other cells in a different table. My idea is that I have a number of "master cells" and that any updates I make to these are automatically implemented in any "slave" cells across the rest of the document. Is this an option? Thanks, Nick

                    • 7. Re: Workflow for exporting xml from Excel and importing into table in InDesign
                      MW Design Level 4

                      It might be time consuming to set up initially, but you might be able to set up text variables to handle it. You can only use a single character style with them is the only give a heck because ID sees them as a single character. And that fact might have other issues. But give it a try and see if that works for your needs.

                       

                      Mike