 |  |
How To: Configure a Load Activity Import
1. Background
This feature is
only relevant for those users who track workers and/or equipment by activity on
load slips.
The Logger's Edge allows you to define an import format so that you can take standard files (e.g., Excel files or tab delimited files) and import them into The Logger's Edge. Once you have entered or imported your loads you can also import your load activities, including the workers and equipment that performed each activity.
Assuming you set up your activity import file the same way, you will be able to import the load activities through a one-step process, assuming you have all your load information (i.e. each load ticket) is already entered in The Logger's Edge.
2. Steps InvolvedPreparing the File in Excel
- Open the file (or create a new file) in Excel. Make sure that row 1 has the column header information, and the data start in row 2 of the file. If not, manipulate the file accordingly. (If the file does not have column headers, the data should start in row 1 of the file.)
- You must have the Ticket Number and Pay Activity. The Worker, and Equipment are optional, but highly recommended. If you must add items (columns), be sure that you add them into the same column each time you create an import file; otherwise you will have to change your import configuration format which can be a hassle. Also, be sure that the pay activity, worker and equipment values have already been set up in The Logger's Edge. You also must ensure that each ticket has already been entered into The Logger's Edge or the row in the import will fail.
- Save the file as a tab delimited file (that is a “.txt” file) using the File | Save As menu item in Excel.
Excel File Example:
How to save:
Be sure to select 'Tab delimited' as the file format. The import will NOT work with a file format other than tab delimited -- If you try to import an Excel file, the import will most assuredly bomb in a big way.
Setting up the File Exchange in The Logger's Edge
1. In The Logger's Edge, select File | Setup File Exchange | Load Activities. You will see a window like the following, but you will have nothing in your list. In this example, I have a generic format for my load activities.
Click OK. Respond “Yes” to the question: “Import Format does not exist.
Create New?”. You will now see a window like the following:
If your import file includes a header field, check the “Data Includes Field
Header in Row 1” checkbox.
2. Click the “Import Test File” button. Browse to the text file that you created from within Excel. (Note: the file must be a tab delimited file with a “txt” file extension in order to import it.) Find your file, highlight it, and click the open button. Your window should now look something like this:
You will see the columns for the tab delimited file displayed in columns in this
grid. If your file has a header row, you will see the header row displayed
in row 1 of this grid. Scroll to the right to see all the columns that
The Logger's Edge
recognized from your file.
3. The next step is to tell The Logger's Edge which of the columns are ones that you actually want imported and to tell it which field in the The Logger's Edge load activities table corresponds to that column. For example, suppose you want import the field labeled “Ticket No” from your import file. To tell The Logger's Edge that you want to import this field into the corresponding field called “Ticket No” in the The Logger's Edge database, you would use the drop-down box in row 6 in this grid (the “Load Field” row in the grid). Click on the box to display the drop-down list, then find the The Logger's Edge field in the drop down list that logically corresponds to the column you want to import from the file (TICKET_NO in this case). The Logger's Edge will interpret this setting to mean: “when importing each load during a file import, take the value in this field for each load and put this exact value in the 'Ticket No' field on the load slip.” In the example below, the import file has a 'Ticket No' of “1065” for the first load that is displayed in this grid. When The Logger's Edge does the import, it will look for Ticket “1065” in the list of loads you have already entered in the system. If it doesn’t find a ticket with a code of “1065”, you won’t be able to import the load activities for that ticket.
Likewise, the activity in the first row is 'DELIMB'. In order to import this row you must already have an activity with the activity code of 'DELIMB' set up.
4. When you are finished mapping your entries, you can test your import by clicking the 'Save & Test' button. This function will run a test of the import for 5 load revenue records (see below). The function will not save any load revenue data, but just checks for errors . The lower grid will show the results of the import that you can review to ensure that the import data is properly aligned with the database entries.
5. When you are finished, click OK.
Importing the Load Activities File
When you have finished with your load revenue import configuration, you will then want to import the revenue records into The Logger's Edge.
1. In
The Logger's Edge, select Data Entry | Load
Slips | Import Load Activities. You will see a window like the following:
The top left of the window shows the import formats you have set up.
Choose the one you want to use for the set of loads you are about to import.
Use the bottom left of the window to navigate to the location of your “txt” import file.
The bottom right panel shows all files (regardless of file type) stored in the folder you selected on the left. Be sure you choose the “txt” file and not the original “xls” file when you try to import.
2. Click the “Import” button. At this point, the system will attempt to import all the load revenue records in the file. It will import those load revenue records that it can successfully import, but will generate errors for those loads that can’t be successfully imported.
If you already have load activities on a load (or loads) you will receive the following message:
The options are as follows:
• Quit: This exits the import immediately
• Skip Once: This skips the activity (once for each load); will save any activities for new loads.
• Skip All: Skips all existing load activities; will save any activities for new loads.
• Update Once: Updates current load; will prompt for each load.
• Update All: Updates all loads.
The load
activities import is somewhat finicky. Here a rule you need to be aware
of: If you click 'Update' (either Once or All),
The Logger's Edge
will delete all preexisting activities
and then insert the activities from the import file. This rule assumes the
the import file has a comprehensive list (not an incremental list) of activities
for the loads in the file. It is not possible to just add activities in
the import -- your file must contain and activities workers and equipment.
The result of the import is that the
The Logger's Edge
database will mimic exactly (nor more or less) what is in your import file.
In terms of troubleshooting the import errors, there are a number of common problems that people encounter. The following list should help you in your troubleshooting.
• Confirm that you have set up in The Logger's Edge all the attribute values that are on the loads. For example, maybe the load revenue record has an activity of 'DELIMB', but you have not yet set up the activity DELIMB or perhaps you set it up but called it DELIMBING instead.
• Confirm that your naming convention exactly matches the naming convention used in the file. As noted above, if the file calls an activity “DELIMB”, but you call it “DELIMBING”, it will fail upon import. You can either: change the value in the file to be DELIMBING and re-try the import; rename your activity to have a code of “DELIMB” instead of “DELIMBING”; or using the Import Translation Table functionality to tell the system that if it encounters a value of “DELIMB” to import it as “DELIMBING” during the processing. See the appendix for more details on using the import translation tables.
• If you manipulate columns in your spreadsheet when you do your import, make sure that you are doing the exact same manipulation that you did when you created the import format. For example, some mills do not supply certain fields (such as destination or driver) in their import file. Users may find it easier to add those columns into the import file prior to import, since mass edits in Excel are more efficient than mass edits in The Logger's Edge. If you add columns, they must be added in EXACTLY the same position each time you do the import – otherwise, The Logger's Edge will not recognize the import data.
3. Appendix
This Appendix provides more detail on some of the advanced functionality associated with load imports.
Using the Import Translation Tables
Many users do not adopt exactly the same naming convention as the mills they serve, or they serve multiple mills that don’t all use the same naming convention. If you find yourself in this circumstance, you may wish to use the import translation feature.
Select the menu item File | Setup File Exchange | Import File Translations. The screen captures below shows this window where two “translations” have been set up, both for use in the “Std Load Activities” file import.
The way to interpret the rows set up here (with the Add button) is: for the
import, when importing the field in the file that you’ve indicated is the
PAY_ACTIVITY_CODE (by using the drop-down selections shown in step 4 above), any
time the system encounters a value of DELIMB in the import file, it should
convert the value to “DELIMBING”
(the “related DB value”) when writing the load into the
The Logger's Edge
load activities
table.
You can repeat this process for any field in the import file that you’d like, simply by adding a new row for each field in a given import.
Using the Advanced Features in the Import Definition Window
You may have noticed when we configured our import file in the example above, we did not make use of a number of options that were available in the bottom of the window.
Match on Description: If you check (i.e., click on) the “match on description” box for a given column, the system will match the value in the import file with the description rather than the code you have used in The Logger's Edge when you set up the given item. For example, you may have set up an activity code to be “DELIMBING”, but the description to be “DELIMB”. If your use the terminology “DELIMB” in the import file, you’ll want to use the checkbox and match on description (where the description is 'DELIMB').
Source Format: This row is typically used for specifying a date
format that is different from the standard date format the system normally uses.
If your import fails on the DATE_IN or DATE_OUT field, you may need to tell the
system how the field in the “txt” is formatted. See the following screen
capture as an example, where the date field in the txt file is in a month,
day, year (MM/DD/YYYY) format:
Source Concatenation: This row is useful when you need to blend two
fields in the txt file into a single field for import into our system. The
most common use for this functionality is to merge a date and time when the two
fields are in separate columns in the txt file. For example, to
concatenate columns 9 (date) and 10 (time in) and import it into the “Date In”
field, you would put the two column numbers you are concatenating into square
brackets (with no spaces) in the “Source Concatenation” row in the column you
are indicating as the “Date In” column.
Trick
A common trick that many users find enhance the efficiency of their activity imports is as follows:
1. Import load slips. When load slips are imported (or keyed in), The Logger's Edge will create dummy activity entries for each activity specified on the block, filling in the worker as 'NONE' and the equipment as 'NONE'
The snapshot above shows the dummy entries for a batch of imported loads.
2. Export the grid to Excel. By using the 'Export' button you can export the grid of dummy entries to Excel. In Excel, you can manipulate the file (by sorting and grouping) to enter your workers and/or equipment much faster than in The Logger's Edge.
3. Once you have finished your worker/equipment assignments in Excel, you save the file as a tab delimited file (like above) and import the file into The Logger's Edge.
Voila. easy as pie.
