Extracting Information from an Excel Sheet

If there is a column in your Excel spreadsheet that contains both text and numbers, the ISAM driver will not be able to correctly interpret which datatype it should be. Make sure that all the cells in a column are formatted to be the same datatype. For example, you might have following data in four columns in an Excel sheet:

   male   female children teens
   11     cc     78       ee
   22     xx     33       ff
   45     uu     56       oo

If you try to read the data through ISAM driver against the whole sheet, you will get the null values for first row. If you want to avoid this, create named ranges: one containing only the header information and another containing the data information. For example:

   named range 'myRange1' :
   male     female    children     teens

   named range 'myRange2' :
   11   cc   78   ee
   22   xx   33   ff
   45   uu   56   oo

Now you can connect to Excel via TextPipe and request information only from the particular named range. However, in one range, a particular column can contain only one type of data.

How to Create a Range

Follow these steps in order to create a range:

Highlight the data.
From the menu bar, click Insert, and then click Name.
Click Define, and then click Range name.

Note: The Refers to box will refer to your highlighted range. This should grow and shrink as data is inserted and deleted.

To retrieve your data, use the range name you just created for the table name in your select statement.

Excel Steps

Create the Excel file, test.XLS, with following data in sheet1:

   excel File : test.xls with the following entries:

        male  female    children   teens
         11    cc       78           ee
         22    xx       33           ff
         45    uu       56           oo

Create the named range, myRange1 and myRange2, in the sheet containing the appropriate data.

      named range : myRange1

      male female children teens

      named range  : myRange2

      11     cc   78   ee
      22     xx   33   ff
      45     uu   56   oo

TextPipe Pro Steps

Add a database filter from Filters Menu\Special\Database filter
Set the database filter to connect to the Excel driver, and specify the full path to test.xls
Add the following text in TextPipe's Trial Run area:

select * from `myRange2`

Note the reverse apostrophe "`" while specifying the range
Click the Trial Run button

The following results are as expected:

  11
  cc
  78
  ee
  22
  xx
  33
  ff
  45
  uu
  56
  oo