I have a spreadsheet that is the source for several other reports that have been heavily macro'd. There is a new spreadsheet that is going to replace it as the source, but there are several new columns that were added, but not at the end they have been mixed within so the column order is shifted. So what I would like to do is Qlikview join the two and determine which ones are the same and which are new and what their respective location is. I have attached the two in a workbook.
so what I was wondering based on the attached...how can I determine the location and the ID to know which is new and which is changed.
i.e BMR ID is in A1 for both, GEO Share Early is in the new sheet in AH1, but is not in the Current.
OK, no worries - I've just testing one theory and I get the same error. I created a .CSV and manually renamed it to .XLS.
If you open the target file and do a save as, making sure you save it as Excel Worksheet format and then try the ODBC connector, I think it should work.
yes. that is what I needed. Also the ODBC connection once I save as the files then it worked...it also gave me even more information as using the SQL columns I can now determine if any of the changed columns also were different formats...text vs numeric
Not sure if you managed to get the ODBC connection working in the end. The error could be because of the driver associated with the DSN. If the driver assigned to that DSN is Excel 4.0 then it wouldn't work with .xlsx files. You can go to Data Sources under Control Panel > Administrator Tools, or go direct to it from Qlikview script under Tools > ODBC Administrator 32-bit (64-bit). Highlight the DSN and click 'configure' to see the settings.
In practice you would be better to create your own DSN, rather than tweak the existing DSN which might be used by something else, by clicking add and choosing the relevant driver. If a suitable driver doesn't show then it would need installing.
