Designers! Is there a way to get indesign text into excel using Keyboard Maestro?

Questions & Suggestions
,
1

Would there be a way to automate taking all the text from an indesign document and putting it into an excel sheet, where each (unthreaded) text box in InDesign would have its own cell in the spreadsheet?

I basically have 1000s of pages across around 50 different documents and need to create a master excel/sheets document from all the text in indesign.

What I actually need is some sort of reverse data merge but failing that I'm wondering if I could set up actions to copy and paste from indesign to excel. OR from a word doc to excel, because I know I can export the text to a word doc from Indesign but not sure how it breaks up text boxes or pages or how that would then translate to being copied and pasted into excel cells...

TIA!

2

Are you able to supply a sample of a InDesign document?
Both InDesign and Excel can be controlled with AppleScript, so this should be possible.

3

The code below will export the content of each textframe to a separate record in a csv-file.
It is a JavaScript for InDesign.
You can copy it out and save it as a .jsx file.
And follow the instructions here How to install scripts in InDesign | CreativePro Network to install it.
I tested it on a document with 3 text frames, so I do not know how it handles a very big document.
So perhaps test with a small document first.

var doc = app.activeDocument;  
var textFrames = doc.textFrames.everyItem().getElements();
var csvContent = "";

for(var i = 0; i < textFrames.length; i++){
    var textFrame = textFrames[i];
    csvContent += "\"" + textFrame.contents.replace(/"/g, '""') +"\"\n";     
}


var myFile = File("~/Desktop/myData.csv");  
if ( myFile !== false ){  
    myFile.open( 'w' );
    myFile.encoding = 'UTF-8'; // Set the encoding to UTF-8
    myFile.write(csvContent);  
    myFile.close();  
}
2 Likes
4

Hi Jimmy! Sorry I thought I had responded to this a month ago to say thanks and I would give it a try when I had some time spare.

This is amazing, thank you! Absolute genius!!! I searched forever for something like this and everywhere I looked I found that it simply couldn't be done. Thank you.

I'm not really a coder/scripter, so this may be asking too much, (and I don't want to seem ungrateful for the script you did already supply), but is there a way to control where it imports the information in the excel? For example so you could data merge the info back into indesign? At the moment all the data is in a single column but the script does recognise the order of the boxes on the page and the text is imported into excel in the same order for each page.

For example if each page looks like this:

image
image1576×2266 309 KB

The excel looks like this:
image

But it would obviously need to be like this for data merge
image

If not, this will still be crazy faster to copy paste into the right format from the excel vs copy and paste from each individual text box in indesign into excel, so thank you again!!!

5

Does the page in InDesign always have 5 entries, which should go into 5 columns as in your last screenshot?
Or is it possible for you to share a sample document with some pages?

6

Yeah, all the pages are identical in terms of layout, number of text boxes etc, it's just the info that changes on each one. I actually have 50 ish documents, some of them the pages have 5 entries on each page, most have 2 entries, title and copy, a few have 3 entries.

Not at my computer at the mo to upload an example but they're just like the screenshots but with a background and more formatting lol.

Thank you!

7

I can see I in the first post forgot to mention, that I used ChatGPT for this.

So I have tried again.

// This script exports content from the last five text frames on each page to a CSV file with each text frame's content in separate columns in reverse order.
// Line breaks within the content are escaped.

var csvFile = File.saveDialog("Save CSV File", "*.csv");
if (csvFile) {
    csvFile.open("w");
    csvFile.writeln("Frame 1,Frame 2,Frame 3,Frame 4,Frame 5,Page");

    var document = app.activeDocument;

    for (var pageIndex = 0; pageIndex < document.pages.length; pageIndex++) {
        var page = document.pages[pageIndex];

        var row = [pageIndex + 1];

        for (var frameIndex = Math.max(0, page.textFrames.length - 5); frameIndex < page.textFrames.length; frameIndex++) {
            var frame = page.textFrames[frameIndex];
            var content = frame.contents.replace(/"/g, '""').replace(/\r/g, '\\r').replace(/\n/g, '\\n'); // Escape double quotes and line breaks within content
            row.unshift('"' + content + '"'); // Insert at the beginning to reverse the order
        }

        // Fill remaining columns with empty strings if needed
        var emptyColumns = Math.max(0, 5 - row.length + 1);
        for (var j = 0; j < emptyColumns; j++) {
            row.unshift('""');
        }

        csvFile.writeln(row.join(","));
    }

    csvFile.close();
    alert("CSV export complete.");
}

This code takes a document like this:

image
image1286×1944 108 KB

And produces this output:

Frame 1,Frame 2,Frame 3,Frame 4,Frame 5,Page
"TitleA","Info 1A","Info 2A","Info 3A","CopyA copyA copyA",1
"TitleB","Info 1B","Info 2B","Info 3B","CopyB copyB copyB",2
"TitleC","Info 1C","Info 2C","Info 3C","CopyC copyC copyC",3
1 Like
8

Absolutely genius idea!!! Thank you so much, this workes amazingly. You literally just turned 1000s of tedious work hours into a fraction of the time. :heart: :exploding_head:

1 Like