11 Replies Latest reply: May 15, 2014 9:52 AM by Marty _ RSS

    External Lookups with Excel as Source

    Marty _

      Hi,

       

      I am having a problem defining an External Lookup to Excel from the Table Window using Monarch Pro version 7.

       

      In My Excel worksheet I have a named range where the lookup column (the 1st column in the range) contains cells formatted as Text (the actual values are alphanumeric). The Field Properties in Monarch Pro for the matching column from the Table Window s defined as Type:Character - Format: General.

       

      I select the Excel file as the data source (local or network file). Then select the named range in the External Lookup dialog box and click Next. The "sample data" window in the External Lookup dialog box then shows the first  column with the header "F1" but with the values trailing with 5 decimals - all 0s.

       

      In the Select Columns to Link dialog box I select the Excel column as the source and the appropriate Table Window column to link but receive the error dialog box "The Source and Table Window Columns must be of the same type."

       

      Any insight would be appreciated.

       

      Regards,

      Marty.

        • External Lookups with Excel as Source
          Marty _

          Hi,

           

          I am having a problem defining an External Lookup to Excel from the Table Window using Monarch Pro version 7.

           

          In My Excel worksheet I have a named range where the lookup column (the 1st column in the range) contains cells formatted as Text (the actual values are alphanumeric). The Field Properties in Monarch Pro for the matching column from the Table Window s defined as Type:Character - Format: General.

           

          I select the Excel file as the data source (local or network file). Then select the named range in the External Lookup dialog box and click Next. The "sample data" window in the External Lookup dialog box then shows the first  column with the header "F1" but with the values trailing with 5 decimals - all 0s.

           

          In the Select Columns to Link dialog box I select the Excel column as the source and the appropriate Table Window column to link but receive the error dialog box "The Source and Table Window Columns must be of the same type."

           

          Any insight would be appreciated.

           

          Regards,

          Marty.

          • External Lookups with Excel as Source
            Nigel Winton

            Marty

            I have come across this problem where the first record in the excel file is a numeric. Although it is formatted as text, it seem to be treated as a numeric then will not match with the Monarch character field. Try putting a character record at the top of the column or sorting the excel file descending, which will put the character records first, it sometimes works.

             

            Nigel

            • External Lookups with Excel as Source
              Nigel Winton

              Marty

              I have come across this problem where the first record in the excel file is a numeric. Although it is formatted as text, it seem to be treated as a numeric then will not match with the Monarch character field. Try putting a character record at the top of the column or sorting the excel file descending, which will put the character records first, it sometimes works.

               

              Nigel

              • External Lookups with Excel as Source
                Bruce _

                Marty

                 

                Also try going into the "field list" (by right clicking in the table window. Then adjust the properties of the field in question.

                 

                Regards

                • External Lookups with Excel as Source
                  Bruce _

                  Marty

                   

                  Also try going into the "field list" (by right clicking in the table window. Then adjust the properties of the field in question.

                   

                  Regards

                  • External Lookups with Excel as Source
                    Marty _

                    Hi All,

                     

                    Thanks for your help thus far. I've since set the column in Excel to sort descending so that the rows with values in which the column begins with  alpha characters (the whole value for the column contains both alpha and numeric) appear first. As a result I am beyond the "Source and Table columns must be of the same type" problem but now find:

                     

                    "An error was encountered while reading the sample data. The value at row xxx, column y could not be read. This and subsequent bad values will be treated as nulls".

                     

                    The row xxx happens to be the row number where the first occurence of an all-numeric value begins (even though the format is text in Excel). When I manually insert an apostrophe before the offending row's cell value. I get the same error but with the row number xxx + 1 (the next row). I can't manually insert apostrophes in over 1000 rows. Any ideas?

                     

                    Regards,

                    Marty.

                    • External Lookups with Excel as Source
                      Marty _

                      Hi All,

                       

                      Thanks for your help thus far. I've since set the column in Excel to sort descending so that the rows with values in which the column begins with  alpha characters (the whole value for the column contains both alpha and numeric) appear first. As a result I am beyond the "Source and Table columns must be of the same type" problem but now find:

                       

                      "An error was encountered while reading the sample data. The value at row xxx, column y could not be read. This and subsequent bad values will be treated as nulls".

                       

                      The row xxx happens to be the row number where the first occurence of an all-numeric value begins (even though the format is text in Excel). When I manually insert an apostrophe before the offending row's cell value. I get the same error but with the row number xxx + 1 (the next row). I can't manually insert apostrophes in over 1000 rows. Any ideas?

                       

                      Regards,

                      Marty.

                      • External Lookups with Excel as Source
                        RalphB _

                        Marty,

                         

                         

                        I have had similar problems and the way I have solved it is to convert everything to text in Excel. 

                         

                        The easiest way would be with a formula in a neaby cell.  The formula you could use is assuming that "A1" is the where your lookup column starts is: 

                         

                        "=if(isnumber(a1),text(a1,0),a1)".

                         

                        Copy this formula down the full length of your table then "copy", "paste special" "as value" on top of the first column.

                         

                        What the formula does is checks to see if the value in the cell is a number or a text.  If it is a number, then it converts it to text otherwise it leaves it alone.

                         

                        This is probably the quickest and easiet way to solve your problem that I can come up with and has worked fine for me.

                         

                        Ralph

                        • External Lookups with Excel as Source
                          RalphB _

                          Marty,

                           

                           

                          I have had similar problems and the way I have solved it is to convert everything to text in Excel. 

                           

                          The easiest way would be with a formula in a neaby cell.  The formula you could use is assuming that "A1" is the where your lookup column starts is: 

                           

                          "=if(isnumber(a1),text(a1,0),a1)".

                           

                          Copy this formula down the full length of your table then "copy", "paste special" "as value" on top of the first column.

                           

                          What the formula does is checks to see if the value in the cell is a number or a text.  If it is a number, then it converts it to text otherwise it leaves it alone.

                           

                          This is probably the quickest and easiet way to solve your problem that I can come up with and has worked fine for me.

                           

                          Ralph

                          • External Lookups with Excel as Source
                            Marty _

                            Ralph,

                             

                            Thanks so much for your help! Using the IsNumber function with the explicit conversion to text  did the trick - an apostrophe gets inserted for each column value !

                             

                            Best Regards,

                            Marty.

                            • External Lookups with Excel as Source
                              Marty _

                              Ralph,

                               

                              Thanks so much for your help! Using the IsNumber function with the explicit conversion to text  did the trick - an apostrophe gets inserted for each column value !

                               

                              Best Regards,

                              Marty.