How to auto-fill lots of columns or cells with the same value in Excel

I had a spreadsheet recently (actually a CSV file which contained numerous columns of data. The values in the cells were either blank (empty) or the letter 'Y'. I needed to change all the blanks to 'N'.

Find and replace didn't work as you can't specify blank as a matching character (at least I couldn't find out how, please comment if you know!) and I didn't have the energy or time to manually update each cell. After some research I found that the answer was the 'Goto -> Special' options hidden within the 'Edit' menu.

Here's how to do it...

  • Select the columns and/or cells that you want to perform the special find within (hold down CTRL as you click the column headers or individual cells.

  • Click the 'Edit' menu and select 'Go To...':


  • Then click the 'Special...' button:


  • Then click the 'Blanks' option and click 'OK':

Any cells within your selection that contain blanks will now be selected leaving the other non-blank fields un-selected. If you now type and press ENTER it fills that first cell with what you typed and moves to the next blank cell. BUT here is the trick, if you type what you want in the cell and press CTRL+ENTER then all the blank fields will be filled with the entered values - pretty neat huh!

There are many more options within that Special pop-up and I've not really explored it at all but I have found out how you select all the non-number-only fields:

Remember, the 'Go to -> Special' helps you select the correct cells, the CTRL-ENTER auto fills them all with the value you enter in the first selected cell.

I found this information out from the rather useful Excel section of allexperts.com.

Technorati Tags: , ,

How to run individual JUnit unit test methods within Eclipse

Eclipse supports JUnit out of the box, and even has a handy keyboard shortcut for running all the test methods within a JUnit test class: CTRL-F11. You can also right-click within the unit test class, choose 'Run As' then 'JUnit Test'.

But what if you want to run just one of the many test methods with a particular test class? IntelliJ's IDEA has this option (you just right-click on the method name and run it) but Eclipse doesn't offer that feature.

One solution is to run all the tests within the test class (as described above) and then click on the particular test in the left-hand JUnit results window, right-click on the test and choose 'Run':


The other is to open the "Outline" view, and then right-click on appropriate unit test method, choose 'Run As' then 'JUnit Test':


That means that you don't have to run all the tests within a test class just to be able to run one of them. Now isn't that easier?

Technorati Tags: , , , ,

Using ampersands (&) without variable substitution in Oracle either direct or with SQL*Plus

Oracle uses the ampersand (&) symbol within a string to indicate a substitution variable. But what if you want to use an ampersand as part of a regular string such as 'Bob & Sons'?

There are two solutions that I know of and which one you use depends on the context in which you are running your SQL:

Dealing with the ampersand on an SQL level

To turn off interpreting the ampersand as a substitution variable it must be at the end of a string:
insert into companies values ('Bob &' || ' Sons');

Dealing with the ampersand when using SQL*Plus

To disable Oracle's variable substitution and therefore return & to the pool of standard characters you need to tell SQL*Plus to disable it:
set define off
Add that to the top of your Oracle SQL script or type it manually at the SQL*Plus prompt.

These tips plus many more can be found within the rather useful The Oracle (tm) Users' Co-Operative FAQ.

Technorati Tags: , , , , ,

Hibernate HQL problems with database views and deleted rows within the same transaction

I found an interesting Hibernate view-related issue yesterday when deleting some data from a table within a single transaction.

Let me set the scene a little - we have a People table which has one row per person. We have a FamilyTree database view which displays family trees using the People table as one of it's sources (it's quite a complex view with functions and the like but you get the picture).

I was writing some unit tests around the area of deleting people. I used the view to get a list of people, deleted a person from the People table and then used the view to ensure that the person was gone. All of this was done with Hibernate-annotated domain objects and some JPA-based DaoSupport style classes - the standard Spring/Hibernate way.

What I found was that when we were within a single transaction (i.e. within a unit test or within a web controller using the OpenSessionInView) and we delete from the table, the 'deleted row' is still visible and therefore incorrect. If you looked at the SQL trace you would find that the DELETE SQL statement was never flushed to the database as Hibernate is trying to be lazy and only perform such actions when it really has too.

As there are no annotations for telling Hibernate that this entity is actually backed by a view rather than a table, there is no way to tell Hibernate that it must flush changes to the People table when we access the FamilyTree view! This was (roughly) the code that was failing:

List results = getJpaTemplate().find("FROM FamilyTree ft WHERE ft.parent = ?", parent);
return results.get(0).getPerson();

As the HQL doesn't make a reference to the People table or the person relationship within the FamilyTree entity, Hibernate does not know that it needs to flush any outstanding People changes before it performs the SELECT. To prove this if we add a simple SELECT * FROM people between the row delete and the view access then the delete is flushed and so the view displays the correct information.

This didn't seem to be the cleanest of solutions so I tried changing my HQL query of the FamilyTree view to return a Person entity like so:
List results = getJpaTemplate().find("SELECT ft.person FROM FamilyTree ft WHERE ft.parent = ?", parent);
return results.get(0);
As we have explicitly mentioned in the HQL that we want the person child object of the entity Hibernate is forced to flush any outstanding People changes before it performs the SELECT!

I know that using database views within Hibernate is considered 'advanced' but it would would be nice to have some kind of @ReliesOn annotation to cover these edge cases. We now have to try and remember this strange behaviour whenever we deal with views.

I'm no expert on Spring and Hibernate so I may have got the wrong end of the stick completely - if that is the case then please comment on the right way to solve this problem as I would love to learn!

Technorati Tags: , , , , , ,