Importing Data from CSV or Excel into the Stratigraphy Table

RockWorks | Borehole Manager | File | Import | CSV | CSV File -> Stratigraphy
RockWorks | Borehole Manager | File | Import | Excel | Single Table | Excel -> Stratigraphy

These Borehole Manager tools are used to read row and column data from a single worksheet in Excel file or from a single CSV file, and import the rows and columns into the Stratigraphy table for a single borehole or for multiple boreholes. This is primarily designed for users who have elevation or depth measurements for multiple units, which can't be easily brought into the RockWorks database using the main Excel import tool.  The import works with XLSX, XLS, CSV, and TXT files, provided that Microsoft Excel is installed on the user's machine.

Here is an example of the kind of Excel data that can be brought into RockWorks using this tool; this subset lists the borehole ID in the first column, and elevations for all of the stratigraphic units  in the subsequent columns. This example shows formation tops and bases, but you can import tops only.  This example uses elevations; depths are also importable.

! This import tool allows you to import one or many columns from the spreadsheet.

! This tool allows you to either append new data from Excel to existing stratigraphic data in RockWorks, or to replace all existing data.

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 list below "Input Column Name".  (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 "Name", "Top:A-Horizon", "Base:A-Horizon" and other items in the example above.  If checked, these names will appear under the "Input Column Name" listing to the left.  If unchecked, the column names will be set to the more generic "Column 1", "Column 2", etc.
   
• Append to existing data: If checked, RockWorks will append the new data to any existing data in the Stratigraphy table. If not checked, all data will be replaced with the data being imported.
  
  
• Import for one Borehole: Here you can specify whether the import data is for a single borehole or for multiple holes. 
  • Check this box to select the name of the existing borehole to which the stratigraphy data is to be recorded.
  • 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 names which already in the RockWorks project database.
     
• Stratigraphy:
  • Import Tops Alone:  If you check this box, the program will import the elevations/depths for stratigraphic tops only.  If unchecked, you will be able to import the top AND base elevations/depths for each unit.  (See more on this below.)
  • Calculate Bases:  If you're importing tops only, you can request that the program compute the formation bases automatically.  Please note that it does this by setting the base of each unit to the top of the next unit, relying on the order of the formations defined in the Stratigraphy Types table. 
    ! If you request this calculation, be sure your formation order is defined correctly in the Types table.
  • Values represent:
    • Depths: Click in this button if your spreadsheet lists formation depths, from the top of each hole.
    • Elevations: Click in this button if your spreadsheet lists formation elevations.
      ! Note that the program will assume your holes are vertical if the data are imported as elevations.
       
• 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 stratigraphic formations which you have already defined for your project, in the Stratigraphy Types table.
  ! If your formations are not listed here, click the Types Table button to define your formations at this time.  (See Stratigraphy Types Table for more information.)
  
  Your job here is to map the columns: For each selected spreadsheet Column Name, choose the formation 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 database field named Borehole.
  • Be sure the borehole(s) to which the data is to be imported already exists in the project database.  You can't post Stratigraphy data to a borehole if it doesn't yet exist.
  • If you don't have formation names established at this time, you can click the Type Table, at the bottom of the window to create your Stratigraphy Types. 
  • Any non-numeric data will be skipped.
     
• 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.
   

Step-by-Step Summary

1. Access the RockWorks 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 described above, with data for each borehole on a single row and formation depth or elevations listed in columns.   If you are importing for multiple holes at this time, the borehole name must be listed in one of the columns.
      
3. Select the File | Import | CSV | CSV File -> Stratigraphy menu option to import from CSV.
   Select the File | Import | Excel | Single Table | Excel -> Stratigraphy menu option to import from XLS or XLSX.
    
4. 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.
      
5. Next, specify whether you are importing for a single borehole or for multiple holes using the Import for One Borehole checkbox (as described above).
    
6. Next, tell the program whether you are importing formation tops and bases, or just tops alone.  If the latter you can also select whether the bases are to be computed automatically (as described above).
    
7. Choose whether the data to be imported is recorded in depths below ground or as elevations.
   ! This is important.  RockWorks will accept either.
    
8. If you need to define the formation names for your borehole project (or add to existing formation names) click the Type Table button and make the necessary changes to the Stratigraphy Types table.
    
9. Map the project's formations 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 formations you wish to import.
    
10. 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 for the indicated boreholes.  If the input data is listed as elevations, they will be transformed to depths for posting to the database, based on the borehole's surface elevation already defined in the Location table in the project.  If you are importing tops only and have requested computation of formation bases, the program will do so at this time. 

Any existing stratigraphy for the requested columns will be overwritten.

Back to Importing Data

RockWare home page