11 Replies Latest reply on Sep 12, 2013 10:42 AM by nepcoproducts

    How can I pull data from an attached excel/csv file?

    nepcoproducts

      I currently manage some price lists for the company I work for. They are graphical, and usually created in inDesign and then exported to pdf.

       

      I'm looking for a way to attach our company's prices in an excel sheet, (or delimited file) and be able to pull a price into the pdf.

       

      For example, attaching an excel file with 2 columns, SKU & Price. Then saying, if text field SKU= "APPLE" text field PRICE = "0.50". Only I don't want the user to have any inpurt options, I want to do this myself and then save a flat version to distribute in the company.

       

       

      Any ideas on how this could be accomplished (if it even could be?) I've been searching for the past week.

       

       

      Thank you in advance.

       

      (I have Acrobat X Pro)

        • 1. Re: How can I pull data from an attached excel/csv file?
          George_Johnson MVP & Adobe Community Professional

          Will the users be limited to Reader, or will they have Acrobat? If limited to Reader, the easiest is to attach the data file as an attachment and use the doc.getDataObjectContents method to read the data. It is shown as method #2 in the following tutorial: http://acrobatusers.com/tutorials/getting-external-data-into-acrobat-x-javascript

           

          You can also include data in JavaScript code that's included in the file, but it is a bit more difficult to update. Still, if you have to do it a lot you can create code to make it easy.

          1 person found this helpful
          • 2. Re: How can I pull data from an attached excel/csv file?
            nepcoproducts Level 1

            Thank you for your help, this seems to be the right direction.

             

            After I use doc.getDataObjectContents to take in the data, how can I tell it to send the price that corresponds to a certain SKU to a text field on the document?

            • 3. Re: How can I pull data from an attached excel/csv file?
              nepcoproducts Level 1

              I have come to this point:

               

              Through reseraching the function you gave me, I found another post of yours where you said you need to parse the data with Javascript afterwards.

               

              This is how I've come up with doing that:

               

              <script type="text/javascript">

               

               

              var oFile = this.getDataObjectContents("Price.xls");

              var strData = util.stringFromStream(oFile, "utf-8");

               

               

               

              function ParseData( strData ){

               

               

              var objCollection = {}

               

               

              strData.replace(

              new RegExp( "\\[(\\w+)=([^\\]]*)\\]", "gi" ),

              function( $0, $1, $2 ){

              objCollection[ $1 ] = $2;

              }

              );

               

               

              return( objCollection );

              }

               

               

              I'm really stuck as to where to go from here on pulling a specific SKU's corresponding price and outputting it to a text field. (If i even did this part right).

               

              Any more direction you could give me would be appreciated, I'm sorry to say that I'm really not overly familiar with Javascript and I'm just trying to work through it for this one job function.

              • 4. Re: How can I pull data from an attached excel/csv file?
                George_Johnson MVP & Adobe Community Professional

                That's the right approach. Assuming that the objCollection object is correctly populated with the data in the file attachment, you merely look up the price based on the SKU. For example:

                 

                // Custom Calculate script for price text field

                (function () {

                 

                    // Get the value of the SKU field

                    var sku = getField("SKU").valueAsString;

                 

                    // Look up the price

                    var price = objCollection[sku];

                 

                    // Set the value of this field to the price if it's present,

                    // otherwise blank this field

                    event.value = price || "";

                 

                })();

                 

                This code assumes that the objCollection variable is initialized when the document is opened, so the code you posted can go in a document-level JavaScript.

                1 person found this helpful
                • 5. Re: How can I pull data from an attached excel/csv file?
                  nepcoproducts Level 1

                  First, let me start off by saying thank you again for your help. I sincerely appreciate it.

                   

                  I have placed the following code into the document-level section of the javascript editor:

                   

                   

                  var oFile = this.getDataObjectContents("Price.xls");

                  var strData = util.stringFromStream(oFile, "utf-8");

                   

                   

                  function ParseData( strData ){

                   

                  var objCollection = {}

                   

                  strData.replace(

                  new RegExp( "\\[(\\w+)=([^\\]]*)\\]", "gi" ),

                  function( $0, $1, $2 ){

                  objCollection[ $1 ] = $2;

                  }

                  );

                   

                  return( objCollection );

                  }

                   

                   

                  // ---------------------------------------- //

                   

                   

                  // Custom Calculate script for price text field

                  (function () {

                   

                      // Get the value of the SKU field

                      var sku = getField("SKU").valueAsString;

                   

                      // Look up the price

                      var price = objCollection[sku];

                   

                      // Set the value of this field to the price if it's present,

                      // otherwise blank this field

                      event.value = price || "";

                   

                  })();

                   

                   

                  And I have created a field SKU, which I placed a valid SKU value into. I also created a blank field called event for the price to populate into, but I have a strong feeling that this was the wrong approach.

                   

                  At any rate, I've recieved the following error in the javascript debugger:

                   

                  ReferenceError: objCollection is not defined

                  89:Document-Level:ADBE::FileAttachmentsCompatibility

                   

                   

                  Any idea what I may be doing wrong?

                   

                  Thanks again.

                  • 6. Re: How can I pull data from an attached excel/csv file?
                    George_Johnson MVP & Adobe Community Professional

                    You're almost there. You created a function to parse the data, but you didn't call the function so that the code is executed. So change the code to:

                     

                     

                    var oFile = this.getDataObjectContents("Price.xls");

                    var strData = util.stringFromStream(oFile, "utf-8");

                     

                    function ParseData(sData){

                     

                    var oData = {};

                     

                    sData.replace(

                    new RegExp( "\\[(\\w+)=([^\\]]*)\\]", "gi" ),

                    function( $0, $1, $2 ){

                    oData[ $1 ] = $2;

                    }

                    );

                     

                    return( oData );

                    }

                     

                    // Parse the data into an object

                    var objCollection = ParseData(strData);

                     

                     

                     

                    Note that I changed some of the variable names to clarify the code.

                    1 person found this helpful
                    • 7. Re: How can I pull data from an attached excel/csv file?
                      nepcoproducts Level 1

                      Thank you again, that did fix my error problem and it makes complete sense.

                      Unfortunately, the price never shows up either. I've been playing with it here for a couple hours and I just don't see what I'm missing.

                       

                      I have the code as follows:

                       

                       

                       

                      var oFile = this.getDataObjectContents("Price.xls");

                      var strData = util.stringFromStream(oFile, "utf-8");

                       

                      function ParseData(sData){

                       

                      var oData = {};

                       

                      sData.replace(

                      new RegExp( "\\[(\\w+)=([^\\]]*)\\]", "gi" ),

                      function( $0, $1, $2 ){

                      oData[ $1 ] = $2;

                      }

                      );

                       

                      return( oData );

                      }

                       

                      // Parse the data into an object

                      var objCollection = ParseData(strData);

                       

                      // ---------------------------------------- //

                       

                       

                      // Custom Calculate script for price text field

                      (function () {

                       

                          // Get the value of the SKU field

                          var sku = getField("SKU").valueAsString;

                       

                          // Look up the price

                          var price = objCollection[sku];

                       

                          // Set the value of this field to the price if it's present,

                          // otherwise blank this field

                          event.value = price || "";

                       

                      })();

                       

                       

                      and I have two text fields, one with a valid SKU already set as the value, and a blank event text field.

                       

                      I'm banging my head over finding where this is going wrong.

                       

                      If it helps, this is the actual PDF.

                      http://nepcosignsupply.com/store/pc/catalog/test.pdf

                      • 8. Re: How can I pull data from an attached excel/csv file?
                        George_Johnson MVP & Adobe Community Professional

                        I haven't bothered to study that regular expression to see what it's supposed to do, but I would recommend saving the spreadsheet as a CSV (or better yet, tab delimited TXT), with just two columns, one for the SKU # and one for the price. It will be much easier to parse that way. Trying to parse an XLS with JavaScript is going to be difficult.

                        1 person found this helpful
                        • 9. Re: How can I pull data from an attached excel/csv file?
                          George_Johnson MVP & Adobe Community Professional

                          Also, there are close to 20,000 items in that spreadsheet. If they are all not needed, I would try to get rid of as many as you can.

                          • 10. Re: How can I pull data from an attached excel/csv file?
                            nepcoproducts Level 1

                            I updated to CSV, and removed some columns that may have been junking it up. If I can get this working, I will definitely modify my reports to have smaller, more relevant csv files.

                             

                            Currently, I'm still not getting any print out, and I'm now getting an undefined error on Prices. My code is essentially the same, except for the change to .csv.

                             

                             

                            I'm sorry to bother you with this so much, but you've been awesome helping me.

                             

                            Would you mind taking one more peek?

                             

                            http://nepcosignsupply.com/store/pc/catalog/test.pdf

                             

                            Thank you again.

                            • 11. Re: How can I pull data from an attached excel/csv file?
                              nepcoproducts Level 1

                              I have updated my code to look like this: (I think i was mismatching some variables before)

                               

                               

                              var oFile = this.getDataObjectContents("Price.csv");

                              var sData = util.stringFromStream(oFile, "utf-8");

                               

                              function ParseData(sData){

                               

                              var oData = {};

                               

                              sData.replace(

                              new RegExp( "\\[(\\w+)=([^\\]]*)\\]", "gi" ),

                              function( $0, $1, $2 ){

                              oData[ $1 ] = $2;

                              }

                              );

                               

                              return( oData );

                              }

                               

                              // Parse the data into an object

                              var oData = ParseData(sData);

                               

                              // ---------------------------------------- //

                               

                               

                              // Custom Calculate script for price text field

                              (function () {

                               

                                  // Get the value of the SKU field

                                  var sku = getField("SKU").valueAsString;

                               

                                  // Look up the price

                                  var price = oData[sku];

                               

                                  // Set the value of this field to the price if it's present,

                                  // otherwise blank this field

                                  event.value = price || "";

                                  console.println(price);

                               

                               

                              })();

                               

                              still with the two text fields on the form. one that i have filled in a valid SKU, and a blank one named event.

                               

                              I still seem to be getting an undefined error on prices. I feel like this is so close that it's frustrating.

                               

                              Does anyone have an idea of where I may be going wrong? I get that undefined error and nothing appears on my form.

                               

                              Thank you in advance.