24 Replies Latest reply on Nov 1, 2015 8:47 AM by Trevorׅ

    Exporting data in tables to an Excel spreadsheet?

    MoshiMan

      Is there a script or another way to export or save a documents tables (multiple tables over several pages) to an excel spreadsheet? I have found a solution which involves exporting each spread as a html file which i can then open straight in excel but this is still quite long winded and i can only seem to do this a spread at a time. As the job I am working on involves many pages this is taking too long. Surely there must be a script which can automate this in some way. I am fairly new to scripting so any help will be grateful. I have searched in scripting but nothing has really come close to what i am trying to achieve. Indesign CS5.5 user.

        • 1. Re: Exporting data in tables to an Excel spreadsheet?
          Harbs. Level 6

          The simplest solution is probably to save the table contents as a csv files and open that in Excel.

          • 2. Re: Exporting data in tables to an Excel spreadsheet?
            Daniel Sterchi Adobe Community Professional

            Hi Matthew

             

            As Harbs wrote, convert the table to text, then store the text as CSV-File

             

            To convert the Table to text:

            - Table.convertToText (columnSeparator: string , rowSeparator: string ): Text

             

            Save the text to a file:

            - Text.exportFile (format:varies, to: File , showingOptions: Boolean , using: PDFExportPreset , versionComments: string , forceSave: Boolean )

             

            That could or should work

             

            Kind regards

             

            Daniel (from Switzerland)

            • 3. Re: Exporting data in tables to an Excel spreadsheet?
              Laubender Adobe Community Professional & MVP

              @Daniel – ahem(!)…

               

              The documentation does say so, but "using:PDFExportPreset" implies a PDF file as output file format. This parameter is not provided, if you want to export to a text file format, let's look at

               

              Enumeration ExportFormat

               

              ExportFormat.EPS_TYPEExports to EPS format.1952400720 = 't_EP'
              ExportFormat.EPUBExports to EPub format.1701868898 = 'epub'
              ExportFormat.HTMLExports to XHTML format.1213484364 = 'HTML'
              ExportFormat.INCOPY_MARKUPExports to InCopy markup (ICML) format.1768123756 = 'icml'
              ExportFormat.INDESIGN_MARKUPExports to InDesign markup (IDML) format.1768189292 = 'idml'
              ExportFormat.INDESIGN_SNIPPETExports to InDesign snippet (IDMS) format.1936617588 = 'snpt'
              ExportFormat.INTERACTIVE_PDFExports to Interactive PDF format.1952409936 = 't_iP'
              ExportFormat.JPGExports to JPEG format.1246775072 = 'JPG '
              ExportFormat.PACKAGED_XFLExports to packaged XFL format.1702389356 = 'exfl'
              ExportFormat.PDF_TYPEExports to PDF format.1952403524 = 't_PD'
              ExportFormat.PNG_FORMATExports to PNG format.1699761735 = 'ePNG'
              ExportFormat.RTFExports to rich text format (RTF).1381254688 = 'RTF '
              ExportFormat.SWFExports to SWF format.1702066022 = 'eswf'
              ExportFormat.TAGGED_TEXTExports to a tagged text file with a TXT extension.1416066168 = 'Tgtx'
              ExportFormat.TEXT_TYPEExports to text (TXT) format.1952412773 = 't_te'
              ExportFormat.XMLExports the document's tagged content to XML.1481460768 = 'XML '

               

               

              Two of that list will meet our needs*:

               

              ExportFormat.RTF

              ExportFormat.TEXT_TYPE

               

              *And these two types only support a tabular approach with *formatted* text and *formatted* table cells in a unsufficient way.

               

              @Matthew:

               

              There is another way:

              simply copy/paste to Excel.

               

              Or even better copy/paste to Word.

              And in another step copy/paste from Word to Excel.

              And see, if some of the formatting will survive.

               

              Here an example with a very simple InDesign table:

               

              1. Original InDesign table

              01-InDesign-Table.png

              2a. Copy/Paste that table to Word:

               

              02a-Copy-Paste-To-Word.png

               

              2b. Copy/paste to Excel:

               

              02b-Copy-Paste-To-Excel.png

               

              3. Here the extra step. Copy/paste from Word (2a) to Excel:

               

              03-Copy-Paste-To-Word_Copy-Paste-To-Excel.png

               

              But remember: this is a very simple table. Prepare for surprises if it's going more complex.

               

              Used software: InDesign CS5.5, MS Excel 2004, MS Word 2004 on OSX 10.6.8.

               

              When on a Mac these steps could be automated with AppleScript. On a Windows PC, I think, VB Script could do that. But here I'm not sure, because I hardly know AppleScript and to a even lesser extend VB Script.

               

              Uwe

              • 4. Re: Exporting data in tables to an Excel spreadsheet?
                MoshiMan Level 1

                Thanks Laubender. I think the ExportFormat.RTF would work as the tables are set up as tab seperated coulmns. I ideally need to select all table in the document at once, convert to text and then export to RFT format.

                 

                I have started with this to select the contents of the tables:

                 

                app.activeDocument.stories.everyItem().tables.everyItem()

                 

                But as for the rest I haven't managed to work out how to follow on with this:

                 

                Table.convertToText (columnSeparator: string , rowSeparator: string ): Text

                 

                Any suggestions...?

                • 5. Re: Exporting data in tables to an Excel spreadsheet?
                  Daniel Sterchi Adobe Community Professional

                  Ups…

                   

                  First I started to search for a solution with XML and XML-Rules. But then I've read Harbs solution and I thougth there must be a much simpler solution. Then I saw the Text.exportFile…

                   

                  XML and XML-Rules are definitly to much work and I am sure there is a simpler solution.

                   

                  kind regards

                   

                  Dani (from Switzerland)

                  • 6. Re: Exporting data in tables to an Excel spreadsheet?
                    Daniel Sterchi Adobe Community Professional

                    Hi Matthew

                     

                    I have a sollution which works most of the time. Sometimes the script does not recognize all the table objects. So then there is an error on line 15 where it says:

                    var myText = myTables[i].convertToText(";", "\n");

                    The error says that there is an invalid object.

                     

                    What I found out is, that when I run the script on a fresh opened InDesign file the script works…

                     

                    @laubender perhaps you have a solution to this problem.

                     

                    Depeding of your operation system you have to choose an other rowSeparator.

                    There is not much error checking.

                     

                    You have to choose a directory for the files to be stored!

                     

                    kind regards

                     

                    Dani (from Switzerland)

                     

                    so here is the code:

                     

                    main();

                    exit();

                     

                     

                    function main() {

                     

                              if (app.documents) {

                     

                                        app.activeDocument.select(NothingEnum.NOTHING);

                     

                                        var myDoc = app.activeDocument,

                                                  myTables = myDoc.stories.everyItem().tables.everyItem().getElements();

                     

                                        var i = myTables.length;

                     

                                        alert(i + " tables found");

                     

                                        // for every table in the Indesign File

                                        while (i--) {

                     

                                                  var myText = myTables[i].convertToText(";", "\n");

                     

                                                  // without this line the text don't get selected !!!???

                                                  var dummy = myText.showText();

                     

                     

                                                  // create a CSV-File you have to change this!!

                                                  var csvFile = openFile("/table_" + i + ".csv");

                     

                     

                                                  // writes the selected text to the created file.

                                                  writeToFile(csvFile, document.selection[0].contents);

                     

                     

                                                  csvFile.close()

                                        }

                              }

                              alert("Done");

                    }

                     

                     

                     

                     

                    function openFile(fName) {

                              try {

                                        logFile = new File(fName);

                                        var ok = logFile.open ("w");

                              }

                              catch(myError) {

                                        alert("Error\nThe file: " + fName + " couldn't be read");

                              }

                              return logFile;

                    }

                     

                     

                    function writeToFile(f, msg) {

                     

                              try {

                                        f.writeln(msg);

                              }

                              catch(myError) {

                                        alert(myError);

                              }

                    }

                     

                    • 7. Re: Exporting data in tables to an Excel spreadsheet?
                      MoshiMan Level 1

                      Hey Daniel, Great work! I was half way there with this as i had managed to convert tables to text, just the output bit I hadn't got right.

                      Your script didn't work totally first time as you had used the wrong column seperator (;)

                       

                      var myText = myTables[i].convertToText(";", "\n");

                       

                      I have changed it to (:)

                       

                      var myText = myTables[i].convertToText(",", "\n");

                       

                      This keeps the coloumns split when converting to the CSV table.

                       

                      The only issue I have now is if i have many tables in a Indesign document, it creates a CSV file for each table. Is there any way to just create a single CSV from multiple tables? I know this may be asking a lot!

                      • 8. Re: Exporting data in tables to an Excel spreadsheet?
                        Daniel Sterchi Adobe Community Professional

                        Hi Matthew

                         

                        Yes, you have to adapt the script to your needs. Depending on your operation system you to change the row separator.

                         

                        There is also no error checking exept of the open and writeFile.

                         

                        I hope the script helped you to solve your task.

                         

                        Kind regards

                         

                        Dani (from Switzerland)

                        • 9. Re: Exporting data in tables to an Excel spreadsheet?
                          Trevorׅ Adobe Community Professional

                          Hi all,

                           

                          I didn't read much at all of the above but here a script I put together (with quite a lot of help) to create excel files from indesign on both Mac and Windows.

                           

                          I has quite an advantage over csv files in that they open strait without warnings.

                           

                          Adapt it as desired

                           

                          Trevor

                           

                           

                          // Exports SIMPLE tables as a proper excel file
                          // Needs Excell to be installed on the computer
                          // With a bit of brain racking can be developed to deal with mearged cells and nested tables
                          // Sold AS IS ;-)
                          // By Trevor www.creative-scripts.com (coming sometime) Custom and Readymade scripts for Adobe Indesign and other products.
                          
                          exportTable (/* Leave blank for first table in document or specify your table here*/)
                          
                          function exportTable (myTable) {// Thanks Trevor :-) http://forums.adobe.com/thread/1387437?tstart=0
                                  var doc = app.properties.activeDocument && app.activeDocument,
                                         myTable = myTable || getTable (doc);
                                  if (!myTable) {alert ("Take a break"); exit();};
                                  var  numberOfRows = myTable.rows.length,
                                         rowNumber, columnNumber,
                          
                                         rowContents = [],
                                         setRange, openMark, closeMark;
                          
                                  if ($.os.match(/Mac/i))
                                      {
                                          setRange = 'set value of range "A';
                                          openMark = '" to {';
                                          closeMark = '}';
                                      }
                                  else
                                      {
                                          setRange = 'app.Range("A';
                                          openMark = '") = Array(';
                                          closeMark = ')';
                                      }
                          
                                  for (var z = 0, rowNumber = 0; rowNumber < numberOfRows; rowNumber++) {
                                      var  numberOfColumns = myTable.rows[rowNumber].columns.length,
                                              toRange = GetExcelColumnName (numberOfColumns - 1),
                                              columnContents = [];
                                       for (columnNumber = 0; columnNumber < numberOfColumns; columnNumber++) {
                                           var cellContents = myTable.rows[rowNumber].cells.everyItem().contents;
                                           columnContents  = '"' + cellContents.join('", "') + '"';
                                       }
                                      rowContents[rowNumber] = setRange + ++z  + ":"  + toRange+ z + openMark  + columnContents + closeMark;
                                  }
                          
                             var tableData = rowContents.join("\r");
                          
                             if ($.os.match(/Mac/i))
                                  {
                                      // Thanks Hans http://forums.adobe.com/message/5610204#5610204
                                     myAppleScript = ['tell application "Microsoft Excel"',
                                                               'set theWorkbook to make new workbook',
                                                               'tell active sheet of theWorkbook',
                                                               tableData,
                                                               'end tell',
                                                               'end tell\r'].join("\r");
                                     app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE);
                                                     // alert (myAppleScript); exit() for Debug
                                  }
                              else
                                  {
                                      // Thanks Calos http://forums.adobe.com/message/5610204#5610204
                                       var vbscript = [
                                                              '''Err.Clear
                                                              On Error Resume Next
                                                              set app = GetObject(,"Excel.Application")
                                                              If (Err.number <> 0) Then
                                                              Set app = CreateObject("Excel.Application")
                                                              End If
                                                              app.visible = true'
                                                              set newDoc = app.Workbooks.Add
                                                              ''',
                                                              tableData,
                                                              ''''set newDoc = nothing
                                                              set app = nothing
                                                              '''
                                                              ];
                                                           // alert (vbscript); exit() for Debug                            
                          
                                  var vbfile = File(Folder.temp  +"/createXLSfile.vbs");
                                  vbfile.open('w');
                                  vbfile.write(vbscript.join('\r'));
                                  vbfile.close();
                                  vbfile.execute();
                                  $.sleep(750);
                                  vbfile.remove();
                                  }
                          }
                          
                          
                          
                          function GetExcelColumnName (columnNumber) {// 0 is A 25 is Z 26 is AA etc.
                              // parsed from http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa
                               var dividend = columnNumber + 1,
                                      columnName = "",
                                      modulo;
                          
                              while (dividend > 0)  {
                                  modulo = (dividend - 1) % 26;
                                  columnName = String.fromCharCode (65 + modulo) + columnName;
                                  dividend = Math.floor((dividend - modulo) / 26);
                              } 
                              return columnName;
                          }
                          
                          
                          function getTable (doc) { // thanks Marc http://forums.adobe.com/message/6087322#6087322
                              if (!doc) return false;
                              app.findTextPreferences = null;
                              app.findTextPreferences.findWhat = "\x16";
                              var tables = doc.findText();
                              if (tables.length) return tables[0].parentStory.tables[0];
                              return false;
                          };
                          
                          
                          • 10. Re: Exporting data in tables to an Excel spreadsheet?
                            mpc888 Level 1

                            Trevor, this would be a great help to me. I have an InDesign CS6 catalog with over 100 tables that we want to export to an excel spreadsheet. The format of each table is identical. Two columns, the left column in the legend and the right is the variable data.

                             

                            I copied your script into a text editor and saved as an applescript. When I try to run it, it immediately returns an error -2740 (see image attached). I've tried eliminating the first few green lines so it starts with "export table" but without success. Looking at the script in Applescript Editor seems to show a problem with the forward slashes (when I hit "Compile"

                            applescript error.jpg

                            I know nothing about writing or editing scripts. Can you help?

                            • 11. Re: Exporting data in tables to an Excel spreadsheet?
                              Trevorׅ Adobe Community Professional

                              this is not and applescript it's a .jsx script

                              copy it to a text editor like you did and save it with a .jsx extension.

                              Put it in the scripts folder which you can find by opening the scripts panel in indesign and right click on the "user" folder then click reveal in explorer and put the script in that folder

                               

                              Just a small correction on what I wrote about the warnings one get's if saved as a csv file.  A csv file can be created without resulting in warnings

                               

                              HTH

                               

                              Trevor

                              • 12. Re: Exporting data in tables to an Excel spreadsheet?
                                mpc888 Level 1

                                Thanks for the quick response. I thought it was an applescript because of the word "applescript" within it. Now I'm running it as a jsx. However in my real document with nothing selected, it selects only one table on page 3 to export. I created a new test document with three tables in separate text boxes on one page, and the script only exports the first one. I put the three tables in one big text box (one story), and still it only exports the first one. I tried selecting the second table in the story and ran the script again, but it still just exported the first table.

                                 

                                What I am hoping for is a script that will export every table to a single excel spreadsheet. Here is an example of the dummy tables with the legend in the first column and the data in the second column. I don't mind if the script exports the legend every time; I can repmove those columns in the excel file.

                                 

                                 

                                1

                                a

                                2

                                b

                                3

                                c

                                4

                                d

                                5

                                e

                                6

                                f

                                7

                                g

                                8

                                h

                                 

                                 

                                1

                                i

                                2

                                j

                                3

                                k

                                4

                                l

                                5

                                m

                                6

                                n

                                7

                                o

                                8

                                p

                                 

                                 

                                1

                                q

                                2

                                r

                                3

                                s

                                4

                                t

                                5

                                u

                                6

                                v

                                7

                                w

                                8

                                x

                                • 13. Re: Exporting data in tables to an Excel spreadsheet?
                                  Trevorׅ Adobe Community Professional

                                  I think you didn't read the script that carefuly

                                   

                                  Change the line (towards the begining)

                                   

                                  exportTable (/* Leave blank for first table in document or specify your table here*/)

                                   

                                  to

                                   

                                   

                                  var doc = app.properties.activeDocument && app.activeDocument,
                                        myTables = getTable(doc),
                                        n = myTables && myTables.length;
                                  
                                  while (n--) exportTable (myTables[n].tables[0]);
                                  

                                   

                                  and the the lines (towards the end)

                                   

                                  var tables = doc.findText();
                                      if (tables.length) return tables[0].parentStory.tables[0];

                                   

                                  to

                                   

                                  var tables = doc.findText(true);
                                  if (tables.length) return tables;
                                  

                                   

                                   

                                  note that the script will take quite some time to process 100 tables

                                   

                                  Enjoy

                                   

                                  Message was edited by: Trevorׅ I see that Jives is playing up today and making the letter l look like the number 1 so I changed the l's to n's

                                  • 14. Re: Exporting data in tables to an Excel spreadsheet?
                                    mpc888 Level 1

                                    Thank you! I didn't "read" the script at all since I don't understand the syntax . The word "applescript" just popped out to my eye!

                                     

                                    I'll try the revisions tomorrow and let you know. Thanks for your help.

                                    • 15. Re: Exporting data in tables to an Excel spreadsheet?
                                      Trevorׅ Adobe Community Professional

                                      I was talking aobut this line

                                      exportTable (/* Leave blank for first table in document or specify your table here*/)

                                      The script does use vb or applescipt depending on the os but it's in js.

                                      • 16. Re: Exporting data in tables to an Excel spreadsheet?
                                        mpc888 Level 1

                                        Hi Trevor,

                                        I made the changes to the script and it does indeed work fine. I'm very happy to have it, I'm sure I'll use it in the future, and I appreciate your help.

                                         

                                        It turns out it won't solve my current problem. I need to get the data out of the InDesign tables into a single Excel spreadsheet - they want an Excel database of the table entries that they can sort or search key words. I currently have 63 tables and the script gives me 63 Excel files. I can copy manually from InDesign and paste into one Excel sheet faster than dealing with all those separate files.

                                         

                                        But I learned something in the process. Thanks again for your help.

                                        • 17. Re: Exporting data in tables to an Excel spreadsheet?
                                          claired97144388

                                          Hi

                                          I have two computers. This script was working on one computer but not the other but now the computer it was working on is coming up with this error (picture below).

                                          I have a 30 page document. Table data on each page. I need to extract each table from every page into 1 excel file. Can anyone help me.

                                          Screen Shot Error.jpg

                                          • 18. Re: Exporting data in tables to an Excel spreadsheet?
                                            Trevorׅ Adobe Community Professional

                                            Weird message!

                                             

                                            Try the following.

                                             

                                            Delete lines 57 and 58 (as per your error message)

                                            app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE); 

                                            // alert (myAppleScript); exit() for Debug

                                             

                                             

                                            Add theses lines in their place

                                             

                                            var w = new Window('dialog'),

                                              e = w.add('edittext', undefined, myAppleScript, {multiline: true, readonly: true});

                                            e.characters = 120;

                                            w.show();

                                            exit();


                                            Save the script as Exports SIMPLE tables as a proper excel file DEBUG.jsx

                                             

                                            Run the script and your get some window like this

                                            Screen Shot 2015-08-19 at 14.26.10.png

                                            Copy the contents and paste it into the applescript editor.

                                            Run the applescript from the applescript editor and you can see what message comes up.

                                             

                                            You can post the applescript that you pasted in the applescript editor over here and a screenshot of the message you get.

                                            Maybe someone will be able to help you.

                                             

                                            HTH

                                             

                                            Trevor

                                            • 19. Re: Exporting data in tables to an Excel spreadsheet?
                                              Vamitul Level 4

                                              Hi Trev,

                                              Before sending the cell's contents to applescript make sure you encode the special characters in it. Claired97144388 probably has some quotes inside the table's cells and those screw up your as script. I learned that one the hard way

                                              • 20. Re: Exporting data in tables to an Excel spreadsheet?
                                                stephan_0815

                                                Hey Trevor,

                                                 

                                                thanks a lot – your script is amazingly cool

                                                But one question: If I have thin spaces inside my InDesign-Document,

                                                and alert myAppleScript, these spaces seem to be okay …

                                                After the AppleScript ran, Excel shows them as "<2009>" (which I guess, is a translation of it’s Unicode Position) …

                                                There are some characters with similar behaviour – someone an idea, how to solve this?

                                                 

                                                Thanx

                                                Jonas

                                                • 21. Re: Exporting data in tables to an Excel spreadsheet?
                                                  Trevorׅ Adobe Community Professional

                                                  Hi Jonas,

                                                   

                                                  Thanks for the compliment.

                                                  To replace thin spaces with regular add the line

                                                  tableData = tableData.replace(/\u2009/g," ");

                                                  After the line var tableData = rowContents.join("\r");


                                                  If you need need to keep the thin spaces as thin spaces in the excel file then that's a bit more complicated and you can contact me here Contact | Creative-Scripts.com


                                                  HTH


                                                  Trevor

                                                  • 22. Re: Exporting data in tables to an Excel spreadsheet?
                                                    stephan_0815 Level 1

                                                    Hi Trevor,

                                                     

                                                    thanks for your helpful and fast reply …

                                                    I had another idea before: parsing tableData and a variable for the sheetName as parameters to the Applescript …

                                                             var myParameters = [tableData, "Achtel geviert"]; ////there is a thin space included in "Achtel geviert"

                                                              myAppleScript = [

                                                                                        'tell application "Microsoft Excel"',

                                                                                        'tell active workbook',

                                                                                        'set sheetName to item 2 of arguments',

                                                                                        'make new worksheet with properties {name:sheetName} at end', ////there is now a thin space in the name of the worksheet

                                                                                        'set mytableData to item 1 of arguments',

                                                                                        'tell active sheet',

                                                                                        'display dialog mytableData', // thin spaces and the AppleScriptLines look fine in the dialog (see below)

                                                                                        'mytableData', ///// mytableData data isn’t executed by the Applescript

                                                                                        'end tell',

                                                                                        'end tell',  

                                                                                        'end tell\r'].join("\r");

                                                            app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE, myParameters);

                                                            }  

                                                    mytableData isn’t executed – even it looks good in the dialog

                                                    set value of range "A1:D1" to {"Achtel geviert", "2", "3", "4"}

                                                    set value of range "A2:D2" to {"Achtelgeviert", "", "", ""}

                                                    set value of range "A3:D3" to {"", "", "", ""}

                                                    set value of range "A4:D4" to {"", "", "", ""}

                                                     

                                                    Thanks & greetz

                                                    Jonas

                                                    • 23. Re: Exporting data in tables to an Excel spreadsheet?
                                                      Trevorׅ Adobe Community Professional

                                                      Hi Jonas

                                                       

                                                      I don't have time to work on this at the moment (at least on the freebie side) the office API's can be very problematic when it come to Unicode, they are still (haven't looked into 2016) based on  something like Windows 97 that didn't have Unicode support.

                                                      Either way I can't see the point of parsing arguments into the applescript when could just add the line set sheetName to "Achtel geviert" to the beginning of the myAppleScript array.  I think avoiding the arguments and a bit of googling should get you the answer.

                                                       

                                                      Post the answer when you get it.

                                                       

                                                      Regards

                                                       

                                                      Trevor

                                                      • 24. Re: Exporting data in tables to an Excel spreadsheet?
                                                        Trevorׅ Adobe Community Professional

                                                        P.s. try pasting the outputted applescript in the applescript editor and running it from there.