RDBMS Table Design in Excel?


Posted by Cynic | Posted in Databases, Logic | Posted on 27-01-2011

Tags: , , ,

Image: jscreationzs / FreeDigitalPhotos.netI was just thinking about data mining relationships between atoms and chemical compounds, and about putting it all into a nice, neat RDBMS for that, when it occurred to me that I’m really not an expert in the field, and designing the database would require more knowledge than I have. However, in a process of table design on a subject that you’re not really familiar with, it also occurred to me that the best tool for the job is likely Microsoft Excel. In a spreadsheet you can add column effortlessly, and while you are doing that, you can fill in the rows. The process effectively lets you create the table and fill in the data slowly before you end up doing the “real” design.

I’m not advocating database design in Excel. Heck… I’m one of those radical freaks out there that would crucify anyone that dared not initially design for the 5th normal form and then denormalize for optimization. But in that specific case where you’re doing investigative work, Excel can be a very useful tool in table design.

Copying Collapsed Rows/Columns in Excel Excludes Hidden Data


Posted by Cynic | Posted in Solutions to Problems | Posted on 17-01-2011

Tags: ,

Excel doesn’t copy collapsed columns or rows, which can be excruciatingly painful to figure out.

When copying Excel data into a text editor (or other program), if you have a collapsed row or column, the data will not paste properly.

51 rows in excelSo, imagine you have 51 rows of data. That’s what Excel tells you anyways like shown in the screenshot on the right here.

Collapsed row in ExcelNow, you paste that into your text editor, but only get 50 rows! WTF? Well, go back and check for a collapsed column like shown in the next screenshot on the left. Notice how row 17 is gone, and how the first column skips from 14 to 16 in the next cell. Copying those three cells, i.e. 16, 17 and 18 gives us:


And that’s pasted directly into this page. Where’s 15? Hidden by Excel.
It’s not “bad” behavior, but it is certainly annoying if you are expecting to have all your data pasted and not anticipating a hidden row.

For those of us that normalize our data and use Excel as a spreadsheet and not a document authoring tool, it’s painful. I suppose that’s what you would expect if you’re used to authoring documents in Excel though.