Comparing two worksheets in Excel

[Anonymous]

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.

[#2]

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

[Anonymous]

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.

[flipside]

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

[Anonymous]

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?

[#6]

Hi Todd,

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

[Anonymous]

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

[#8]

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?

[Anonymous]

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?

[Anonymous]

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