Excel Imports

From MT Marvelit
Revision as of 18:32, 1 November 2008 by Rmortensen (Talk | contribs)

Jump to: navigation, search

Make Excel Data Accessible in your Dashboards

With DASH Professional - you can take excel data and make it accessible to your dashboard. This is done in four parts:

* Create a properly formatted excel file with your data
* Set the import preferences
* Import your excel data into a DASH table in your database
* Access this data in your dashboard

Format your Excel File

The "Import Data Via EXCEL" portlet is, quite simply, a fast way to define a database table and records using Microsoft Excel as a prototyping system. You create tables in Excel, hand off the Excel file to the importer portlet, and the importer converts it into SQL tables with the table name of the Excel sheet name. The tables are stored in the same database that Jetspeed uses in the back end. Although it's easy to use, there are several things the user should be aware of. First, the importer doesn't automatically know what data type each column should be, so you need to specify it manually. The first row of each table should be the names of the fields, the second row should be the data types of those fields, and the third rows and later should be the actual data.

Here is a very simple example of what an Excel sheet might look like if it were to contain a simple user registry:

Import3.jpg

In this example, we have just two records. The record stores the unique userid (perhaps the primary key), the username, and real name of Jean-Luc Picard and William Riker. The second row tells us what data types should be used: an integer, a fixed-length string of 8 characters, and a variable-length string of up to 32 characters.

Don't forget to put data types in the second row! If we had left them out, the importer would have tried to interpret the first record as a list of data types.

Most data types that are recognized by your SQL database should work with the importer. The most common data types are: CHAR(X) A fixed-length string of X characters. VARCHAR(X) A variable-length string of up to X characters. DECIMAL(X, Y) A number of up to X digits and up to Y digits after the decimal point. INTEGER An 32-bit integer ranging from -4,294,967,296 to 4,294,967,295.1 � Other data types tend to vary significantly between databases, and are listed here but not explained. Many of them require arguments to indicate the capacity of the field (e.g. "CHAR(10)" for a string of 10 characters). Consult the documentation for your particular SQL database.

The following data types are supported by JDBC, in addition to those listed above:

   * BIGINT
   * BINARY
   * BIT
   * BLOB
   * BOOLEAN
   * CLOB
   * DATALINK
   * DATE
   * DISTINCT
   * DOUBLE
   * FLOAT
   * JAVA_OBJECT
   * LONGVARBINARY
   * LONGVARCHAR
   * NULL
   * NUMERIC
   * OTHER
   * REAL
   * REF
   * SMALLINT
   * STRUCT
   * TIME
   * TIMESTAMP
   * TINYINT
   * VARBINARY

A few data types have special meaning to the importer. These are meant as convenience functions. CHAR Equivalent to CHAR(255) VARCHAR Equivalent to VARCHAR(255) LONGVARCHAR Equivalent to VARCHAR(1200) DECIMAL Equivalent to DECIMAL(10,2)

Keep in mind that the data types supported are always constrained by the SQL database that is being used. If, for example, you write the Excel file to work with Oracle, it may not work on MySQL, and vise versa.

Also note that any tables defined through an Excel file in this manner are saved in the same database as the DASH tables. As a result, there is a risk that a careless user of the portlet could modify one of DASH's tables, thereby breaking DASH. Extreme care should be used when setting up this portlet, to ensure that it cannot be accessed by anyone who is not an administrator!

Access the Import Portlet

Import1.jpg

Access the importer by selecting DASH Administrative Portlets and selecting Importer. The Importer portlet selection screen is displayed.

Import2.jpg

There is additional documentation on the importer contained in the portlet - select th i icon in the upper right corner of the portlet to display this content

Set your import preferences

To set your import preferences - select the pencil or edit icon on the portlet. The Importer preferences page is displayed.

500

Import Preferences: Error Handling

Ignore Invalid Excel Sheets

If set to "TRUE", any sheets containing errors are ignored. If set to "FALSE", an error in any sheet will prevent the entire file from being imported.

