topic Re: Comparing two worksheets in Excel in QlikView

Comparing two worksheets in Excel

Anonymous — Mon, 07 Oct 2013 19:27:58 GMT

Good afternoon/evening/morning.

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.

Re: Comparing two worksheets in Excel

Mon, 07 Oct 2013 22:06:28 GMT

Hi Todd,

I would worry with the order of the columns as long as the header names are the same.

You can simply join them in a table using a concatenate load like this:

Table:

LOAD *

FROM [Book1.xlsx] (ooxml, embedded labels, table is [current sheet];

Concatenate(Table)

LOAD *

FROM [Book1.xlsx] (ooxml, embedded labels, table is [New sheet];

Everything should fall into place.

Hope this helps.

Kind regards,

Nuno

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 14:33:41 GMT

Nick

Thanks for the reply, but in my case I want to know what the columns are as it will check the macro. Qlikview doesn't care about the order just that each header is unique...Excel does. When the column location isn't the same the macro isn't capable of finding it, thus causing errors.

Re: Comparing two worksheets in Excel

flipside — Thu, 10 Oct 2013 14:49:43 GMT

Hi Todd,

You could use an Excel ODBC connection to interrogate the data as this ...

ODBC CONNECT TO [Excel Files;DBQ=C\SourceData.xls];
SQLColumns;
Disconnect;

ODBC CONNECT TO [Excel Files;DBQ=C\SourceData2.xls];
SQLColumns;
Disconnect;

You can then retrieve the ORDINAL position of the columns for comparison. You may need to set up the User DSN using the Microsoft Excel driver.

flipside

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 15:28:16 GMT

flipside. I have an error External table is not in the expectd format. I have mapped the drive and the excel is 97-2003 format?

Re: Comparing two worksheets in Excel

Thu, 10 Oct 2013 15:40:51 GMT

Hi Todd,

Just a quick sanity check, do the file extension on the files match those in the ODBC CONNECT line in your script?

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 15:54:50 GMT

so my guess is no? the file is capital .XLS and the map is .xls (lower case)

Re: Comparing two worksheets in Excel

Thu, 10 Oct 2013 16:00:49 GMT

I've checked and Qlikview will accept .xls .XLS and .xlsx, so it's not that.

Do you have the line of code for the ODBC CONNECT?

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 16:09:01 GMT

I believe that is what I am trying to get to. Apologize as I am not very versed in scripting...so in the edit script I hit the connect ODBC then a window opens for connect to data source...I then select Excel files. then a window opens for the drive that I mapped. I then select one of the files I want then the window with the error pops up?

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 16:14:00 GMT

Typically I just hit the table files button and go to the Excel file...only ODBC connections I have done are Access databases

Re: Comparing two worksheets in Excel

Thu, 10 Oct 2013 16:22:01 GMT

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.

Re: Comparing two worksheets in Excel

Clever_Anjos — Thu, 10 Oct 2013 16:39:26 GMT

Check if helps

Re: Comparing two worksheets in Excel

Anonymous — Thu, 10 Oct 2013 16:45:42 GMT

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

Re: Comparing two worksheets in Excel

flipside — Fri, 11 Oct 2013 09:00:21 GMT

Hi Todd,

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.

You can also do something similar with OLE DB drivers (http://www.connectionstrings.com/excel/).

flipside