Excel and EntomoLabels – or how to make your life easier – part 3 of 3 – Data import

To import data from CSV files to EntomoLabels you need to create the import templates you need. Import templates are just like regular templates, but contain additional information that is used by the import wizard to include data from imported files. The column names in such templates are enclosed in curly braces.

For starters, simple templates …

The graphic below shows how the data from the CSV file is combined with the import template. This is a simply template with the name of the species for the unit trays I use.

Using the same data, it’s easy to make a template with determinantion labels.

The bottom field requires some explanation.

 

And a more advanced example:

When we import data, we do not know exactly how big the label must be for all the data to fit on it. When designing the import template, we need to define the size of the label so that there is enough space on it. For example like this:

<import6>

Instead of a border, you can only make lines on the left and right side of the label, they will make it easier to cut the labels.
Now let’s take a look at the template’s data and content.


The content of the expression to be imported looks like this:

{Country}{UTM^, }
{Place}
{CollStage$ }{CollDate$/n}{Plant$/n}{Ex$ }{HatchingDate$/n}{Legit^Leg. }

{Country}
{UTM^, } – prefix = comma and one space
{Place}
{CollStage$ } – postfix = one space
{CollDate$/n} – postfix /n = new line
{Plant$/n} – postfix /n = new line
{Ex$ } – postfix = one space
{HatchingDate$/n} – postfix /n = new line
{Legit^Leg. } – prefix – Leg. and one space

When the content of a column in the imported file is empty, no prefix and postfix are put on the label.

 

And another example of a template – labels for slides. Import template and result.

One more example – how to import a sex mark (gender symbol?) ?
You already know how to import data into text fields. Similarly, you can import graphics. There is a sex mark on the labels for slides.

In order to import the sex mark, the import tag must be defined appropriately. If in the database (Sex column) we mark the gender with single letters M / F or space, the tag may look like this:

Sex_{Sex}.bmp

after substituting the data from the import, we will get Sex_M.bmp, Sex_F.bmp or Sex_.bmp make sure that files with these names are in the folder .. \ Graphics

Hope you find this information useful. Whenever you need help with EntomoLabels, you can write to me, I will be happy to help.

 

Excel and EntomoLabels – or how to make your life easier – part 2 of 3 – Data export

Based on the actions from the first part – we have already created a database and a macro that supports data export.

Regardless of how much data the database is, we usually only need to export a small part of it. It can be several contiguous rows or many non-contiguous rows ranges. Excel does not have such built-in tools, this is what we will use the created macro for.
I designed the macro in such a way that no matter what range of cells you select, the range from the first to the last column of all selected ranges is exported, e.g. if you select A8 : B10 and R15 : V18, the range A8 : V10 and A15 : V18 will be exported and automatically the first row with headings.
EntomoLabels requires that the first row of the CSV file contains the column names and the following rows the data. This is exactly how the file formats the macro.

Next steps for import:
1. Select the range or ranges of data you want to export
2. Click the View tab (1) and then the Macros button (2)

3. In the dialog box, select the macro ExportEntomoLabels_CSV
4. Press the Run button

5. Enter the file name and press Save. The default file location is ..EntomoLabels \ Data, but you can save it anywhere on the disk

This way you can export any data to CSV files. Species lists are very useful, as they will allow you to quickly prepare the necessary determination labels or, for example, labels for unit trays.


Excel and EntomoLabels – or how to make your life easier – part 1 of 3 – Database

In the next few posts, I will show you how you can use a simple specimens database in Excel and how it can be used to generate labels in EntomoLabels.

Each collection of specimens grows over time. It is then necessary to create a database that will allow for quick information retrieval. A quick, easy and effective way is to create such a database in Excel. In the database you can store all information about specimens, if necessary, to expand it by adding new columns.
Below is a description of the structure of such a database and a file with sample data.

