18 Replies Latest reply on Jun 13, 2016 9:04 AM by Scott_CHS

    How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode

    roberth1959

      Can you please advise if the following process is possible within InDesign CS 5.5? (Mac OS 10.8.2).

       

      We need to create a data merge that includes a barcode. The data merge is for several thousand records.

       

      We have a customer-supplied Excel spreadsheet that contains seven fields, one of which is a number that ultimately needs to print as a barcode when we make the data merge in InDesign.

       

      As you would expect, the conventional six fields data merge perfectly - but we can't find a way to make the barcode print correctly within the data merge.

       

      We have investigated several possible solutions without success.

       

      Some solutions suggest a plug-in for Excel to convert the number into a barcode whilst still in the spreadsheet - but this doesn't come across correctly when we import the CSV file into the InDesign data merge.

       

      We have investigated certain third party software applications but none provide the full solution we require. Some, for example, appear to create the barcode within the Excel spreadsheet but don't produce the barcode correctly when we data merge in InDesign; and some will allow us to create a one-off barcode within InDesign but have no automated way of retrieving the thousands of Excel numbers and turning them into barcodes.

       

      We have also come across possible 'font' solutions but have had no success with these either.

       

      For a previous job we needed to produce we outsourced to a specialist mailing company, who assured us they produced the whole job (very quickly) within InDesign. They, understandably, won't tell us how they did it. But, given the speed with which they produced the job for us, we imagine - with the correct software - the whole process is easy to do.

       

      Can you please advise?

        • 1. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
          MW Design Level 4

          If there is a font that can represent your barcode number as it is entered into your spreadsheet properly, it is no different than any other merge. Else the number format string needs amended to accomodate what the font requires.

           

          What type of barcode (128, 39, etc), and how is it presented in the spreadsheet?

           

          In ID, you can format the number as a paragraph or character style applying the correct font for the barcode type.

           

          Mike

          • 2. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
            roberth1959 Level 1

            Mike, thanks for your reply.

             

            I appreciate what you say about barcode fonts but we've never had any success with them - either in the native Excel file or during the data merge in InDesign.

             

            The barcode we're ultimately trying to produce is Code 39.

             

            The number which ultimately needs to end up as the barcode is presented as a six-digit number within its own field in the Excel file.

             

            The Excel file also has additional fields containing normal text and these (as you would expect) data merge perfectly in InDesign. It's just the barcode field that we can't get to data merge as a barcode.

             

            The Excel file is supplied to us from our customer so we have no input in setting it up.

             

            Thanks again for your help.

             

            Rob

            • 3. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
              MW Design Level 4

              Hello Rob.

               

              Can you give an example of a number as it appears in the merge file, perhaps a single row with made up data for the other info, but the number as is?

               

              Likely you will need to pad the number in accordance to the font requirements. Some need characters pre and post number, etc. But an example would be helpful.

               

              Mike

              • 4. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                Jeffrey_Smith Most Valuable Participant

                Code 39 barcodes have start and end digits which usually are asterisks. For example, data that reads 123456, to successfully scan a barcode, the data would look like this *123456*  If this is the case, it would be an easy task to include asterisks into the data.

                • 5. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                  roberth1959 Level 1

                  FORUM COMBINED INDD EXAMPLE.png

                   

                  HI MIKE

                   

                  Thanks again for your reply. I hope the screenshot above helps. Left side shows the data merge placeholders; right side shows the live info when imported from the CSV file, with an arrow showing the six-digit number that needs to print as a barcode.

                   

                  Hope this helps. And thanks again.

                   

                  Rob

                  • 6. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                    SJRiegel Adobe Community Professional & MVP

                    My guess is that your printer used a solution like this one:

                    http://www.barcodemaking.com/barcode-automation-in-adobe-indesign.html

                     

                    In short, using barcode software to create all the bar codes as images, and then use an image field in the data merge to bring them into the document.

                    • 7. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                      MW Design Level 4

                      Hi Rob--just the text pasted into a message of a row would have been good.

                       

                      Like this zoomed in text?

                       

                      capture-002689.png

                       

                      You simply need to put an asterisk before/after the number in most all cases. Then set the paragraph or character style to use a proper code 39 font. It's easy enough to modify the Excel file. If you need assistance, let us know. But in short, I usually insert a column before and after the barcode number, fill those columns with an asterisk, export to a tab delimited .txt file for the merge, open the .txt file in a good text editor, search and replace for the literal asterisk followed by a tab character, remove the tab (and keep the asterisk, then do another s/r for the tab character followed by an asterisk, remove the the tab (and keep the asterisk). Save the file and it is ready for merging.

                       

                      Mike

                      • 8. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                        roberth1959 Level 1

                        HI MIKE

                         

                        Thanks for your reply. Sorry about the misunderstanding regarding the screenshots!

                         

                        Thanks for your info - that sounds really straightforward and I understand the process you describe.

                         

                        Please excuse my ignorance but do you know where we can access or purchase a 'proper code 39 font'? We've downloaded various fonts purporting to be code 39 and also various 'barcode fonts' but we've had no success with those.

                         

                        Also, do you apply the code 39 font to the barcode column in Excel? Or leave that column as is and only apply the code 39 font during the data merge in ID? From your comment, I'm suspecting the latter?

                         

                        And a final question - if all the above works, does the final printed barcode end up with a physical printed asterisk at its start and end or not? (as your screenshot).

                         

                        Thanks again for all your help with this.

                         

                        Kind regards

                         

                        Rob

                        • 9. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                          roberth1959 Level 1

                          Thanks for your reply. That way of creating/merging the barcodes is something we're investigating too.

                           

                          Kind regards

                           

                          Rob

                          • 10. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                            Jeffrey_Smith Most Valuable Participant

                            The barcode font is applied in the Indesign layout, not in Excel.

                             

                            My initial assumption with your statement regarding barcode fonts not working was due to the possibility that you were not including all the proper info into the barcode (like a start/end digit). I use barcode fonts all the time, and confirm with a scanner that they work. It really depends on the kind of barcode you are using, and whether a barcode application is needed to generate more specific data like a control digit. In the case of a code 39, a font can be used without issue.

                             

                            The Asterisks are used as start and end digits so scanners can read the barcode correctly. If they are not in the barcode, the scanner will not read the data properly.


                            It doesn't affect the human readable portion if the Asterisks are there or not, it's more of a preference.

                             

                            • 11. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                              MW Design Level 4

                              Hi Rob,

                               

                              Reference to proper is simply to say that oft times free off-the-web fonts may not always be a quality product. One I do know of that works with the scanners is from ID Automation, from which I have some paid-for fonts from:

                               

                              Free Medium-Size Code 39 Font

                               

                              Yes, one only applies the font in InDesign, not in Excel. If the code is in its own paragraph, use a paragraph style. If it is mixed into another paragraph that will use a different font, use a character style using the barcode font and apply the character style to the placeholder for the barcode.

                               

                              The font above for a code 39 barcode requires control digits at the start and end. If you do not desire the asterisks, with fonts from ID Automatino (and others), you can enclose the digits in parenthesis like this (123456) and the control character are not used in the final output.

                               

                              Mike

                              • 12. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                roberth1959 Level 1

                                HI MIKE

                                 

                                Thanks again for your replies. We're getting somewhere but are coming up against a recurring problem in the text editor process you describe.

                                 

                                In Excel, I've created columns before and after the barcode number and filled those columns with an asterisk. I've then made a .txt file. So far so good.

                                 

                                But when I open the file in our text editor software (Mac default programme called TextEdit) I can't get the search and replace to work correctly. It appears to do the s/r for the asterisk column before the barcode number but your s/r commands for the column after seem to undo the first s/r process - and simply ends up stringing all three columns together in one field. Consequently when we then try to data merge in ID we simply get three columns stringing together and coming in as one field.

                                 

                                I'm hopeful that as this appears to be so close to what we ultimately need to achieve there is a simple fix here somewhere - and I'm sure it's to do with the text editor part.

                                 

                                Thanks as always for any info you can provide.

                                 

                                Rob

                                • 13. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                  roberth1959 Level 1

                                  HI MIKE

                                   

                                  Thanks again for your reply and your help with this.

                                   

                                  I've downloaded the barcode font from the link you supplied.

                                   

                                  I've tried three tests - and seem much closer to getting what we need. But I've come up against a couple of issues.

                                   

                                  TEST 1 - almost just to see if the downloaded font worked, I simply data merged our source csv file and applied the downloaded barcode font to the serial number in ID. Probably unsurprisingly, this produced a perfectly printed barcode - that wouldn't scan.

                                   

                                  TEST 2 - I manually added asterisks before and after the serial number in the first couple of fields and then tried the same process as TEST 1. It produced a perfectly printed barcode which this time DID scan correctly - but obviously the barcode had asterisks on it which I don't want. I just want the serial number. And with thousands of serial numbers we need the addition of the asterisks to happen automatically (which is where your text editor process seems the perfect solution, if we could get that to work).

                                   

                                  TEST 3 - Noting your comments about using parenthesis around the serial number, I followed your example of (123456). But Excel won't let me type a parenthesis. It lets me type the parenthesis but then instantly substitutes it with a - (hyphen) before the number in the actual serial number cell and no character at all after the number.

                                   

                                  If you have any suggestions or solutions for any of the above they'd be gratefully received! Thanks again for all your help with this.

                                   

                                  Kind regards

                                   

                                  Rob

                                  • 14. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                    MW Design Level 4

                                    Hi Rob,

                                     

                                    Excel will allow the typing of parentheses and keep them as such, if you select the column header for these cells (which highlights/selects the entire column), right-click, choose Format Cells, and change the format to Text.

                                     

                                    capture-002697.png

                                     

                                    But then, if i had a spreadsheet that already had the numbers, I would use the text editor approach on a tab delimited file and find/replace them in one go.

                                     

                                    Mike

                                    • 15. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                      roberth1959 Level 1

                                      HI MIKE

                                       

                                      Thanks for your reply. We think we've - tentatively - achieved success!

                                       

                                      A combination of the correct Barcode Font in ID and applying the parentheses in Excel seems to have done the trick. Although we also found a workaround regarding the text editor problems we were having.

                                       

                                      We've negated the text editor process completely (and therefore the search/replace we were struggling with) by merging the three columns in Excel (that's the parenthesis column before the serial number column, the serial number column itself, and the parenthesis column after the serial number, into one new merged column. This has had the very happy result of giving us (123456) - which we're then able to data merge successfully in ID (using the barcode font on that particular field). The barcode generated scans successfully and doesn't actually print any unwanted asterisks, parentheses etc, which we were struggling with earlier.

                                       

                                      Can't thank you enough for your help, without which we would have got nowhere near the above solution.

                                       

                                      Massive thanks for all your help. It's very very much appreciated.

                                       

                                      Kind regards

                                       

                                      Rob

                                      • 16. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                        MW Design Level 4

                                        You are most welcome, Rob. Glad you got it sorted out.

                                         

                                        Mike

                                        • 17. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                          roberth1959 Level 1

                                          HI JEFFREY

                                           

                                          Thanks for your reply and info. With Mike's replies and yours, we think we've reached a solution.

                                           

                                          Thanks again for your help. It's very much appreciated.

                                           

                                          Kind regards

                                           

                                          Rob

                                          • 18. Re: How do I generate a data merge in InDesign from an excel spreadsheet which includes a barcode
                                            Scott_CHS

                                            Rob,

                                            I have had great success with this by adding the asterisk into the data merge field directly, before & after the placeholder.  This eliminated the need for extra columns on the .csv side.