Import Data from Excel or Text into the I-Data Table

Borehole Manager | File | Import | Excel | Column Based | I-Data
Borehole Manager | File | Import | Text | Column Based | I-Data

These Borehole Manager programs are used to read row and column data from a single worksheet in an Excel file or from a single text (CSV, tab-delimited) file, and import the columns into the I-Data table for a single borehole, or for multiple boreholes if the borehole IDs are listed in the spreadsheet. This is primarily designed for users who have measurements for multiple interval-data components, by depths, that can't be easily brought into the RockWorks database using the Row Based Excel import program. The import works with XLSX, XLS, CSV, and TXT files, provided that Microsoft Excel is installed on the user's machine.

! The Borehole Manager I-Data table is restricted to numeric values only. If you need to import alphabetic or alphanumeric data into RockWorks, use the I-Text import tool.

Here is an example of the kind of Excel data that can be brought into RockWorks using this program; this subset lists the borehole ID in the first column, measurement depth intervals in the next 2 columns, and different measurements in columns 4 - 9. The columns can be in any order.

! This import program allows you to import one or many columns from the spreadsheet.
! It also allows you to append new I-data columns to existing boreholes. For example, if the borehole already lists Ca and Ag columns, you can add new Gravel and Sand columns to the hole, with the result being all 4 I-Data columns for the borehole.
! This program also allows you to append new data to an existing column. For example, if the borehole already lists Ca and Ag columns for depths 0 - 50 and you import Ca and Ag data again for depths 51 - 100, the original data can be retained. Be sure to activate the Append to Existing Data option.

Menu Options
Step-by-Step Summary

Menu Options

• Filename: Click on the small open-file button to browse for the name of the Excel file (XLSX or XLS) or CSV (CSV or TXT) that contains the data to be imported.
• 
  
• Worksheet: If you are importing from XLSX or XLS, select the name of the worksheet in the Excel file that contains the row and column data. When you select this, the program should display data information in the Tracks section of the window. (If you are importing from CSV or TXT this step is not necessary.)
• 
  
• Change Delimiter: If you are importing from CSV, you can use this button to define a different column delimiter for the input file.
• 
  
• Data Contain Column Titles: This setting, on the right side of the Import window, allows you to define if the worksheet contains an uppermost row that contains the name of each data column, as in the "Bore", "Depth to Top", "Sand" and other headings in the example above. If checked, these names will appear in the Tracks listing to the left. If unchecked, the column names will be set to the more generic "Column 1", "Column 2", etc.
• 
  
• Data Contain Column Units: This setting allows you to define if the worksheet contains a second row in which the units for certain columns, such as feet or meters for depths as shown at the top of this page. This tells the program to ignore the second row and begin data import with the 3rd.
• 
  
• Append to Existing Data: Check this item if you are importing new data to existing tracks in the I-Data table which already contain data. This provides a means of appending new data. However the importer will NOT check for duplicates.
• 
  
• Borehole Name, Import for one Borehole: Here you can specify whether the import spreadsheet contains data for a single borehole or for multiple holes.
  • Check this box to select the name of the one existing borehole to which the I-data is to be recorded.
    ! The program will assume that there is only data for this hole in the import spreadsheet. If there is data for more than one hole, and the IDs are listed, you should leave this box unchecked as discussed next.
  • Leave this box unchecked if the data will be imported to multiple holes.
    ! This requires that there be a column in the spreadsheet that lists the borehole name.
    ! The borehole names in the spreadsheet must match the borehole name fields in the RockWorks project database.
  • 
    
• Input Column Name: This list, along the left, should contain all of the columns in your spreadsheet, either in a "Column 1", "Column 2" format (the Data Contain Column Titles option is not checked) or listing your actual column titles (the Data Contain Column Titles option is checked).
• 
  
• Available Fields: In this area you will see the names of the I-Data tracks you have already defined for your project, in the I-Data Types table.
  ! If your defined fields are not listed here, click the Types Table button to define your columns at this time. (See I-Data Types Tables for more information.)
  
  Your job here is to map the columns: For each selected spreadsheet Input Column Name, choose the I-Data track in the current database where the column is to be imported. You can either click and drag the Available Fields to the column names using your mouse, or you can click on the respective items and use the Add button to do the mapping.

• Tips & Reminders:

  • If you have specified that data for multiple boreholes is being imported (and the Import for One Borehole option is not checked), be sure that you map the spreadsheet column containing the Borehole ID to the "Track Name" named Borehole.
  • Be sure to map the columns containing the top- and base-depth values to the database Depth 1 and Depth 2 columns. This is required.
  • 
    
• Depth Multiplier: Use this setting to convert depth units as necessary. For example, if your database elevations and depths are in meters, but the import units are in feet, you can check this box and type in: 0.3048 so that the units will match.
• 
  
• Negative Data Values: Use these settings to define what to do with columns that contain negative values. They can be imported as-is, discarded altogether, or replaced with a user-specified value.
• 
  

Other tips

• Be sure the borehole(s) to which the data is to be imported already exists in the project database. You can't post I-Data to a borehole if it doesn't yet exist.
• Be sure that the data in the Excel sheet is sorted by borehole, if multiple boreholes are listed.
• Any non-numeric data will be skipped.
• 
  

Step-by-Step Summary

1. Access the Borehole Manager program tab.
   
2. If necessary, browse to the project folder in which you want to work.
   • Be sure that the borehole(s) to which you wish to import the spreadsheet data already exist in the project.
   • Be sure that the spreadsheet you want to import complies with the row and column format, and the sort order described above.
   • 
     
3. Select the File | Import | Text | Column Based | I-Data menu option to import from CSV.
   Select the File | Import | Excel | Column Based | I-Data menu option to import from XLS or XLSX.
   
4. If you are prompted whether you want to create a backup of your database before proceeding, we generally recommend that you choose Yes. Never hurts.
   
5. Select the name of the XLS or CSV file to be imported. The program will scan the file and load the names of the columns in the Input Column Name listing.
   • If you are importing from XLS you can use the Worksheet field to choose a different worksheet from the file.
   • If the input data does not contain column titles, uncheck the Data Contain Column Titles checkbox.
   • 
     
6. Next, specify whether you are importing for a single borehole or for multiple holes using the Import for One Borehole checkbox (as described above).
   
7. If you need to define the column names for your borehole project (or add to existing names) click the Type Table button and make the necessary changes to the I-Data Types table.
   
8. Map the project's column names to the spreadsheet columns, as described above. Be sure to map the borehole name column to the Borehole ID field. You only need to map those columns you wish to import.
   
9. Click the OK button to proceed.

The program will read the specified XLSX, XLS, CSV, or TXT file. It will extract the data from the requested data columns and map them to the indicated database fields. Any existing I-Data for the requested columns will or will not be overwritten as specified; all new I-Data columns will be added.

Back to Importing Data

RockWare home page