Did you notice the dates, my dear Spreadsheet?
During testing I noticed some strange dates in a table. It was “01-01-0001”. That is more than two millennia ago. (One of the most influential men had his first birthday party on that particular date.)
I thought again:
“How would I mark an item with no end date?
But why are there more than 2 items with the same name and no end date?”
I found a bug. Next I scrolled through the table and found other pairs. This process should be automated.
- Export the table with the header in the database to a csv file.
- Import the file into a spreadsheet program.
If all data is still shown in one column, then
- Select the column.
- Use the spreadsheet function Convert text to columns.
- Select Separated.
- Choose the delimiting character ; (semi colon)
- Click Finish.
Now it is time to sift the information:
- Select all the columns of the table.
- Order the table on the item name and then on the end date.
Column A is item name and column C is end date.
- Select the cell right to the heading in the first row.
Column G is the first empty column.
- Add an extra attribute to the heading e.g. “Same” (in cell G1).
- Select the cell under the attribute Same. (G2)
- Go to the formula editor and make the following formula:=IF(AND(A2=A3; C2=C3);”yes”;”no”)
This means, when the item names of row 1 and 2 are the same and the end dates of row 1 and 2 the same, show the text “yes”;
if this is not the case, show the text “no”.
- Copy the formula by selecting the cell with the formula (G2), placing the mouse on the right lower corner of the cell with the formula, press the left mouse button, move the mouse downwards until the lowest row of the table and release the left mouse button.
The formula has been copied to all relevant cells in the column Same.
- Set a filter in the header of the table.
- Set the filter of the column Same to “yes”.
- Set the filter of the C column or End date to “01-01-0001”
All rows with items with the same name and no end date are shown.
Can you handle Big Data, Mister Spreadsheet?
A spreadsheet program is great to analyse csv files. But my first analysis of more than 70,000 lines of data was not so great: it took some time to load and convert the data in the spreadsheet program and it was not possible to load all data at once. There was a chance, that I might miss something. And I did not want to miss a bug.
Some people abhor the use of command lines. And if I mention UNIX some people think: “Run for the hills”. However there are some powerful commands, which make the life of a tester easier. grep is a Unix command, which can be used to filter information from ASCII files using pattern recognition like item id. So I searched for “Windows grep” on the Web and found an interesting open source tool.
I started with a csv file of table with no header. The table description was in a pdf file. I only had to examine all records with specified item id.
- Use a windows grep program to extract all lines containing the string with item id.
- Export the output to a csv file.
- Import the csv file in a spreadsheet program as described in the previous paragraph.
- Copy the column with the attribute names from the table description in the pdf file.
- Paste these cells in another worksheet of the spreadsheet.
- Transpose these cells into a decent heading.
(Change a vertical row cells to a horizontal row cells using a standard spreadsheet function.)
- Copy and insert the heading above the table.
- Set a filter on the heading.
- Set the filter of item id in the heading to the item id to be analysed.
All records containing the item id are shown.