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

[Username1]

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!

[JimmyHartington]

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.

[JimmyHartington]

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

[Username1]

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:

The excel looks like this:

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

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

[JimmyHartington]

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?

[Username1]

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!

[JimmyHartington]

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:

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

[Username1]

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.

[lwdesign13]

Afternoon. I stumbled across this and managed to get the first script to work adn extract the data.

My Indesign file is much more involved through. I would need help to get the content into columns so that it can be imported into something like WooCommerce.

Can you help?

[JimmyHartington]

Can you describe or upload images of what you have and what you need?

[lwdesign13]

Hello Jimmy,

I have a menu I produce monthly, via Indesign. See Low res pdf attached.

I extracted the new menu the data with your script. - Attached as MyData2.csv - All the content in single rows.

I’d like to be able to extract them as rows and columns, as per the attached

app4-scraped-data-with-images csv file,

to import into WooCommerce and to be able to update future menus, possible by updating the spreadsheet then importing the data back into Indesign.

Do you think this is possible?

See attached, with thanks.

Lee Wayland
BA (Hons) Illustration
LW Design

tel: 01306 876203 mobile: 07720 401492

Email: lee@lwdesign.co.uk
Visit http://www.lwdesign.co.uk

Social Media - Facebook: LW Design Ltd | Dorking

Our Terms of Business are on the LW design website

6 South Street, Dorking, Surrey, RH4 2EL

This email and any attachments are intended for the named recipient.
If you are not the intended recipient, please delete this email and all attachments, advising me of the error.
Thank you.

(Attachment myData2.xlsx is missing)

(Attachment app4-scraped-data-with-images.csv is missing)

(Attachment Mamuska-8pp-menu-June-2024-09.pdf is missing)

[lwdesign13]

Hi Jimmy

My email looks like it was rejected due to the file types.

How can I get these to you?

Lee Wayland
BA (Hons) Illustration
LW Design

tel: 01306 876203 mobile: 07720 401492

Email: lee@lwdesign.co.uk
Visit http://www.lwdesign.co.uk

Social Media - Facebook: LW Design Ltd | Dorking

Our Terms of Business are on the LW design website

6 South Street, Dorking, Surrey, RH4 2EL

This email and any attachments are intended for the named recipient.
If you are not the intended recipient, please delete this email and all attachments, advising me of the error.
Thank you.

[JimmyHartington]

You can log into the forum via a browser and attached at first screenshots of the InDesign page, and the Excel fil you would like.

[lwdesign13]

Thanks Jimmy

Screenshot of one of the pages - Drinks Menu, plus screenshot of the spreadsheet columns and rows.

[Airy]

@lwdesign13

Screenshot of one of the pages

I have a totally different approach. Naturally, you can reject my totally different approach and that's fine.

My approach is to take a macro that extracts the text from any image that appears in the clipboard, and configure the macOS Snapshot utility to place its results into the clipboard. I actually am running such a macro right now. Any time I take a screenshot, I have a macro that reads the text in the image and displays the text on the screen, using the KM LARGE DISPLAY feature. Yes, it's very annoying to see my screen fill with text from an image that I copy! But it works. I think it could work for you too.

Your pages are very busy/complicated. So what I would do in your situation is expect your user (which is probably you yourself) to take individual screenshots for each "block of text" in that image, and then concatenate all the OCR text results into a variable, which you can then paste into a spreadsheet at the end.

Here's my macro:

You would need to change that macro's last action from "Display Results Large" to "Append Results to Variable MyVar". (You should probably append an extra two "Returns" right after that action.)

Anyway, that's another approach. It could use some fine tuning, but since this thread is "solved" I won't bother with any fine tuning. I've been using this approach for many months and it works well for my purposes.

Even though your screenshot is very tiny on this page, I tried using my macro on your image, and the results were surprisingly usable. Here were the first few lines of results:

Drinks Menu

*MEANS ALCOHOL FREE | All spirits are 40% ab unless stated otherwise

BEER BUCKETS

SUMMER DEALS....... From £24.00

GOSCISZEWO CRAFT:

Surfer, Komtur, Szeryf & Rycerz.....

£24.00

MAIN STREET:

Namystoow, Perta Miodowa, Zywiec Biate,

Lech & Zubr......

£30.00

AMBER MICROS:

APA, Pszeniczniak, Joannes, Kozlak....... €30.00

Yes, it would take a little more work to have these items pasted into a spreadsheet correctly, but a little more coding could probably address some of that.