
External Lookups with Excel as Source
Marty _ May 15, 2014 9:52 AM (in response to 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 May 15, 2014 9:52 AM (in response to Marty _)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 May 15, 2014 9:52 AM (in response to Marty _)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 _ May 15, 2014 9:52 AM (in response to Marty _)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 _ May 15, 2014 9:52 AM (in response to Marty _)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 _ May 15, 2014 9:52 AM (in response to 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 allnumeric 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 _ May 15, 2014 9:52 AM (in response to 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 allnumeric 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 _ May 15, 2014 9:52 AM (in response to Marty _)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 _ May 15, 2014 9:52 AM (in response to Marty _)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 _ May 15, 2014 9:52 AM (in response to 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 _ May 15, 2014 9:52 AM (in response to 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.