Import Spreadsheet Data and DeleteCases of empty cells &/or choosing a Range to import

Question

I am importing Excel spreadsheet data into Mathematica.

Here is what the data looks like, notice the cell Q1 with "sdf":

I import this data by specifying the Spreadsheet location, sheet name, top 9, left 7, drop the Header Row and delete empty values:

Dataset1 = 
  DeleteCases[
   Drop[Import[
      "c:\\GraphingSoftware\\Book2.xlsx", {"Sheets", "Sheet2"}][[9 ;;, 7 ;;]], 1], ""];

FullForm[Dataset1]

List[List[126.7, 55.3, 113.8, 96.9, 47.8, 74.6, 71.9, 55.2, "", "", ""]

The results contain the emtpy cell values upto the "sdf" column.

I want to be able to remove empty values with the DeleteCases function but it doesn't work.

The documentation doesn't indicate if you can specify the number of columns and that would be really useful.

To save the day I simply want to delete empty cells or even better specify the number of columns so empty columns aren't included. Help as always is much appreciated.

Answer 1

Save your import result to a variable and check its structure. You will notice that importresult[[1]] is a List (representing the first row, the header in this case). Thus, if you use "" as the pattern for DeleteCases, it will match nothing because in level 1 of importresult there cannot be an empty string anywhere.

You could tell DeleteCases to operate on level 2: DeleteCases[importresult, "", {2}]. This will delete empty cells.

However, I would suggest not to do it this way as it will not be very robust in my experience. When importing external data, there can always be empty or faulty values and you should not let them break your code. Instead of deleting the cells which do not match, use Cases to select the rows which do match. You could do it like this:

1. Find out the number of columns that need to have values. You might take the number of strings in the header for this (but different methods might be more appropriate):

   columns = LengthWhile[importresult[[1]], StringQ]

2. Pick all rows with Cases that have as many Reals:

   Cases[importresult, {values:Repeated[_Real, {columns}], ___}:>{values}]

___ is BlankNullSequence, so anything (or nothing) can follow the repeated Reals. Finally we name the matched Reals values and use a replacement rule in Cases to just obtain this part of the matched row.

I believe you could even extend this and find the position of your data table in the excel sheet automatically, so you will not have to specify your starting position {9,7} at the time of import.

Answer 2

The documentation doesn't indicate if you can specify the number of rows/columns and that would be really useful.

I was wrong! Its a simple case of the Excel spreadsheet import info not having an example using spans.

To clarify, here is how I let users pick a top left cell to get the data in Row 3 to 28, Column D to L:

d = Import["C:\\MMA\\D13~136587.xlsx", {"Sheets", "Sheet1"}][[3 ;; 28 ;; 1, 4 ;; 12 ;; 1]];

Now I've got the exact region I'm interested in its easy to handle Nulls, #N/A's, Empty cells & etc using either the:

1) DeleteCases,
2) /. "" -> Sequence []
3) Cases[importresult, {values:Repeated[_Real, {columns}], ___}:>{values}]

Thanks!