DATABASE structure:
————————–
SpecimenID – unique specimen number, if you have a specimen
SlideID – a unique slide number, if any
SlidePrep – name of the person who made the slide
Medium – slide medium
Stain – slide stain
WetID – unique number of the wet specimen (alcohol, glycerin, lactic acid, etc.)

Family
Sex – M – male / F – female / empty – unrecognized
Genus
Species
Author
Det – the name of the person who determined the specimen

Country
UTM
Place
CollStage – at what stage the specimen was collected Larva / Pupa / Ovo / Empty – imago
CollDate – colleced data
Legit – the collector’s name
Plant – the food plant on which the specimen was grown
Ex -the stage at which the specimen was grown e.l. – ex larva / e.p. – ex pupa / e.o. – exovo
HatchingDate – hatching date
Cult – breeder’s name

Note – additional information about the specimen, physical condition, need to verification, etc.

LoanFrom – from whom / where the specimen was loaned from
LoanDate – when the specimen was loaned
LoanTo – to whom the specimen was loaned
BackDate – when the specimen was returned
LoanNote – return date agreed, specimen condition etc.
—————–

Sample database:

Sample Lepidoptera collection database  Sample Lepidoptera collection database

 

Create Macro to ease export data:

On Menu click View (1) and Macro (2)

In dialog windows insert macro name (1) and press button Create (2)

Copy and paste code below, press Save (1) and close window (2)

Macro source code:

Sub ExportEntomoLabels_CSV()

Dim el_filename As String
Dim nwb As Object, slcn As Range

On Error Resume Next
Set slcn = Application.Selection
On Error GoTo 0
If slcn Is Nothing Then Exit Sub

el_filename = Application.GetSaveAsFilename(Environ$(“USERPROFILE”) & “\Documents\” & “EntomoLabels\Data\” & CSVfileName, filefilter:=”Comma Separated Text (*.csv), *.csv”)
If el_filename = “False” Then Exit Sub
el_filename = LCase(Trim(el_filename))
If Len(el_filename) = 0 Then Exit Sub

Set slcn = Intersect(Union(Rows(1), slcn.EntireRow), slcn.EntireColumn)

Application.ScreenUpdating = False
Set nwb = Workbooks.Add
slcn.Copy nwb.Sheets(1).Range(“A1”)
Application.CutCopyMode = False

nwb.SaveAs FileName:=el_filename, FileFormat:=xlCSV, Local:=True
nwb.Close False

Set nwb = Nothing
Set slcn = Nothing
Application.ScreenUpdating = True

MsgBox “The selected data has been saved in : ” & el_filename, vbInformation

End Sub

How the EntomoLabels template is built

By learning about the structure of the label template in EntomoLabels, you will understand why sometimes you click on an object and a different one is selected, not necessarily the one you clicked on. In the graphic below you can see the template “from above” and its side layered cross-section.

When starting to design a template, we set its size – with the use of controls to set its width and height.

Then we add any number of elements to the template, specifying their parameters (eg size, position, font, line thickness …) using the controls assigned to them. Each added object constitutes another layer of the template. You can view the current order of layers:

The layers are sorted from lowest to highest.

By selecting any object – you can use the “Move Forward” and “Move Backward” buttons to change their position by moving up or down.

     

The position of objects (layers) is important because they can obscure each other. For example, if the top layer is a rectangle filled with a certain color – it will cover the objects below – making it impossible to select them with the mouse. Then a given object can only be selected by selecting it from the list of objects (Object -> Select – on toolbar).

Moving this rectangle to the end of the list of objects will give us the following effect:

Note: Manipulations on label objects concern one specific label  from the list of added labels, they do not cause changes in the template. For the changes to take effect – the template must be saved:

From now on, each label added based on this saved template will have the appearance and properties – just like saved in the template.

EntomoLabels 8.0 additional info

By default, the autonum fields and INC functions are not calculated when previewing a printout. Check the checkbox to preview the print exactly like the printout. In previous versions, the print preview was so small that it didn’t matter.