Home > Excel, SQL > Importing varbinary fields into Excel

Importing varbinary fields into Excel

May 18th, 2011 admin

I needed to compare two large datasets and find out if they contained the same records.
So I amended the records with a HashByte at the end so I could check if they existed in the other one.
First I imported both queries in Excel with the idea to do a lookup on them.
But a varbinary field is not imported in excel and it will return no error when ommitting a field,
except when the only field is a varbinary field then Excel willl tell you:

No columns that Microsoft Excel can use were returned from this query.

So I had to go back to the Management Studio and join the two sets which was a pain because they were on differend servers.
So much pain I kept trying to find a Excel solution.
All I had to do was convert the varbinary field by making it a parameter in a function which would return a normal varchar.

So

SELECT [field] FROM [table]

had tot change to

SELECT master.dbo.fn_varbintohexstr([field]) AS [field] FROM [table]

 

enjoy

Categories: Excel, SQL Tags: ,
Comments are closed.