13 Replies Latest reply on Aug 24, 2011 7:47 AM by John Hawkinson

    Populating Database HELP!

    e113nwood

      I am in the process of trying to populate a database with content that is in InDesign CS5. In order to populate the database the data needs to be in excel and then into the database. I am wondering, rather than cutting and pasting, is there a way to export from InDesign CS5 into excel? Any help on this issue would be greatly appreciated.

       

      Thanks.

        • 1. Re: Populating Database HELP!
          Peter Spier Most Valuable Participant (Moderator)

          How is the information formatted currently in InDesign? Tables, tabbed text, something else?

          • 2. Re: Populating Database HELP!
            Daniel Flavin Level 4

            Select/Highlight all the table data.

            Table > Convert Table to Text (Column seperator = Tab, Row seperator = paragraph)

            Select/Highlight all the resultant text

            Export > Text

            Open the resulting text file with Excel

            • 3. Re: Populating Database HELP!
              e113nwood Level 1

              The file currently has on one page a table holding content. The following pages are made up of a two column layout with text frames. All text in the entire document is formatted using paragraph and character styles. Does that help? Thanks.

              • 4. Re: Populating Database HELP!
                Peter Spier Most Valuable Participant (Moderator)

                And what part of that do you want to move into Excel? Daniel has provided some basics for the table data.

                • 5. Re: Populating Database HELP!
                  e113nwood Level 1

                  To give some more background we are using multiple pages with alot of content.

                   

                  Think 36 weeks of schedules (tables) organized by week and day on one page..... followed by 2 text frames (or more) worth of content on the following pages.

                   

                  To get as much of the information as possible over into excel with out doing to much manual work.

                   

                  I tested Daniel method but that works for tables. What about the text frames of content? Is what we are trying to accomplish going to require a custom script to be built?

                  • 6. Re: Populating Database HELP!
                    Peter Spier Most Valuable Participant (Moderator)

                    You really need to provide a lot more information about the content in these other frames. Excel is perfectly happy to open an ordinary text file as delimited text with each paragraph being a cell in one column. So exporting the entire document to text is a possibility.

                    • 7. Re: Populating Database HELP!
                      Joel Cherney Adobe Community Professional & MVP

                      You really need to provide a lot more information about the content in these other frames. Excel is perfectly happy to open an ordinary text file as delimited text with each paragraph being a cell in one column. So exporting the entire document to text is a possibility.

                       

                      I completely agree, although I'd add the caveat that heterogenous data requires more pre-processing than homogenous data. If all you need to do is some table-conversion along the lines of Daniel's suggestion, and then just export the whole thing all at once as text, then go for it. Otherwise, we can't guess because we're not looking at your data and have no idea how many different kinds of pre-processing work will be necessary.

                      • 8. Re: Populating Database HELP!
                        John Hawkinson Level 5

                        What everyone else said, but also:

                        I am wondering, rather than cutting and pasting, is there a way to export from InDesign CS5 into excel?

                        What's wrong with cutting and pasting?

                        (This is a serious question, not a rhetorical one. Please answer it. It will help us help you.)

                         

                        You should be able to do the entire table in one operation, and it should move, cell-for-cell.

                        • 9. Re: Populating Database HELP!
                          e113nwood Level 1

                          There is nothing wrong with the copy and paste method-I know that that is a solution, I am just looking to see if there is a faster solution for transferring data into excel from InDesign. The amount of data that needs to be moved to excel is massive. To assist you all with what the files actually look like I have attached a link to a pdf. This link: http://www.sonlight.com/uploads/3-week-samples/core/ACG-3-weeksample.pdf will take you to a sample pdf of the content I am referring to. We don't need to preserve the paragraph and character styles, just the content! The key pieces that are needed are: the schedule pages (pp. 2-3) and the notes that follow (pp. 3-7) and any other content that can be moved as well is great. Hopefully this will provide you with a better understanding of what I am are trying to get transferred into excel from InDesign. Please do keep in mind that this is only a sample of the content. The entire instructor's guide document is a couple hundred pages long, hence why we are trying to avoid copying and pasting. I do appreciate all the assistance with this issue.

                           

                          Thanks

                          • 10. Re: Populating Database HELP!
                            Joel Cherney Adobe Community Professional & MVP

                            I work in the translation business, so I see this situation comparatively often. ("We have hundreds of pages of content in InDesign... the only format it's in, outside of InDesign, is in a thousand email attachments... how do we get it out of InDesign?") If you have the tools available to you, and you probably don't, my suggestion would be to make a translation memory of the English against itself.  E.g. InDesign document -> export IDML for translation memory tool -> translation memory database -> export XLIFF -> use something like Swordfish to export .csv -> Excel.

                             

                            That's my solution because I'm a language industry nerd who doesn't have the time (or skills!) to write the XSL to transform the IDML into another format more amenable to my needs. I bet that you don't, either. That means that you'd need some other way to do it. I don't see why this won't work for you:

                             

                            1) Use a plugin to connect all of the frames in the whole document

                            2) Export raw text

                            3) Open raw text in Excel

                             

                            That will give you some very messy output, but it will get all of your data out of ID and into Excel.

                            • 11. Re: Populating Database HELP!
                              John Hawkinson Level 5

                              Hi, E113n:

                               

                              It looks like your posts are a bit it conflict -- are you moving data from Excel to InDesign, or InDesign to Excel, or both?

                              They have different parameters.

                               

                              Honestly, I would probably cut-and-paste once per table. Even if you have a hundred tables, 100 pastes isn't that bad.

                              But a big problem is how you manipulate it in Excel.

                               

                              If you're up for scripting you could certainly write a script to export each table to a seperate Excel file, or even one big Excel file. Or, I suppose, we could script combining all tables into one big mammoth table that can be cut and pasted en masse. That would probably be pretty easy.

                               

                              I think the hard part is getting this back into InDesign. Are you going to be using Data Merge? Or is Excel -> InDesign not part of the spec?

                               

                              Please do clarify which way you're going and how....

                              • 12. Re: Populating Database HELP!
                                e113nwood Level 1

                                John

                                 

                                In response to your questions: "Are you going to be using Data Merge? Or is Excel -> InDesign not part of the spec?"

                                 

                                Yes I believe we have access to data merge since we are using CS5. We haven't looked into that fully at this time. Excel to InDesign is not needed.

                                 

                                After futher discussion on this topic I believe that the we would have to xml tag up our InDesign documents. Then after that is complete write a scripts to pull the content we want at specific times. Either that or copy and paste thousands of pages of content. Do you believe this is correct?

                                 

                                Thanks

                                • 13. Re: Populating Database HELP!
                                  John Hawkinson Level 5
                                  Yes I believe we have access to data merge since we are using CS5. We haven't looked into that fully at this time. Excel to InDesign is not needed.

                                  Please note, Data Merge is a technology for importing spreadsheets and repeating data into InDesign. So if Excel > InDesign is not needed, Data Merge is not relevant.

                                   

                                  After futher discussion on this topic I believe that the we would have to xml tag up our InDesign documents. Then after that is complete write a scripts to pull the content we want at specific times. Either that or copy and paste thousands of pages of content. Do you believe this is correct?

                                  No.

                                   

                                  It is of  course, complicated but there are a lot of options. If you choose to use InDesign's XML tagging, then you will have to ensure that every piece of content you want to export is tagged. If everything is already tagged with Paragraph Styles and those styles have a clear mapping to the XML tags you want, then this is trivial and takes five minutes. If nothing is tagged with styles, or if the mapping is not 1:1, then you may have to touch every single word, and it may be worse than cutting-and-pasting thousands of pages of content.

                                   

                                  And again, there are other options. You can merge all the tables together with scripting. If this meets your needs, we can help you with this readily. You can export the entire document to IDML, an XML format that is structured with the InDesign document object model, and then transform or translate it into a different XML format. This is probably a lot of work for a programmer, but could be done; it would only have value if it was going to handle a lot of future cases.

                                   

                                  There are also the options that Joel mentioned.

                                   

                                  Also, if  your content is a story that flows across multiple pages, there is no need to cut and paste page-by-page. You can go story-by-story. Of course, this may depend on how you want the information structured in Excel.

                                   

                                  (Maybe your conclusion of the above is that XML tagging is the only practical option. I would tend to doubt that, but I guess it is possible. It really depends a lot on your situation.)