Spreadsheet Import

Top  Previous  Next

This option allows you to import data from spreadsheets or virtually any other text data-source. The tool allows you to import data with any data order, separator and file format.


To access this option, select the "Tools->Import Text/Spreadsheet" option from the menu bar.




The tool works by reading the text in using user-selectable parameters and displaying the results. If the parameters are wrong, the displayed data will look wrong. When you get the format right, the data will appear correct.


Getting Started. To begin using the tool, simply press the "Open File" button. The program will display the data using the currently selected parameters.


Changing Parameters. The left-hand panel shows the currently selected parameters. Here is complete description of the parameters:


Item Order. The "Item-Order" section allows you to choose which data items are available in the file and the order the items appear in each row of data


The table on the left shows the currently chosen data items and the order in which they processed. The table on the right shows all the possible data items.


You can add items by selecting the items on the right and pressing the left-arrow button. You can delete and move items using the buttons to the left of the list.




The names in the list correspond to the normal survey items like From or Length. There is also a "Dummy" item, which is used to handle empty fields and or data that isn't normally a part of cave survey data.


You are only allowed to put one copy of each normal data items into the list. For example, you can only use one copy of items like From, To, Length, Azm1, Azm2 etc.  However, you can have as many Dummy items as you want.


Pressing the "Compass Default Order" button puts the items in the same order used by the "Spreadsheet Export Option."


Directly Manipulating Columns. You can also insert, delete and move the data item for each column directly.


Move Columns. To move items, you simply drag them from one column to another. For example, to move the "Length" item to different column, just click on the Length-cell. When you do this, a vertical line will appear. Drag the line to a different location and drop it. When you do, the item will move to the new location



Delete Items. To delete an item from the columns, right click on the column whose item you want to delete and the select the Delete option. The item will be removed and all the other items will shift to the left.




Insert Items. To insert an item onto the columns, right click on the column where you want to insert and select the "Insert Order Item." Finally, select the item you want to insert.




Separator. Most  data exported from spreadsheets uses a text character to separate each data item. Here is an example show data separated by commas:





The importer allows you to use any normally ASCII character as the separator. You just the ASCII character you want in the separator section.


The most common separators are comma and tab, so all you have to do is click on the Comma or Tab option to get that value. For other values, you enter the character the character by name or by hex or decimal number.



Quoted. Some times each data item will have quotes around it. If so, check the "Quoted" option.


Header Rows. Spreadsheet data can have one or more header rows that describe the data in each column. Since these row contain no cave survey data, they can cause problems if you attempt to import them. For this reason, the importer allows you to exclude any number of header rows. If you see header rows in the data, just increment the "Header Rows" value until all the rows are removed.




Checking the Format. As you make changes to the item-order and the separator, the program will reprocess the data and display the results. If the data appears wrong, adjust the order and separator until the data looks right. For example, this what comma-separated data looks like when it processed using a tab separator:




This is what it looks like using a comma separator:




Items that appear incorrect or are missing will appear in red. Fields that are missing or empty will show the word "empty."




When you save the data to a cave file, the program will make an attempt to use reasonable value for missing, illegal or bad values. Missing text-string items like station names will use the word "empty" in the Compass data files. Missing or defective numerical items will use a value of zero.


Raw Data. To help you choose the correct item order and separate, you can view the raw file data by clicking on the "Raw Data" tab at the top of the right hand panel.


Export Parameters. Clicking on the "Export Parameters" tab on the left show the Export Parameters page. It allows you to set a Cave-Name, a Survey Name, a Survey-Team, a UTM zone and whether the imported data was in meters or feet.


Save/Load Default. The program allows you to save all your settings to be used as defaults. Pressing the "Save Defaults" button saves the following items:


Header Row Count, Quoted Flag, Separator, Cave Name, Survey Name, Survey Team, Default Zone, Units, and Item Order.


These saved items are automatically loaded when the program starts. You can also load the by pressing the "Load Defaults" button.





Saving Data. When you've got all the settings the way you want, press the "Save File" button and choose a filename. The program will create both a project (*.mak) file and one data file (*.dat). Once the files have been created, they can be loaded into Compass and manipulated by all the Compass tools.