You are here: Defining Files and Fields > Importing Non-Excel Spreadsheet Data

Importing Non-Excel Spreadsheet Data

Analyzer can only read MS Excel spreadsheets directly. For other proprietary spreadsheets such as Lotus 1-2-3 you can import spreadsheet data into Analyzer by exporting the desired range of data in the spreadsheet into a format that Analyzer can read such as:

• Comma or tab delimited file
• ASCII text file
• dBASE file

You can also choose to import ODBC-compliant spreadsheets via ODBC.

A delimited file is preferred, because Analyzer recognizes delimited files and automatically creates the required table layout.

Before saving spreadsheet files as delimited files or importing via ODBC, ensure that the spreadsheets have the following characteristics:

• The first row in the spreadsheet must contain field names.

Note: dBASE truncates long field names to 10 characters while ODBC truncates long field names to 33 characters. If truncation creates duplicate field names, fields with duplicated names will not be displayed in Analyzer.

• The second row in the spreadsheet must contain data.
• There must not be any blank rows between records or blank columns between column titles. Blank rows or columns indicate the end of the file.
• There must not be any named ranges called Database. To verify this in MS Excel, click in the column title. From the Insert menu, select Name, then select Define. If a named range called Database exists, rename it by typing over the current name. Click [OK].
• When the first data field in a column of alphanumeric data contains only numbers, spreadsheets expect all fields in that column to contain numeric data only. Any fields in that column that contain both alpha and numeric characters will display as zero values. To avoid this problem, add an apostrophe (') as the first character in that field.

For example, a payroll spreadsheet contains the field name Department Number. The first and second data fields below it are 144 and A144. If an apostrophe is not inserted in the first field (144), all subsequent records that start with a character, such as A144, will display a 0. Inserting the apostrophe ('144) allows subsequent records to display both numeric and character fields.

Note: It is a best practice to select and name the data range to be accessed via ODBC. For more information see Importing ODBC-Compliant Data.