Fix the issue: Blank cells in Excel 2010 via Copy with Headers in SQL Server 2008 R2

In this Sprint, we have the important feature: Users uploads the Excel Spreadsheet in our Silverlight Application, 283 values will be saved in the new table in SQL Server 2008 R2 and the values will show up immediately in the Silverlight UI page.

Validating 283 values is really time-consuming. One basic test scenario is that I put the incremental values (1-283) in each cell first and then validate the values are showing up correctly in DB after the upload of spreadsheet.

Each field in the UI has the long business name so I use column aliases in the SQL script.

For example

Select

YR1_SALES_INV_RATIO as ' Market Year 1 Sales to Investment Ratio'     ,

From TableName

In order to validate the values correctly, I use Copy with Headers and paste the data to Excel spreadsheet. Here are the steps:

·         Select the data in SQL Server 2008 R2 and use Copy with Headers

·         Paste to the Excel Spreadsheet 2010

·         Transpose the data

One interesting issue happens after trying to compare the values.  Many header cells after the transpose are blank in Excel Spreadsheet.

I know I can use “Shift cells up” to remove the blank cells, but this is not the best solution because I need to remove them manually every time after doing copy/paste/transpose.

After looking into this interesting issue for a while, I finally find “column Aliases” are too long and Copy with Headers function can’t handle it well in Excel Spreadsheet, which causes the blank cells.  

After simplifying column aliases and try the steps above, the blank cells are going away.