In practice, an error in one sheet may not be detected until the preceding sheets have already been imported. If this happens, and IgnoreInvalidSheets is set to "FALSE", the importer will attempt to undo the changes it made according to those previous sheets. However, it is NOT guaranteed that it will succeed. IgnoreDataErrors

This option does absolutely nothing!

Ignore Invalid Data Rows

Excel uses 1904 based dates

This should be to either "TRUE" or "FALSE", according to whether your version of Excel considers the epoch to be January 1, 1900 (default on Windows) or January 1, 1904 (default on Macintosh). The default is "FALSE". If you find that dates are imported incorrectly, try changing this option. Why is this an issue? Microsoft Excel supports both date windowing formats, but doesn't save any clues as to which format is being used. As a result, if this option is not set correctly, all dates will be off by 4 years. If you are producing Excel files from multiple computers to be used with the importer, it is highly recommended that you set the Excel preferences on all of the computers to the same value that the importer is configured to expect.

Overwrite existing tables instead of appending

If this option is set to "TRUE", any tables defined in the Excel file that already exist in the database will be dropped from the database and replaced by those from the Excel file. If set to "FALSE", the records from the Excel file are simply appended to the existing database table. Note that if the tables in the database and in the Excel file don't have the exact same schema, then it will not be possible to append the records from the Excel file. The importer will display an error message in this situation.

Database Browser Preferences: Data Source Type

The mechanism to use for accessing the database. Valid options are:

   * jetspeed: Uses the same database that Jetspeed uses.
   * jndi: Uses a JNDI data source.
   * dbcp: Uses an SQL database via JDBC.
   * sso: Uses an SSO data source.

The default is "jetspeed".

JNDI Settings

   * JndiDatasource: The path to be passed to the JNDI driver.

JDBC/DBCP Settings

   * JdbcDriver: The fully-qualified name for the JDBC driver.
   * JdbcConnection: The path to be passed to the JDBC driver.
   * JdbcUsername: The username to pass to the database.
   * JdbcPassword: The password to pass to the database.

J2 SSO options

   * SSOJdbcDriver: The fully-qualified name for the JDBC driver.
   * SSOJdbcConnection: The path to be passed to the JDBC driver.
   * SSOSite: The URL to be passed to the SSO driver.
   

Test and Save your Importer Preferences

Once you have made all your updates - clcik the Test button to ensure that they are correct. If errors display - correct the preferences until all errors are resolved. Click the Save button to store your preferences for the importer.

Select and Upload your Excel File

Once you have stored your preferences, click the browse button and navigate to a properly formatted Excel file and select it. The full path to the file will be displayed in the portlet

Import5.jpg

Click the Upload button to process the import. After several seconds messages will display that either tell you that the file was processed successfully or if there were import errors.

Import6.jpg

Common Import Errors

1. Make sure your sheet names are one word - or use the underscore "_" instead of spaces. 2. Make sure there are no spaces in your column names - use the underscore "_". Column names in your spreadsheet = column names in your table. 3. Make sure that you do not bad characters in you spreadsheet - especially columns defined as integers. Make zero instead of being empty. 4. Make sure column or sheet names are not SQL reserved words

How to Troubleshoot Importer Errors

Set your preferences to overwrite your data - that way during troubleshooting - your table will be dropped and rebuild each time

1. Start small - have 1 row of data for all columns. This will usually weed out data errors - and will let you know if column names are the issue. 2. If 1 row of data does not load - than something is wrong with your column names or data definitions. Start with th efirst column do an import. Keep adding columns until you get the error. The last column is the issue. 3. If all columns load with 1 row of data - than you have a data issue in other data rows. Look at your spreadsheet and pay close attention to columns defined as integers or real. Dates can also be an issue. Unfortunately - the same method applies - load small amounts until you can isolate the row with bad data.

Access your data in your dashboard

Once you data has been uploaded - you can access it using standard SQL in all mmarvelit portlets. Your sheet names = table names and column names = columns in your table.