Attribute Lookup: Excel Sheet with Leading Zeros & Using XLSX files

A problem was encountered with an attribute lookup using an Excel file.  The data contained leading zeros.  Regardless of the Excel cell formatting that is chosen, the leading zero is never brought back to IQ as part of the lookup.

There does not appear to be a straightforward way to ensure that the leading zeros are returned to IQ just by using the cell formatting in Excel.  The solution is to use the FORMAT function. For Example:

SELECT Customer, Address, City, State, FORMAT(Zip,’00000′) as ZipCode FROM [Sheet1$] WHERE Account = [%Account No.%]