User Tools

Site Tools


spreadsheets

Prepare Spreadsheet for Import

This page will guide you through setting up your spreadsheet. It will cover Well Production and Well Interest from modifying the tab names, verifying column names, and concatenating columns in Microsoft Excel.

Back to Minerals Home

Header Maintenance

The column header needs to be Row 1, as seen in the image below.



As you can see below, the spreadsheet has 6 rows above the header row. Delete the 6 rows at the top, which will make the column headers row 1 as expected and look like the image above.


Well Production Spreadsheet

- Modify Tab Name

  • The sheet tab for the Well Production should be named MCF.

- Verify Column Names

  • Make sure the spreadsheet has all the required columns. Here are the Required columns for Minerals Import Well Production spreadsheet.
Required columns (In Red)API NumberPermit NumberWell IDWell NameProduction AmountDepth
Other columnsProduction StatusAsset TypeCalculation MethodOperatorTransporterAsset UnitAsset CountLegal DescriptionSectionTownshipRange
  • Here is what the Well Production spreadsheet should look like:


click image to enlarge

Well Interest Spreadsheet

- Modify Tab Name

  • The sheet tab for the Well Interest should be named DOI.


- Verify Column Names

  • Make sure the spreadsheet has all the required columns. Here are the Required columns for Minerals Import Well Interest spreadsheet.
Required columns (In Red) Well IDOwner IDOwner NameAddress 1Address 2CityStateZip CodeInterest TypeInterest Amount
Other columnsLegal DescParent IDSectionTownshipRange
  • Here is what the Well Interest spreadsheet should look like:


click image to enlarge

- Additional Spreadsheet Manipulation

The standard expects one (1) Owner Name and two (2) Address line columns. If your spreadsheet contains more than the expected number of columns for these two fields, follow the instructions below to combine them into the expected number.

- Owner Name Concatenation

  • Open the spreadsheet to the DOI tab. As you can see in the image below, this spreadsheet contains two owner name columns when only one is expected.


  • Right-click on column D (Owner Name 1) and click Insert to create a new column. Then click in cell D1 and enter the column heading “Owner Name”.

Please Note: During import, the program will find the expected columns based on the column name/header. This is why the spreadsheet can have additional columns other than those expected since they will be ignored. However, if more columns exist in the spreadsheet than expected, the program will only load/save the expected number, which could result in data loss. Therefore, after concatenating (combining) fields, rename the original column names/headers to something recognizable without being close enough to get mapped as “the column”.

  • Due to the note above, column E and F need to be renamed, so “nm1” and “nm2” were chosen so that the names where recognizable as the original Owner Name columns, without either being mapped as the “Owner Name” column.


  • Now you need to concatenate (combine) the two Owner Names into one column in cell D. Click into cell D2 and then into the Formula Bar. You need to concatenate cells E2 and F2. Now type
    =CONCATENATE(TRIM(E2)," ",TRIM(F2)) 

    into the Formula Bar (as seen in the image below) and then hit the Enter key. To apply the formula to all rows in column D, click into D2 and locate the box in the lower right corner as circled in the image below. The cursor should turn into a plus sign (+) and then double-click.

  • As you can see in the image below the “Owner Name” column has been successfully concatenated.


If desired you have the ability to Hide the renamed columns by right-clicking the column and then click Hide.

- Owner Address Concatenation

  • Open the spreadsheet to the DOI tab. As you can see in the image below, this spreadsheet contains four address columns when only two are expected.

  • Right-click on column H (Address 2) and click Insert to create a new column. Then click in cell H1 and enter the column heading “Address 2”.

Please Note: During import, the program will find the expected columns based on the column name/header. This is why the spreadsheet can have additional columns other than those expected since they will be ignored. However, if more columns exist in the spreadsheet than expected, the program will only load/save the expected number, which could result in data loss. Therefore, after concatenating (combining) fields, rename the original column names/headers to something recognizable without being close enough to get mapped as “the column”.

  • Due to the note above, column I, J, and K need to be renamed, so “add1”, “add2”, and “add3” were chosen so that the names where recognizable as the original Address columns, without either being mapped as the “Address 1” or “Address 2” columns.

  • Now you need to concatenate (combine) the “Address 2”, “Address 3”, and “Address 4” columns into one column in cell H named “Address 2”. Click into cell H2 and then into the Formula Bar. You need to concatenate cells I2, J2, and K2. Now type
    =CONCATENATE(TRIM(I2)," ",TRIM(J2)," ",TRIM(K2))

    into the Formula Bar (as seen in the image below) and then hit the Enter key. To apply the formula to all rows in column H, click into H2 and locate the box in the lower right corner as circled in the image below. The cursor should turn into a plus sign (+) and then double-click.

  • As you can see in the Addresses have been successfully concatenated.


If desired you have the ability to Hide the renamed columns by right-clicking and then click Hide.

Back to Minerals Home

Cloud Words

spreadsheets.txt · Last modified: 2019/03/21 09:35 by ais