 |  |
How To: Configure a Load Import
1. BackgroundThe Logger's Edge allows you to define an load import format so that you can take standard load files (e.g., Excel files or tab delimited files) that a mill sends you and import them into The Logger's Edge. If you deliver to multiple mills, you will likely need to define a format for each mill, since each mill is likely to provide you with the load data in different formats.
Assuming any given mill provides you with load data in the same file format consistently, you will only need to define the import format for that mill once. Thereafter, you will be able to import the loads through a one-step process, assuming you have all your load information (e.g., trucks, blocks, etc) already set up in The Logger's Edge.
2. Steps InvolvedPreparing the File in Excel
- Open the file provided by the mill 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.)
- If there are any fields that you want to import that are NOT in the file provided by the mill (e.g., Destination or Species), add the columns into the file directly in Excel before you try to import it. Be sure you add the fields in the same column in the file each time, or it will not import properly.
- Be sure the date fields (date in and date out) have the date and time in
a single column. The ideal format would be something like this:
7/16/2004 8:07:31 AM. If the date and time are stored separately, you’ll need to concatenate them before import. - Save the file as a tab delimited file (that is a “.txt” file) using the File | Save As menu item in Excel.
Setting up the File Exchange in The Logger's Edge
1. In The Logger's Edge, select File | Setup File Exchange | Loads. You will see a window like the following, but you will have nothing in your list. In this example, I have a format set up already for L&M Lumber called “LM.” I will now add another one called GP for Georgia Pacific.
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 loads table corresponds to that column. For example, suppose you do not want any of the fields from the import file until you hit the column labeled “TRK ID” from your import file. To tell The Logger's Edge that you want to import this field into the corresponding field called “Truck 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 (TRUCK_CODE 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 'Truck No' field on the load slip.” In the example below, the import file has a TRK ID of “BPE052” for the first load that is displayed in this grid. When The Logger's Edge does the import, it will look for Truck “BPE052” in the list of trucks you have already set up in the system. If it doesn’t find a truck with a code of “BPE052”, you won’t be able to import the loads.
Note, of course, that this rule assumes that you and your mill each using the same naming convention for each truck. There are ways to handle different naming conventions, as described in the Appendix to this document.
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 loads (see below). The function will not save any loads, but will just check 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 File
1. In
The Logger's Edge, select Data Entry | Load
Slips | Import Loads. 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.
The top right panel allows you to set the pay date that will be used on all loads you import in this batch. In general, you will want the pay date to default to the date in shown on the ticket. If, however, the mill sends you a batch of late tickets from a pay period that you have already paid, you can still import the late tickets and retain the delivery date. Just set the pay date to a date that is in the pay period you are currently processing, and the tickets will be picked up in a pay period encompassing that pay date.
2. Click the “Import” button. At this point, the system will attempt to import all the loads in the file. It will import those loads that it can successfully import, but will generate errors for those loads that can’t be successfully imported.
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 has a truck # of 434, but you have not yet set up truck #434.
• Confirm that your naming convention exactly matches the naming convention used in the file. For example, if the file calls a block “754”, but you call it “BL-754”, it will fail upon import. You can either: change the value in the file to be BL-754 and re-try the import; rename your block to have a code of “754” instead of “BL-754”; or using the Import Translation Table functionality to tell the system that if it encounters a value of “754” to import it as “BL-754” 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 “LM” file import (the import for L&M Lumber).
The way to interpret the rows set up here (with the Add button) is: for the LM
import, when importing the field in the file that you’ve indicated is the
BLOCK_CODE (by using the drop-down selections shown in step 4 above), any time
the system encounters a value of 754 in the import file, it should look for a
value of “754 Blk 152-COR” in
The Logger's Edge list of blocks, and use that value
(the “related DB value”) when writing the load into the
The Logger's Edge
loads
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.
If you make use of this feature, you’ll need to add a row here each time you set up a block (or species or sort or destination, etc) with a different naming convention than that used by your mill.
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 destination code to be “GC”, but the description to be “GRANDE CACHE”. If your mill uses the terminology “GRANDE CACHE” in the import file, you’ll want to match on description. This option allows you to map the mill's import file description to your code.
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 11 (date) and 12 (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
The Concatenation can also do math! For example suppose my import file has the gross and tare weights, but not the net weight. I can use the import to calculate the value of the net (= gross - tare). See Below. In column 22, I have selected 'Net' as the field to import and the formula '=[13]-[14]' for the source concatenation. The equal sign '=' is required. A mathematical operator is required.
The key to the trick is you need to select a column in the import file that would not otherwise be imported.
