Import Data – Advanced

With the introduction of the IMPORT feature two new keywords have been created: REF1 and REF2. These Reference rows in the GLSU template work with the IMPORT feature to build moderate to highly complex data imports. Here is an example of what one looks like a GLSU template.

The REF1 line is tied to the Primary Map, and REF2 is tied to the Offset Map.

Let’s take REF1 for example. Normally, when a Primary Import map is created, you map what you like and may or may not leave some column blank. If there is no REF1 row the missing values will import as blank as expected. However, what if the requirement is that the posting key is 40 for every row?

Looking at the example above notice that REF1 has a posting key of 40. This is constant. As long as the Posting Key column is not mapped, the value 40 will be used for every primary record. Some imports may only have a 1:1 correlation between the number of records in the file and the number of records being imported. However, if an Offset Map is created, we have a 1:2 correlation between the number of records being imported and the number of records actually imported. The Primary Map will always import data but the Offest Map will import data only if it is turned on in the mapping screen.

Let’s take a look at the mapping screen for the example above. Notice how the map displays the incoming data with a REF1 row in place.

Notice how the REF1 value bleeds through to the Primary Map. This means that for every row that is imported the value of posting key will be 40. It’s a constant.

The same is true for the Offset Map and the REF2 row. In this case, the Offset Map would have blanks (unmapped columns) for both Posting Key and Account. The REF2 row will fill in those blanks as the records are imported.

There are a few neat tricks that the REF rows can be used for. How about coloring the background of the REF1 row light green? The effect would be that the import data would load and produce a green-bar paper effect. REF1 and REF2 lines like this...

It would produce this look in the imported sheet. It could help when trying to determine which imported records are from the Primary Map and which records are from the Offset Map.

Note that the REF1 and REF2 rows can be hidden once they are set up and tested.

What about calculations? Yes, calculations can be used as well. It is possible to derive data for, say, the Profit Center column, based on Account and anything else on the imported sheet. A VLOOKUP() could be used on Account to lookup the associated Profit Center, for example.

Note also that if only the Primary Map is filled in and the Offset Map is "inactive", then only a REF1 row will be needed. Having a REF2 row will have no effect. If the goal is to have the REF2 row fill in all of the columns for every record loaded, then the Offset Map would be activated but not mapped and a REF2 with constants and/or calculations would be supplied. Perhaps all of the columns in the offset row are always the same and only Amount value changes from record to record. In that case, use the above approach but map the Amount column in the Offset Map.

You can load a fixed length file as follows,

1. Use Excel’s File Open… (fixed-width mode) to pen and slice the file into columns as desired.
2. Save the resulting workbook as a Tab-Delimited (.txt) or Comma Separated variable (.csv) file.
3. Use the standard import method for delimited filed to load the data into your GLSU template.