This will be a series of handy hints for people that know SQL and are frustrated by Excel. I am going to be writing this for Excel 2007 (what I currently have at work) but the tips should work for most recent versions. The SQL stuff should also work with SQL 2000 and higher, but I have only tested on 2008.
The Basics
Run this on a test DB in your SQL Server, making sure you’re set to the default of “Results to Grid” (CRTL-D):
SELECT 1 AS [ID],'Jim' AS [Name], 'Red' AS [FavoriteColor],
CAST('20110101 14:00:00' AS DATETIME2) AS [ModifedDate],
'007' AS [SpecialCode] UNION ALL
SELECT 2, 'Sally','Blue', '20110205 12:30:00','025' UNION ALL
SELECT 3, 'Tim', 'Orange', '20110308 04:17:00','097' UNION ALL
SELECT 4, 'Todd', 'Green', '20110512 18:22:00','101' UNION ALL
SELECT 5, 'Ann', 'Orange','20110719 19:59:00','002'
Left-click the origin of the results (the blank space left of the “ID” column header and above row number 1) to select all the results. Then right click in the same spot and choose “Copy with Headers”. Launch Excel and paste the data into Sheet1 in cell A1. You should get a mess that looks like this:
Several things can quickly be fixed up about the presentation of this data.
- First of all, for some insane reason, Excel does not properly handle formatting date data pasted from SQL Management Studio – it assumes that the correct thing to do is format it as a time. At least in this case, the data is still there and it’s just a presentation issue.
- Highlight column D by clicking the column D header (above where it says “ModifiedD”) and then right clicking in the same location and choosing Format Cells…
- Under the first tab, select category “Date” and then pick the closest one to what you want to see.
- If you find one that is close, but not exact, select it, and then click “Custom” where you can see the actual formatting code under the “Type:” field for customization. “Military” SQL time format for the Gregorian calendar is yyyy-mm-dd hh:mm:ss;@
- Don’t worry about selecting the column header row when you do the formatting – Excel will usually ignore it (see below for some ways that this magic works).
- To fix via formula, click on cell F2 and type =RIGHT(CONCATENATE("000",E2),3) and press <ENTER>. Assuming you were doing a select statement in T-SQL from a table with a field named E2, this would be equivalent to RIGHT('000' + CAST(E2 AS VARCHAR(30)),3) To duplicate this formula to all successive cells in the column, you can just double-click on the “bump” on the lower right of the F2 cell border when it is selected.
- The other way to fix it (since Excel actually does shred out the leading zeros) is to pre-format the column as text. Select the column by clicking the column header for E, right-click and do Format Cells… and on the first tab, choose “Text”. (You can also choose “Custom” and use the format code @ which basically means “whatever was in there”.). Then go back to SQL Management Studio, reselect all and recopy the data with headers. Then paste the data back in to Excel in A1 and it should no longer drop the leading 0s; the only difference is that now Excel puts a green triangle in the cell to warn you that the data is a “Number stored as text”. Note that these format codes are doubly-useful for SQL DBAs as they are also used by SSRS as the number/date formatting codes.
- Note that Excel is generally pretty smart and will realize that row 1 is a header row automatically if for example you have text in all fields in row 1, but some columns have numerics or other data types in all successive columns.
Coming Next
In the next post, I will be showing you how to use VLOOKUP and some other functions in Excel. VLOOKUP is an awesome function that allows Excel to do some fairly clever things to facilitate analyzing different sets of data (it is also how some users start to develop scary monster sheets, so be careful!). I will also show you the Filter feature(awesome!!!) and also how to get a bit quicker with copying formulas around via the keyboard.
