Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
The simplest solution is probably to save the table contents as a csv files and open that in Excel.
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
@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
Copy link to clipboard
Copied
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...?
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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);
}
}
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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;
};
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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 |
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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