Inepro Back Office Suite, Technical Manual

Page: 171 / 248

163
Import Data
Extrude required data for import from CSV from data available in an Excel file
These formulas might be specific for a certain version of MS Excel and might not give the required result in everyone of
the versions. They are provided as-is and are not supported or accepted as subject by our support department.
Extruding customized gender type
This Excel statement will convert textual gender data like 'M' and 'F' into the numeric data used in our database:
=IF(<TextualGenderData>="";0;IF(<TextualGenderData>="<TextualFemaleGender>";2;IF(<TextualGenderData>="<Textu
alMaleGender>";1;0)))
For example:
=IF( A1 =""; 0 ;IF( A1 ="F"; 2 ;IF( A1 ="M"; 1 ; 0 ))) A1
is the field with the gender code. The formula
will convert an empty field to '0', an 'F' field to '2', a 'M' field to '1' and all other values to '0'.
The English 'F' and 'M' can already be used as aliases for 2 and 1, but in other languages use this formula.
A1 Extruded Value
F 2
M 1
X 0
b 0
wfg 0
unknown 0
Extruding customized (bar) code
Some bar codes, like 'CodaBar' need a letter start- and end character. If a fixed length is also necessary and the unique
numbers differ in length then it is hard to generate these kind of bar codes. A field with the fixed length of the bar code is
needed, and with this Excel formule a valid codabar bar code can be generated:
="<StartChar>"&REPEAT(TEXT("0";"0");<FixedLength>-(LENGTH(<UniqueID>)+LENGTH("<CompulsaryCode>")))
&"<CompulsaryCode>"&TEXT(<UniqueID>;"0")&"<EndChar>"
For example:
=" A "&REPEAT(TEXT("0";"0"); $C$1 -(LENGTH( B1 )+LENGTH(" 3000 ")))
&" 3000 "&TEXT( B1 ;"0")&" A ".
Where 'A' is a CodaBar required start/end character, C1 is the field with the required
fixed length of the code, '3000' a specific number / text the school wanted in their bar code and B1 is the student's ID field.
B1 C1 Extruded Value
123 12 A000003000123A
56789 12 A000300056789A
56789 9 A300056789A
56789 18 A000000000300056789A
2356 18 A000000000030002356A
Student1 18 A0000003000Student1A