I have this excel file which is giving me unexpected results on an SQL query with a join on
SELECT CO.MATERIALS, CO.SIZES, CO.TOOLS, IR.PN, IR.BODYJAW
FROM `CROSSJOIN$` CO
LEFT JOIN `INSERTS$` IR
AND ((IR.MATERIAL Like '%'+CO.MATERIALS+'%'))
AND ((IR.SIZE Like '%'+CO.SIZES+'%'))
It's interesting that only the
I think what's happening is that the first values Excel is seeing in your Material columns is 333, so it's inferring the column type is an
Integer value, which would limit the potential matches to other Integer values.
You could test that theory by fudging other material codes -- make a "444" or something in each worksheet and see if that matches. If that's the case, ensure that the datatypes of the columns are what you want them to be (all text, I assume).
IMEX=1 to your connection string might be easier -- it will make the Excel data reader treat all data as strings.