Exporting data in tables to an Excel spreadsheet?

Report · Jan 23, 2014

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.

Report · Jan 27, 2014

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

Report · Feb 03, 2014

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)

Report · Feb 03, 2014

@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_TYPE	Exports to EPS format.	1952400720 = 't_EP'
ExportFormat.EPUB	Exports to EPub format.	1701868898 = 'epub'
ExportFormat.HTML	Exports to XHTML format.	1213484364 = 'HTML'
ExportFormat.INCOPY_MARKUP	Exports to InCopy markup (ICML) format.	1768123756 = 'icml'
ExportFormat.INDESIGN_MARKUP	Exports to InDesign markup (IDML) format.	1768189292 = 'idml'
ExportFormat.INDESIGN_SNIPPET	Exports to InDesign snippet (IDMS) format.	1936617588 = 'snpt'
ExportFormat.INTERACTIVE_PDF	Exports to Interactive PDF format.	1952409936 = 't_iP'
ExportFormat.JPG	Exports to JPEG format.	1246775072 = 'JPG '
ExportFormat.PACKAGED_XFL	Exports to packaged XFL format.	1702389356 = 'exfl'
ExportFormat.PDF_TYPE	Exports to PDF format.	1952403524 = 't_PD'
ExportFormat.PNG_FORMAT	Exports to PNG format.	1699761735 = 'ePNG'
ExportFormat.RTF	Exports to rich text format (RTF).	1381254688 = 'RTF '
ExportFormat.SWF	Exports to SWF format.	1702066022 = 'eswf'
ExportFormat.TAGGED_TEXT	Exports to a tagged text file with a TXT extension.	1416066168 = 'Tgtx'
ExportFormat.TEXT_TYPE	Exports to text (TXT) format.	1952412773 = 't_te'
ExportFormat.XML	Exports 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

2a. Copy/Paste that table to Word:

2b. Copy/paste to Excel:

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

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

Report · Feb 04, 2014

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...?

Report · Feb 04, 2014

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)

Report · Feb 05, 2014

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

Report · Feb 05, 2014

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.convertToText(";", "\n");

I have changed it to (:)

var myText = myTables.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!

Report · Feb 05, 2014

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)

Report · Feb 06, 2014

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

Report · Mar 04, 2014

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"

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

Report · Mar 04, 2014

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

Report · Mar 04, 2014

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

Report · Mar 04, 2014

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.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

Report · Mar 04, 2014

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.

Report · Mar 04, 2014

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.

Report · Mar 05, 2014

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.

Report · Oct 29, 2018

I realise this is an old thread.

Just wondered if there is a way to adapt this script so it produces one excel workbook for all the tables on the document?

I would find this very useful!

Thanks

Report · Aug 18, 2015

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.

Report · Aug 19, 2015

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

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

Report · Aug 19, 2015

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

Report · Nov 01, 2015

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

Report · Nov 01, 2015

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

Report · Nov 01, 2015

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\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

Report · Nov 01, 2015

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

Adobe Community

Exporting data in tables to an Excel spreadsheet?