TIBCO Data Virtualization® Adapter Online Help > TDV File Data Sources > Microsoft Excel Data Sources > Adding Microsoft Excel (non-ODBC) Data Sources
 
Adding Microsoft Excel (non-ODBC) Data Sources
When adding Microsoft Excel files as data sources on UNIX platforms, use the Microsoft Excel (non-ODBC) data source adapter to begin configuration. The TDV Server uses a non-ODBC Microsoft Excel driver based on Apache POI to enable introspection and to use multiple Excel data files at one time.
To add a Microsoft Excel data source on a UNIX platform
1. Before introspecting the Excel data sheet as a TDV available data source, configure it as an ODBC-available data source with a DSN locally accessible to the TDV Server.
2. Right-click at a location in the Studio resource tree where you want this data source to reside, and select New Data Source.
3. In the New Physical Data Source dialog, select Microsoft Excel (non-ODBC).
4. Click Next.
5. Type a name for the data source.
6. Select one of the following:
Selection
Description
Local File System
Select if the Excel file is on the local file system. With this option, you can select one, more, or all the files in a directory. You can also select all the directories and all the files of the same type in those directories to introspect all Excel spreadsheets at the same time.
Specify the root path to begin the search for the files on the local file system. Root path is the absolute path to the root directory where the files reside.
URL
For TDV running on UNIX operating systems, you can add an Excel file located on the local machine with a URL file protocol like the following:
file:///usr/name/folder/excel_filename.xls
 
The directory containing the source Excel file must be mounted to the UNIX server hosting TDV. For example if the computer directory 10.1.2.199/d$/public contains the Excel file, it could be mounted as /root/public. The Excel file could be accessed with a file URL like:
file:///root/public/folder/excel_filename.xls
7. Optionally, specify a filename filter to restrict what types of files are to be introspected. This adapter supports introspection and use of two kinds of Excel data source files: *.xls (Excel 97-2003) and  *.xlsx (Excel 2007). During introspection, the introspection tree only displays the filenames with this extension. If you want to introspect more than one type of file, separate the type filters with commas.
Rules for the filters:
— * (asterisk) means that any character in the filename occurs zero or more times.
— ? (question mark) means that any character in the filename occurs exactly once.
— , (comma) is a separator for each filter.
— \ (backslash) is an escape character to escape a filename that contains an asterisk, question mark, or comma.
Note: Only files that match the filter you specify here are exposed later on during the process of adding or removing data source resources (through the Add/Remove Resources menu option) and also during data source re-introspection.
8. Optionally type values for or make selections for the following:
Element
Description
Character Set
Character encoding type. See Supported Character Encoding Types.
Data Range
Enter the value that indicates the data range you want to introspect.
Blank Column Type
Choose the data type to apply to blank columns: Varchar, Double, Boolean, or Datetime.
Has Header Row
Check if the first row of all the introspected Excel sheets has a row of column names. If it is not selected, the first row of each Excel data sheet is introspected as a data row and the column names are: COL1, COL2, COL3. After the connection is established and the Excel files are introspected, each sheet is made available as a TABLE that can be defined as having or not having a header row independently of the original schema header row setting.
Columns in Every Row Use Format Categories of Columns in First Row
Check to introspect the data in every row formatted as specified in the first row.
Ignore Invalid Data
Check to ignore invalid data.
Introspect with Formatted Display Values instead of Actual Values
Check to introspect the formatted display values.
Blank Value as Null Value
Check to introspect blank values as null values.
9. Click one of these buttons:
— Create & Introspect—To proceed immediately with introspection.
— Create & Close—To create the data source; you can introspect at a later time.
10. Refer the User Guide, Chapter Retrieving Data Source Metadata for how to introspect now or later.