High Capacity Pumpage Electronic Reporting Format

Using the Microsoft Excel Template

For your convenience, we have provided a Microsoft Excel spreadsheet that is pre-formatted into a template for the data you are required to submit. A tab delimited file format allows us to more easily move your data into our database. By following the directions below, you can fill in the template with your data, save the file and submit it via email to the Department.

The template has the columns defined based on either character or number length, ranges or lists. Before using the template, review the Data Elements (below and within the Excel file) to determine what and how data should be entered in each column. Multiple wells should be entered on a single Excel sheet.

Some error checks are built in to the template to help you catch errors on field length maximums, data types and number ranges according to the definitions in the Data Elements table. Error checking occurs upon leaving a field and an error box will appear that states the error and gives you the option to retry or cancel the entry.

Columns E and F ask for Method Codes and Water Use Codes respectively. Those code lists are included in the Excel workbook as separate sheets for your reference. You can switch between the different Excel sheets within the file by clicking the tabs located near the bottom of the window. The Method and Water Use code lists are also listed on the instruction sheet for the paper Hicap Pumpage Form (Form 3300-304).

Owner/Operator and Well Location templates are also included in the Excel file. If you have additions or changes to well owner, well operator or well location information listed on your Hicap Pumpage Form, you can make the changes on the appropriate template(s) and submit your tab delimited files at the same time. Each template you complete must be saved as a separate TXT file but can be submitted together in a single email message.

Using Excel to Create a Tab Delimited (TXT) File

• Step 1: Open the 2007 Pumpage Excel spreadsheet template.
• Step 2: Enter the data into the "Pumpage Report" sheet for each well. Clicking inside a box of the sheet will tell you what data goes in that box. Use one row in the sheet per well. Reference the Data Elements sheet within the file or the table below for details on how the data must be formatted.
• Step 3: To save your pumpage data, have it open to the "Pumpage Report" sheet, go to "File", then "Save As" from the menu. Do not use the save icon in the navigation window. You will be saving the file as a tab delimited file (a file with a TXT extension). Click on the "Save as type" drop-down box in the Save As window and scroll down to Text (Tab delimited) (*.txt). Change the filename to the first hicap well number in your pumpage report followed by an underscore then the pumpage year. For example, if the first hicap well number in your report was 00000, the filename would be "00000_2007.txt". When you click the "Save" button, you will get a popup window that tells you that you can only save the sheet as a delimited file, not the whole workbook. Click "OK" to save the file. It is strongly recommended that you also save the delimited file(s) or the whole workbook to your computer.
• Step 4: (Optional): If you need to make changes to the Owner/Operator or Well Information data that is pre-printed or omitted from your Hicap Pumpage Form, make the necessary changes on the respective sheet(s) in the Excel file and save them as separate tab delimited files in the same manner as Step 3 above. Use the Owner or Operator ID (the one that is being changed) followed by "_change.txt" for the file name of owner/operator changes and a Hicap Well Number (which has location changes/additions) followed by "_change.txt".

  You can make changes to the existing owner's information, change operator or operator's information or correct/add well location information. Owner and Operator changes can go on the same sheet as separate rows. The Hicap Well Number and Owner ID must be used as a reference to owner information changes, Hicap Well Number and Operator ID are needed for operator information changes, and the Hicap Well Number is needed for well information changes. If the ownership of a well has changed during the year, you must complete the Hicap Well Application form (Form 3300-256) and mail it to the Department.

• Step 5: Send an email to DNR HICAP PUMPAGE and attach your saved TXT file(s) as an attachment(s). Use "Electronic Reporting" in the subject line of the email.

NOTE: By submitting your pumpage report via email, you are certifying that your pumpage data is true and correct to the best of your knowledge.

Data Elements List

Data Elements are in the following order for each record. A single record (one row in the sheet) represents a full year's pumpage for an individual well. The length of each Data Element must not be greater than the maximum listed. No punctuation (comma, apostrophe, period, etc.) should be used in the spreadsheet.

Excel Column	Data Element	Maximum Length	Format	Field Description
A	Hicap Well Number	5	Numeric	DNR identifying number for the well
B	Owner ID Number	6	Numeric	DNR identifying number for the well owner
C	Operator ID Number	6	Numeric	DNR identifying number for the well operator
D	Report Year	4	Numeric (4 digit year)	Calendar year of pumpage
E	Method Code	4	Character	Code for the method of measuring or estimating pumpage (see reference list)
F	Water Use Code	5	Character	Code for the majority of water use during the year (see reference list)
G	January Pumpage (Gallons)	9	Numeric	Pumpage for the month
H	February Pumpage (Gallons)	9	Numeric	Pumpage for the month
I	March Pumpage (Gallons)	9	Numeric	Pumpage for the month
J	April Pumpage (Gallons)	9	Numeric	Pumpage for the month
K	May Pumpage (Gallons)	9	Numeric	Pumpage for the month
L	June Pumpage (Gallons)	9	Numeric	Pumpage for the month
M	July Pumpage (Gallons)	9	Numeric	Pumpage for the month
N	August Pumpage (Gallons)	9	Numeric	Pumpage for the month
O	September Pumpage (Gallons)	9	Numeric	Pumpage for the month
P	October Pumpage (Gallons)	9	Numeric	Pumpage for the month
Q	November Pumpage (Gallons)	9	Numeric	Pumpage for the month
R	December Pumpage (Gallons)	9	Numeric	Pumpage for the month
S	Person Completing Form	60	Character	First and last name of person submitting this data
T	Email Address	60	Character	The full email address of the contact person for future correspondence about this well.

Note:
All data elements are required for every record. If there is no pumpage during a month, enter zero (0) gallons.

If you have questions about submitting your data using this format, email Kathy Mooney or call 608-267-7449.