March, 2009 - Importing Nominations from an Excel Spreadsheet
Thursday, March 19, 2009
When you're creating nominations and meeting deadlines, every second counts. By importing nominations into California Gas Transmission's INSIDEtracc system directly from an Excel spreadsheet, you'll save time and effort. Whether you use your own custom-designed spreadsheet or the Gas Transportation Nomination Form suggested by CGT, placing your nominations in INSIDEtracc is as easy as Copy and Paste.
To use CGT's suggested spreadsheet:
- Navigate to Pipe Ranger's Reference/Library, Forms (link to same as above) page
- Click the link for the Gas Transportation Nomination Form
- Save the form to your computer
Type all your nominations directly into the form as it is, or modify the form by moving the columns around. You can also add up to seven additional columns in the spreadsheet for your personal use. For example, you may want to list the names of your source or destination IDs. If you are using your own custom-designed spreadsheet, you probably already have this information listed.
Setting Import Options
There are 17 required fields for any INSIDEtracc nomination:
Customer, Start Date, End Date, Create Source TSA, Source ID, Receipt Pt, Seller Code, Package, Source Rank, Create Dest TSA, Destination ID, Delivery Pt, Buyer Code, Destination Rank, Transportation Exhibit, Receipt Volume, and Delivery Volume.
It doesn't matter to INSIDEtracc which column your information is in, as long as all the critical information required for CGT nominations is included in your spreadsheet. However, prior to importing your nominations for the first time, you must set the Import Options in INSIDEtracc to map which information shows up in which columns. This enables CGT to process your data correctly, matching data in your spreadsheet columns with the appropriate nomination fields in INSIDEtracc.
If you use the default column order on the Gas Transportation Nomination Form you will not need to make any changes to the Import Options. If you have changed the column order or added columns to your spreadsheet, you must change the Import Options to match your spreadsheet. Here are the steps you will need to follow:
- Navigate to Nominations, Import (from INSIDEtracc's Main Menu)
- Click on Options
The Import Options screen will open and the From Clipboard/File section will be on the left hand side of the page. The Column Name section includes all seventeen columns listed on the Gas Transportation Nomination Form, plus seven "Ignore" columns that can be used for additional information not required for PG&E nominations, such as showing source or destination ID names or tracking specific nominations.
To set your Import Options, highlight the column name and click on the up or down arrow to reposition that column to match the location of that data in your spreadsheet. In other words, the column names displayed on the screen must be in the exact same order, from top to bottom, as they are in your spreadsheet from left to right.
Once that is done, choose the Date Format used in your spreadsheet (mm/dd/yyyy or dd/mm/yyyy). Click the Submit button to save the changes to your Import Options.
Note: Nominations may also be saved to a file and imported into INSIDEtracc. While importing from a file is an option for entering data into INSIDEtracc, importing from the clipboard is the easier, more direct way to import data into the system. For more information and assistance on importing your nominations from a file, please call the CGT Helpline for assistance.
Importing Your Nominations
Once your Import Options are set, you're ready to import your spreadsheet data.
- Open your Excel spreadsheet
- Select the nomination data for import (exclude any column headers, footers and/or blank rows)
- Edit, Copy
- Select the INSIDEtracc application (open INSIDEtracc if it is not currently open)
- Navigate to Nominations, Import, From Clipboard
- Paste your nominations into the yellow field
- Click on the Import button located on the lower, right side of the screen
Basic validations are done when your nominations are imported into INSIDEtracc, and you will now see them listed in a table with one or more information bars at the top of the page. A yellow information bar will tell you if you have any invalid nominations containing errors. A green information bar will tell you how many nominations are valid.
The Actions column in the table can display up to three icons.
Edit - The first icon will allow you to edit the nomination. If you click on the Edit Icon for an invalid nomination, the Edit Nomination screen will open up and you can make any changes to any of the yellow entry fields. Once you have made your changes, click the Accept button located at the bottom of the screen. This will update the nomination data and return you to the Import From Clipboard screen, where you will see that the nomination status changed from invalid to unprocessed. When all the errors are corrected you must hit the Submit button to save your nominations to the database.
Delete - The second icon is a toggle which will allow you to delete the nomination. When you click this icon, the nomination font will turn blue and the status column will change to delete. If you click on the icon again, the line will turn to black (indicating a valid nomination) or red (indicating an invalid nomination), and the status column will return to its original status.
Error Messages - The third icon will only appear if a nomination is invalid. Hold your cursor over this icon to see a float-over description of the main error message for the nomination.
Additional validations occur once the nominations are submitted, which may result in additional errors to correct. However, all nominations without errors will be successfully submitted to the INSIDEtracc database when you click the Submit button, and will no longer appear on the screen. You can repeat the steps outlined above to correct any additional errors as a result of the Submit validation process.
- Files imported into INSIDEtracc must contain consecutive rows of data. Blank rows within the data will cause errors.
- You can re-import your nomination file as many times as you like prior to any deadline. For example, you import a file that contains 20 nominations one-half hour prior to the Timely deadline. Ten minutes later, you make changes to 7 nominations. You can re-import your entire file, or just the 7 nominations that you've changed, which will update and overwrite those 7 nominations from the previous file. All 20 nominations will be processed once the deadline passes.
- After the deadline has passed, changes to nominations must be made in the Query/Replace Processed Nominations screen.
- After importing your nominations, there are two excellent INSIDEtracc screens to use to review your nominations: Query Pool Balance and Query/Copy Unprocessed Nominations.
- Import Options are computer-specific. If you set your Import Options on your computer at work and then work from home on the weekend with a different computer, you will need to reset the Import Options on that home computer as well.
- Any nominations in the Import From Clipboard table with a status of "Delete" or "Invalid" will not be saved to the database upon submit. The Invalid nominations will remain in the table; deleted nominations will be removed. When you close the screen, the Invalid nominations will be lost.
- The Source ID and Dest ID columns in your Excel spreadsheet should be formatted such that leading zeros are not removed.
- The Rec Vol and Del Vol columns in your Excel spreadsheet must not be formatted with a comma or a decimal; INSIDEtracc will not accept these characters when importing your volumes.
Print This Article as a .PDF File
Copyright © 2009 Pacific Gas and Electric Company.
All rights reserved.