Importing Datasets from Excel

The Basic Parts
Window Components
Sheet List
Dataset List
Sheet Fields

Three Steps

Limitations

The Algorithm

Other Conversions


 



Spreadsheet importation window

This window appears when you choose "Import..." from DataWallet's Options menu. Using this window you can open Microsoft Excel workbooks and convert spreadsheets to DataWallet information sets.

Window Components

There are three panes or window sections in the Import window.  To access their menus, right-click on either of the two list boxes in the left-hand part of the window.

Sheet List

The upper-left list box contains the names of the worksheets (spreadsheets) contained in the currently open Excel workbook.

Converted Dataset List

The lower-left list box contains the names of the worksheets you have converted to DataWallet's "dataset" format.

Sheet Data

The right-hand grid displays the contents of the worksheet currently selected in the Sheet List.

Three Steps to Importing

Here are the steps to import an Excel worksheet into a DataWallet wallet file.

  • Right-click on the Sheet List (upper-left list box) and select "Open..." from the popup menu.  Browse to the workbook of your choice and open it.  The list box will fill with the names of the sheets it contains.
  • Select a sheet from the list and the right-hand grid will fill with rows of data from the worksheet.
  • Right-click on the name of the selected sheet in the list and select "Convert" from the popup menu.  The name of the new dataset will appear in the lower list box of converted sheets.
  • Switch back to the main DataWallet window and select a DataWallet wallet.  Right-click on the name of the wallet in the left-hand pane and choose "Add Imported Sets".  DataWallet will then add the results of worksheet conversion to the currently selected wallet.

You can delete a converted dataset before adding it to the wallet by right-clicking on the lower-left list box of the Import window and selecting "Delete" from the menu.

Limitations of Excel Importation

Excel worksheets can be very complex.  DataWallet scans each newly opened worksheet looking for a simple rectangular structure:  a single "header" row and up to 1000 "data" rows.  If the sheet does not fit into this pattern, the results of importation may not be what you want.

The Importation Algorithm

Here's what DataWallet does when importing a worksheet.

  • To determine the standard columns, it scans the first 1000 rows of the sheet to find the row with the greatest number of non-blank cells.  This is usually the first row in a simple sheet.
  • Captures the text in the cells of that row and uses them as column names for the dataset.
  • Scans the remainder of the sheet, converting rows.  Rows that are entirely blank are ignored. 

If more than 10 entirely blank consecutive rows are encountered the conversion ends.  If more than 1000 lines are converted you will be given a warning and asked if you want to continue the conversion, since it's unlikely that such a large amount of data needs to be stored in DataWallet.

Other Conversions

Excel supports other file formats; in particular, there is a simple format known as "comma-separated variables" (CSV) in which each line of text is composed of data fields separated by commas.  Since almost any data format can be rendered in this simple fashion, other file formats can be imported into DataWallet by a two step procedure: first, convert your data into a CSV file, then import it using DataWallet's Excel importation feature.