Last post Dec 13, 2014 11:04 PM by Lannie
Dec 12, 2014 02:22 PM|fralo|LINK
I have done only modest imports of SQL data into Excel so please bear with me if this is not too difficult. Basically, I have an Excel document where the cells contain an integer. This integer is nothing more than a SELECT COUNT(*) of a database table.
What's the best way to handle this? Should I create a separate SQL query for each individual cell? I tried this on one of the cells by using the wizard "From Microsoft Query". However, when the data is imported it appears with the header Column1 and the
count right below it.
Thus, the data appears one cell lower in the document than I wish. All I want is the data, not the header.
But I'm even wondering if this is the best way to accomplish this. If I have 50 cells that need populating, for instance, is it really the most efficient to create a separate SQL query for each cell?
Thank you for any help you could lend. I appreciate it.
Dec 13, 2014 11:04 PM|Lannie|LINK
It is best to make as few trips as possible to open and close database connections.
That leaves the issue of sql query returning the header row.
OLEDB has option called HDR HDR=YES HDR=NO
if you update OPENROWSET and set the option HDR=NO
that will likely update the target range in Excel with values and not the SQL header row.