Flexible CSV Importer.fh_lua--[[
@Title: Flexible CSV Importer
@Type: Standard
@Author: Mike Tate
@Contributors: Shelley Crawford & Graham Ward
@Version: 1.9.4
@Keywords:
@LastUpdated: 07 Nov 2025
@Licence: This plugin is copyright (c) 2025 Mike Tate & contributors and is licensed under the MIT License which is hereby incorporated by reference (see https://pluginstore.family-historian.co.uk/fh-plugin-licence)
@Description: Reads a CSV file usually with one line per record and imports people, facts and simple relationships. Provides an interface to map columns to fields.
]]
local strVersion = "1.9.4" -- Update this whenever Version changes
--[[Change history
v1.9.4 Support multiple P._FLGS... record flags; Workaround in FindFact(...) for Date:Compare(Date) anomaly;
v1.9.3 Support P.NAME._USED variants;
v1.9.2 Support P.NAME.TYPE and P.StandardFact.TYPE fields; Support custom facts; Allow / date separators, e.g. 10/4/1900;
v1.9.1 Check Version in Store v1.3; Fix bug that prevented custom facts from adding Sources; Add option to treat unsynced integer REFN as Record Id if record has no Custom Id;
v1.9 Sync fact AGE field; Cater for Unicode in CSV file path;
v1.8 Cater for no date in SetDate(); Cater for UTF-8 characters using stringx library and utf8data.lua; Detect ANSI CSV file and convert to UTF8; Monthly Check Version in Store;
v1.7 Make fields with any prefix work as intended; Fix problems handling Names; Sync current and new data; Update Directions Step 3; Update More Information tab; Check Version in Store;
v1.6 FH V7 Lua 3.5 IUP 3.28 compatible; progbar 3.0;
v1.5 Better load CSV syntax checks, added CSV column Source Citations, fixed multi-column text, new Help button, and more.
v1.5 Added a user option to select exisiting source or create during import.
v1.4 Fixes a problem with import of note fields.
v1.3 Fixes a problem that sometimes occurs when creating families.
v1.2 Adds option to discard custom reference numbers and fixes some bugs.
v1.1 Now appends rather than overwrites information if a citation or note field is duplicated. The separator used when appending can be selected.
Also creates an additional field instead of overwriting if an individual is attributed the same field type on more than one line
(details other than the note field will be overwritten if duplicated within the same line).
]]
require("iuplua")
require("iupluacontrols") -- for the tabs
if fhGetAppVersion() > 5 then -- Cater for Unicode UTF-8 from FH Version 6 onwards
fhSetStringEncoding("UTF-8")
iup.SetGlobal("UTF8MODE","YES")
iup.SetGlobal("UTF8MODE_FILE","YES") -- V1.9
iup.SetGlobal("CUSTOMQUITMESSAGE","YES") -- IUP 3.28 -- V1.6
end
require("luacom") -- To create File System Object -- V1.9
FSO = luacom.CreateObject("Scripting.FileSystemObject")
local strFieldNaming = [[
This plugin first assumes that column headings follow the conventions set out below.
If a valid data reference is not found, the plugin will attempt to construct one.
Simple terms like "DoB" or "Spouse's father" should be interpreted correctly.
Standard facts are imported by using their GEDCOM field tags, e.g. NAME, BIRT, MARR, DEAT, CENS.
Custom facts can be imported by entering the appropriate GEDCOM tag.
They are supplemented with detail tags, e.g. DATE, PLAC, AGE, NOTE.
Unique references (using the REFN tag) can be used to identify the same individual in several lines of data.
e.g. In birth record transcipts to identify the same father and mother across birth records for each of their children.
Most conventional column headings have the format RECORD.FIELD.DETAIL except for SPECIAL CASES shown below.
RECORD identifies the Individual or Family record:
P = Primary - the primary person of interest in the record
PF = Primary's father, PM = Primary's mother
S = Spouse - of the primary person
SF = Spouse's father, SM = Spouse's mother
Family identifiers are the two individuals separated by an underscore:
P_S = the primary person and their spouse
PF_PM = parent family of primary person
SF_SM = parent family of spouse
FIELD is the GEDCOM tag or label for the fact, e.g. REFN, BIRT, DEAT, CENS, NAME or Birth, Death, Census, Name
DETAIL is a GEDCOM tag or label for fact details, e.g. DATE, PLAC, ADDR, NOTE or When, Place, Address, Note
For example, the primary person's birth place is: P.BIRT.PLAC
Marriage date of primary person's parents is: PF_PM.Married.When
Parent and spouse relationships use PF.REFN, PM.REFN and S.REFN
SPECIAL CASES:
NAME fields are assumed to be full names, i.e. Given Names Surname,
unless followed by a name part: GIVEN, SURNAME or SURNAME_FIRST, e.g. P.NAME:GIVEN
Age at the time of a family event (e.g. Marriage) is indicated by individual's identifier, e.g. P_S.Marriage.Age:P
Separate source citation details can be added for each line.
Column headings for citation elements are of the form:
SOUR>TITL = Source Title
SOUR.DATE = Entry Date
SOUR.QUAY = Assessment ~ 1, 2, 3, 4 or P, S, Q, U
SOUR.PAGE = Where within Source
SOUR.TEXT = Text From Source
SOUR.NOTE = Citation Note
Without a prefix as above they are added to every new item.
With a RECORD prefix they add a whole record citation.
With a RECORD.FIELD prefix they add Name and Fact citations.
]]
---------------------------------
-- UPVALUE VARIABLES -- More visible than with globals needing Debug > Options > Dislay Global Types > Table
---------------------------------
local strImportFile = "" -- Import CSV filename
local strSourceTitle = "" -- Global Source Title
local arrRows = {} -- Import CSV file rows by Row number and Header Title
local tblMap = {} -- Mapping of CSV by Tags to Title, and Col number to Valid=false/Tag; Title=Header; Label=Header-Parts[1-4]; Field=Tags[0-4]; Refer=Data-Ref-Tags;
local tblPeople = {} -- Individual record pointers by CSV Row number and Header Ident (P,S,PF,PM,SF,SM)
local tblFamilies = {} -- The Family record pointers by CSV Row number and Header Ident (P_S,PF_PM,SF_SM)
local tblRefn = {} -- Custom ID refs to Individual record pointers
local tblLists = {} -- Lookup tables by Record; Field2; Field3; Source; Names;
local dicSources = {} -- Source Title exists by Tags during Field Interpretaion; Source record pointers by Title during Import Data
local tblRel = {} -- Relationships by Header Ident (P,S,PF,PM,SF,SM;P_S,PF_PM,SF_SM); SOUR; EXCL;
local tblSet = {} -- IUP GUI settings
local dicBox = {} -- IUP GUI gridbox cells
local strRed = "255 0 0" -- IUP GUI colours
local strGreen = "0 128 0"
local strBlue = "0 0 255"
local strNavy = "0 0 128"
local strBlack = "0 0 0"
local strWhite = "255 255 255"
---------------------------------
-- GLOBAL FUNCTIONS
---------------------------------
--[[
@Module: +fh+stringx_v3
@Author: Mike Tate
@Version: 3.0
@LastUpdated: 19 Sep 2020
@Description: Extended string functions to supplement LUA string library.
@V3.0: Function Prototype Closure version with Lua 5.1 & 5.3 comaptibility; Added inert(strTxt) function;
@V2.5: Support FH V6 Encoding = UTF-8;
@V2.4: Tolerant of integer & nil parameters just link match & gsub;
@V1.0: Initial version.
]]
local function stringx_v3()
local fh = {} -- Local environment table
-- Supply current file encoding format --
function fh.encoding()
if fhGetAppVersion() > 5 then return fhGetStringEncoding() end
return "ANSI"
end -- function encoding
-- Split a string using "," or chosen separator --
function fh.split(strTxt,strSep)
local tblFields = {}
local strPattern = string.format("([^%s]+)", strSep or ",")
strTxt = tostring(strTxt or "")
strTxt:gsub(strPattern, function(strField) tblFields[#tblFields+1] = strField end)
return tblFields
end -- function split
-- Split a string into numbers using " " or "," or "x" separators -- Any non-number remains as a string
function fh.splitnumbers(strTxt)
local tblNum = {}
strTxt = tostring(strTxt or "")
strTxt:gsub("([^ ,x]+)", function(strNum) tblNum[#tblNum+1] = tonumber(strNum) or strNum end)
return tblNum
end -- function splitnumbers
local strMagic = "([%^%$%(%)%%%.%[%]%*%+%-%?])" -- UTF-8 replacement for "(%W)"
-- Hide magic pattern symbols ^ $ ( ) % . [ ] * + - ?
function fh.plain(strTxt)
-- Prefix every magic pattern character with a % escape character,
-- where %% is the % escape, and %1 is the original character capture.
strTxt = tostring(strTxt or ""):gsub(strMagic,"%%%1")
return strTxt
end -- function plain
-- matches is plain text version of string.match()
function fh.matches(strTxt,strFind,intInit)
strFind = tostring(strFind or ""):gsub(strMagic,"%%%1") -- Hide magic pattern symbols
return tostring(strTxt or ""):match(strFind,tonumber(intInit))
end -- function matches
-- replace is plain text version of string.gsub()
function fh.replace(strTxt,strOld,strNew,intNum)
strOld = tostring(strOld or ""):gsub(strMagic,"%%%1") -- Hide magic pattern symbols
return tostring(strTxt or ""):gsub(strOld,function() return strNew end,tonumber(intNum)) -- Hide % capture symbols
end -- function replace
-- Hide % escape/capture symbols in replacement so they are inert
function fh.inert(strTxt)
strTxt = tostring(strTxt or ""):gsub("%%","%%%%") -- Hide all % symbols
return strTxt
end -- function inert
-- convert is pattern without captures version of string.gsub()
function fh.convert(strTxt,strOld,strNew,intNum)
return tostring(strTxt or ""):gsub(tostring(strOld or ""),function() return strNew end,tonumber(intNum)) -- Hide % capture symbols
end -- function convert
local dicUpper = { }
local dicLower = { }
local dicCaseX = { }
-- ASCII unaccented letter translations for Upper, Lower, and Case Insensitive
for intUpper = string.byte("A"), string.byte("Z") do
local strUpper = string.char(intUpper)
local strLower = string.char(intUpper - string.byte("A") + string.byte("a"))
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
local strCaseX = "["..strUpper..strLower.."]"
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
-- Supply character length of ANSI text --
function fh.length(strTxt)
return string.len(strTxt or "")
end -- function length
-- Supply character substring of ANSI text --
function fh.substring(strTxt,i,j)
return string.sub(strTxt or "",i,j)
end -- function substring
-- Translate upper/lower case ANSI letters to pattern that matches both --
function fh.caseless(strTxt)
strTxt = tostring(strTxt or ""):gsub("[A-Za-z]",dicCaseX)
return strTxt
end -- function caseless
if fh.encoding() == "UTF-8" then
-- Supply character length of UTF-8 text --
function fh.length(strTxt)
isFlag = fhIsConversionLossFlagSet()
strTxt = fhConvertUTF8toANSI(strTxt or "")
fhSetConversionLossFlag(isFlag)
return string.len(strTxt)
end -- function length
local strUTF8 = "([%z\1-\127\194-\244][\128-\191]*)" -- Cater for Lua 5.1 %z or Lua 5.3 \0
if fhGetAppVersion() > 6 then
strUTF8 = "([\0-\127\194-\244][\128-\191]*)"
end
-- Supply character substring of UTF-8 text --
function fh.substring(strTxt,i,j)
local strSub = ""
j = j or -1
if j < 0 then j = j + length(strTxt) + 1 end
if i < 0 then i = i + length(strTxt) + 1 end
for strChr in string.gmatch(strTxt or "",strUTF8) do
if j <= 0 then break end
j = j - 1
i = i - 1
if i <= 0 then strSub = strSub..strChr end
end
return strSub
end -- function substring
pcall(require,"utf8data") -- Cater for utf8data.lua optional mapping --
-- Translate lower case to upper case UTF-8 letters -- -- Cater for utf8data.lua optional mapping --
function fh.upper(strTxt)
strTxt = tostring(strTxt or ""):gsub("([a-z\194-\244][\128-\191]*)",(utf8_lc_uc or dicUpper))
return strTxt
end -- function upper
-- Translate upper case to lower case UTF-8 letters -- -- Cater for utf8data.lua optional mapping --
function fh.lower(strTxt)
strTxt = tostring(strTxt or ""):gsub("([A-Z\194-\244][\128-\191]*)",(utf8_uc_lc or dicLower))
return strTxt
end -- function lower
-- Translate upper/lower case UTF-8 letters to pattern that matches both --
function fh.caseless(strTxt)
strTxt = tostring(strTxt or ""):gsub("([A-Za-z\194-\244][\128-\191]*)",dicCaseX)
return strTxt
end -- function caseless
-- Following tables use ASCII numeric coding to be immune from ANSI/UTF-8 encoding --
local arrPairs = -- Upper & Lower case groups of UTF-8 letters with same prefix --
{-- { Prefix; Beg ; End ; Inc; Offset Upper > Lower }; -- These include all ANSI letters and many more
{ "\195"; 0x80; 0x96; 1 ; 32 }; -- 195=0xC3 À U+00C0 to Ö U+00D6 and à U+00E0 to ö U+00F6
{ "\195"; 0x98; 0x9E; 1 ; 32 }; -- 195=0xC3 Ø U+00D8 to Þ U+00DE and ø U+00F8 to þ U+00FE
{ "\196"; 0x80; 0xB6; 2 ; 1 }; -- 196=0xC4 A U+0100 to k U+0137 in pairs
{ "\196"; 0xB9; 0xBD; 2 ; 1 }; -- 196=0xC4 L U+0139 to l U+013E in pairs
{ "\197"; 0x81; 0x87; 2 ; 1 }; -- 197=0xC5 L U+0141 to n U+0148 in pairs
{ "\197"; 0x8A; 0xB6; 2 ; 1 }; -- 197=0xC5 ? U+014A to y U+0177 in pairs
{ "\197"; 0xB9; 0xBD; 2 ; 1 }; -- 197=0xC5 Z U+0179 to ž U+017E in pairs
{ "\198"; 0x82; 0x84; 2 ; 1 }; -- 198=0xC6 ? U+0182 to ? U+0185 in pairs
-- Add more Unicode groups here as usage increases --
}
local dicPairs = -- Upper v Lower case UTF-8 letters that don't fit groups above --
{ [string.char(0xC4,0xBF)] = string.char(0xC5,0x80); -- ? U+013F and ? U+0140
[string.char(0xC5,0xB8)] = string.char(0xC3,0xBF); -- Ÿ U+0178 and ÿ U+00FF
}
local intBeg1 = string.byte(string.sub("À",1))
local intBeg2 = string.byte(string.sub("À",2))
local intEnd1 = string.byte(string.sub("Z",1))
local intEnd2 = string.byte(string.sub("Z",2))
-- print(string.format("%#x %#x %#x %#x",intBeg1,intBeg2,intEnd1,intEnd2)) -- Useful to work out numeric coding
-- Populate the UTF-8 letter translation dictionaries --
for intGroup, tblGroup in ipairs ( arrPairs ) do -- UTF-8 accented letter groups
local strPrefix = tblGroup[1]
for intUpper = tblGroup[2], tblGroup[3], tblGroup[4] do
local strUpper = string.char(intUpper)
local strLower = string.char(intUpper + tblGroup[5])
local strCaseX = strPrefix.."["..strUpper..strLower.."]"
strUpper = strPrefix..strUpper
strLower = strPrefix..strLower
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
end
for strUpper, strLower in pairs ( dicPairs ) do -- UTF-8 accented letters where upper & lower have different prefix
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
local strCaseX = ""
for intByte = 1, #strUpper do -- Matches more than just the two letters, but can't do any better
strCaseX = strCaseX.."["..strUpper:sub(intByte,intByte)..strLower:sub(intByte,intByte).."]"
end
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
end -- fh.encoding() == "UTF-8"
-- overload fh functions into string table
for strIndex, anyValue in pairs(fh) do
if type(anyValue) == "function" then
string[strIndex] = anyValue
end
end
return fh
end -- local function stringx_v3
local stringx = stringx_v3() -- To access FH string extension module
-- Convert filename to ANSI alternative and indicate success -- -- V1.9
function FileNameToANSI(strFileName,strAnsiName)
-- strFileName ~ full file path
-- strAnsiFile ~ ANSI file name & type
-- return values ~ ANSI file path, true if original path was ANSI compatible
if stringx.encoding() == "ANSI" then return strFileName, true end
local isFlag = fhIsConversionLossFlagSet()
fhSetConversionLossFlag(false)
local strAnsi = fhConvertUTF8toANSI(strFileName)
local wasAnsi = true
if fhIsConversionLossFlagSet() then
strAnsiName = strAnsiName or "ANSI.ANSI"
strAnsi = fhGetContextInfo("CI_APP_DATA_FOLDER").."\\Plugin Data\\"..strAnsiName
wasAnsi = false
end
fhSetConversionLossFlag(isFlag)
return strAnsi, wasAnsi
end -- local function FileNameToANSI
--[[GetExistingFile
@name GetExistingFile
@param strTitle Prompt Title
@param strFilter Filter template e.g *.CSV;*.TXT
@param strFilterInfo Display Text for the Selection
@param strDir Directory folder
]]
function GetExistingFile(strTitle,strFilter,strFilterInfo,strDir)
-- Creates a file dialog and sets its type, title, filter and filter info
local filedlg = iup.filedlg{ dialogtype = "OPEN"; title = strTitle; filter = strFilter; filterinfo = strFilterInfo; directory = strDir; }
-- Shows file dialog in the center of the screen
filedlg:popup(iup.ANYWHERE,iup.ANYWHERE)
-- Gets file dialog status
local status = filedlg.status
return status,filedlg.value
end -- function GetExistingFile
--[[table.loadcsv
@name table.loadcsv
@description Loads a CSV file into a table of tables, the first line should contain descriptions
@usage provide valid filename, use ipairs loop to use resulting table
@param filename filename to load
@param bHasHeader true if the file contains a header line, false if not, defaults to false
If true
@return table of tables, if a header is used, indexed by the fieldnames from the header
@return table containing the header field names (if used)
]]
function table.loadcsv(filename,bHasHeader)
bHasHeader = bHasHeader or false
local bHeader = false
local header = {}
local contents = {}
local function report(what,where) -- MBT
local row = #contents + 1
if where then where = "\nnear: "..where else where = "" end
fhMessageBox("\nCSV Data File Syntax Error\n\n"..what.." on row "..row..where.."\n","MB_OK","MB_ICONEXCLAMATION")
end
local function fromCSV (s)
s = s .. ',' -- ending comma
local t = {} -- table to collect fields
local fieldstart = 1
repeat
-- next field is quoted? (start with '"'?)
if string.find(s, '^"', fieldstart) then
local a, c
local i = fieldstart
repeat
-- find closing quote
a, i, c = string.find(s, '"("?)', i+1)
until c ~= '"' -- quote not followed by quote?
if not i then
report('Unmatched trailing " ',string.sub(s, fieldstart-3,#s-1))
i = #s
end
local f = string.sub(s, fieldstart+1, i-1)
table.insert(t, (string.gsub(f, '""', '"')))
fieldstart = string.find(s, ',', i) + 1
local x = string.sub(s, i+1, fieldstart-3)
if string.find(x, "[^%s]") then
report('Unmatched opening " ',string.sub(s, i-20, fieldstart))
end
else -- unquoted; find next comma
local nexti = string.find(s, ',', fieldstart)
local x = string.sub(s, fieldstart, nexti-1)
if string.find(x, '"%s?$') then
report('Unmatched closing " ',string.sub(s, fieldstart-2, nexti))
end
table.insert(t, string.sub(s, fieldstart, nexti-1))
fieldstart = nexti + 1
end
until fieldstart > string.len(s)
return t
end
local first
for line in io.lines(filename) do
if line:match("[\128-\255]") and #line == string.length(line) -- Detect ANSI encoded file and convert to UTF8 -- V1.8
and fhGetAppVersion() > 5 then
line = fhConvertANSItoUTF8(line)
end
local fields = fromCSV(line)
local data = {}
if bHasHeader then
if bHeader then
-- Build Data Table with the Header Descriptions
for i,field in ipairs(fields) do
if field ~= '' then
local head = header[i] or " " -- V1.7
if data[head] then head = head.." " end -- avoid duplicate header
data[head] = field
end
end
if #fields ~= #header then
report(#fields.." data cols differ from "..#header.." header cols")
end
table.insert(contents,data)
else
-- Grab Column Names
header = fields
bHeader = true
end
else
first = first or #fields
if #fields ~= first then
report("First row "..first.." cols differ from "..#fields.." cols")
end
table.insert(contents,fields)
end
end
return contents,header
end -- function table.loadcsv
--[[SplitTitle
@name SplitTitle
@description Splits one column title into a table of the labels in use
and their interpretation as Family Historian field tags.
@param strTitle the column title
@return tblMap table of title, labels and fields
]]
function SplitTitle(strTitle) -- MBT -- Rewritten
local arrLabel = strTitle:upper():split(".:>")
local arrField = {}
for i = 1, 5 do
if arrLabel[i] then arrField[i] = arrLabel[i] end
if i <= 3 then arrLabel[i] = arrLabel[i] or "" end
end
arrField[0] = arrLabel[1]
local tblMap = { Valid=false; Title=strTitle; Label=arrLabel; Field=arrField; Refer="";}
return tblMap
end -- function SplitTitle
--[[IsSource
@name IsSource
@description check if field is valid source citation label
@param strField the field
@return true/false
]]
function IsSource(strField) -- MBT
return strField and ( strField:match("SOURC?E?") or strField:match("CIT[EA]") )
end -- function IsSource
--[[TestFields
@name TestFields
@description Inspects the field interpretations and checks if they produce valid Family Historian data reference.
Makes some corrections to fields where possible. Adds a true/false flag for the field interpretation validity.
@param intCol The column number of the titles table
]]
function TestFields(intCol) -- MBT -- Largely rewritten
-- look for INDI and FAM fact types
local arrLabel = tblMap[intCol].Label
local arrField = tblMap[intCol].Field
local strRefer = ""
local dicCodes = { TITLE="TITL"; TITL="TITL"; QUAY="QUAY"; DATE="DATA~DATE"; PAGE="PAGE"; TEXT="DATA~TEXT"; NOTE="NOTE2"; NOTE2="NOTE2"; }
local function ValidateSource(strType,intField)
local isValid = false
local strField = arrField[intField]
local strCodes = dicCodes[strField]
if strCodes then
if strCodes == "TITL" then dicSources[strType] = true end -- Ensure TITL precedes other Citation columns
if dicSources[strType] then isValid = "SOUR~"..strCodes end
strField = strCodes:match("[^~]+$")
end
return isValid, strField
end -- local function ValidateSource
local isValid = false
if arrField[1] then
local strTitle = tblMap[intCol].Title
if strTitle:match("^REF") or strTitle:match("^ID") then -- V1.7
arrLabel = { "P" ; "REFN"; }
arrField = { "INDI"; "REFN"; [0]="P"; }
end
local dicRel = tblRel[arrField[0]]
if dicRel then
if dicRel.RecTag == "INDI" and ( arrField[2] == "SEX" or arrField[2] == "REFN" or arrField[2] == "NOTE2" or arrField[2] == "_FLGS" ) then -- V1.9.4
-- "misc" indi fields
arrField[1] = "INDI"
if arrField[2] ~= "_FLGS" then arrField[3] = nil end -- V1.9.4
arrField[4] = nil
strRefer = table.concat(arrField,".")
isValid = "INDI" -- true
elseif dicRel.RecTag ~= "SOUR" then
-- anything other than citations
arrField[1] = dicRel.RecTag
if not arrField[2] then
arrLabel[3] = nil
arrField[3] = nil
isValid = false
elseif IsSource(arrField[2]) then
arrField[2] = "SOUR"
isValid, arrField[3] = ValidateSource(arrField[1],3)
strRefer = table.concat(arrField,".")
else
-- fix easy typo
local dicSubfield = { PLACE="PLAC"; NOTE="NOTE2"; }
arrField[3] = dicSubfield[arrField[3]] or arrField[3]
strRefer = table.concat(arrField,".")
strRefer = strRefer:gsub("%.NAME%.?",".NAME:"):gsub(":SOUR",".SOUR"):gsub(":NOTE2",".NOTE2"):gsub(":TYPE",".TYPE"):gsub("[:%.]_?USED","._USED") -- V1.9.2 -- V1.9.3
strRefer = strRefer:gsub("%.SOUR%.TITL",".SOUR>TITL")
strRefer = strRefer:gsub("%.SOUR%.DATE$",".SOUR.DATA.DATE"):gsub("%.SOUR%.TEXT$",".SOUR.DATA.TEXT")
strRefer = strRefer:gsub("%.AGE%.%w+",".AGE")
if fhIsValidDataRef("%"..strRefer.."%") then -- checks it's valid
-- valid field
isValid = arrField[1]
if IsSource(arrField[3]) then
arrField[3] = "SOUR"
isValid, arrField[4] = ValidateSource(arrField[1]..arrField[2],4)
elseif arrField[1] == "FAM" and arrField[3] == "AGE" then
if not tblRel[arrField[4]] or tblRel[arrField[4]].RecTag ~= "INDI" then -- need a reference in arrField[4] to which individual's age
isValid = false
end
elseif arrField[3] == "USED" then -- V1.9.3
arrField[3] = "_USED"
end
local strFactType = ListItemLookup(tblLists.Field2,"FactType",arrField[2])
if strFactType == "Event" and not arrField[3] then isValid = false end
else
if arrField[1] == "INDI" then
-- intended FAM field assigned to an INDI so change to FAM
strRefer = strRefer:gsub("^INDI","FAM")
if fhIsValidDataRef("%"..strRefer.."%") then -- checks it's valid
arrField[1] = "FAM"
if arrField[3] == "AGE" then arrField[4] = arrField[0] end
arrField[0] = dicRel.Fams -- selects the correct family record type for that individual from tblRel
isValid = "FAM"
end
end
end
end
end
end
if IsSource(arrField[1]) then
isValid, arrField[2] = ValidateSource("SOUR",2)
arrField[0] = "SOUR"
arrField[1] = "SOUR"
arrField[3] = nil
arrField[4] = nil
strRefer = table.concat(arrField,".")
end
end
if isValid then
if strRefer:match("^INDI%.NAME:") or strRefer:match("^INDI%.REFN") then
local strRef = strRefer:gsub("^INDI",arrField[0])
if tblMap[strRef] then
isValid = false
else
local strTitle = tblMap[intCol].Title
tblMap[strRef] = strTitle
if strRef:match(arrField[0]..".NAME:%u+") then -- Ensure NAME: mapping is removed when Field3 is defined -- V1.7
tblMap[arrField[0]..".NAME:"] = nil
end
end
elseif arrField[2] ~= "SOUR" and arrField[2] ~= "_FLGS" and arrField[3] and arrField[3] ~= "ADDR" and arrField[3] ~= "NOTE2" and arrField[3] ~= "SOUR" then -- V1.9.4
local strFld = arrField[2]
local strRef = arrField[0].."."..arrField[2]
tblMap[strRef] = tblMap[strRef] or {} -- Mapping for synchronising facts in FindFact() -- V1.7
table.insert(tblMap[strRef],intCol)
if #strFld > 4 then -- Handle any _ATTR- or EVEN- custom facts -- V1.9.2
if not tblLists[strFld] then
local strType = "Event"
if strFld:match("^_ATTR") then strType = "Attribute" end
table.insert(tblLists.Field2,{ Label = strFld; Tag = strFld; FactType = strType; RecTag = arrField[1]; })
tblLists[strFld] = true
end
if arrField[3] == "TYPE" then -- TYPE invalid for _ATTR- and EVEN- custom facts -- V1.9.2
isValid = false
end
end
end
end
tblMap[intCol].Valid = isValid
tblMap[intCol].Label = arrLabel
tblMap[intCol].Field = arrField
tblMap[intCol].Refer = strRefer
end -- function TestFields
--[[MarkValid
@name MarkValid
@description Colour codes column titles in the GUI according to field validity
@param intCol The column number of the titles table to mark
]]
function MarkValid(intCol)
-- colours the titles in the gridbox red or green, depending on if the field is valid
local strColor = strRed
if tblMap[intCol].Valid then strColor = strGreen end
dicBox[tblMap[intCol].Title].Header.FgColor = strColor
end -- function MarkValid
--[[TitlesSecondChance
@name TitlesSecondChance
@description Automagically interprets fields based on words found
@param intCol The title index column number
]]
function TitlesSecondChance(intCol) -- MBT -- rewritten
local arrLabel = tblMap[intCol].Label
local arrField = tblMap[intCol].Field
local strTitle = tblMap[intCol].Title:upper()
local wasFound = false
local arrSource = {
{ Label="TITLE"; Tag="TITL"; };
{ Label="ASSESS"; Tag="QUAY"; };
{ Label="QUAL"; Tag="QUAY"; };
{ Label="DATE"; Tag="DATE"; };
{ Label="ENTRY"; Tag="DATE"; };
{ Label="YEAR"; Tag="DATE"; };
{ Label="WHEN"; Tag="DATE"; };
{ Label="WHERE"; Tag="PAGE"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
{ Label="TRANS"; Tag="TEXT"; };
{ Label="TEXT"; Tag="TEXT"; };
}
local function doFind(arrFind,intData,strLook) -- search data array for matching lookup text
local isFound = false
strLook = strLook or arrLabel[intData] or arrField[intData] or ""
strLook = strLook:upper()
for _, dicFind in ipairs ( arrFind ) do
for _, strFind in ipairs ( { "Label"; "Tag"; "Verb"; } ) do
if dicFind[strFind] then
if strLook:find( dicFind[strFind]:upper() ) then
arrField[intData] = dicFind.Tag or dicFind.Ident
if dicFind.RecTag then
arrField[1] = dicFind.RecTag
end
isFound = true
break
end
end
end
if isFound then break end
end
return isFound
end -- local function doFind
-- Record
if doFind( { { Label="CIT[AE]"; }; { Label="SOUR"; }; }, 0 ) then
arrField[0] = "SOUR"
arrField[1] = "SOUR"
doFind( arrSource, 2 )
else
if not arrField[0] or not tblRel[arrField[0]] then
local arrPrimary = { { Label="FATHER"; Ident="PF"; }; { Label="MOTHER"; Ident="PM"; }; }
local arrSpouse = { { Label="FATHER"; Ident="SF"; }; { Label="MOTHER"; Ident="SM"; }; }
arrField[0] = "P"
arrField[1] = "INDI"
doFind( arrPrimary, 0, strTitle )
if doFind( { { Label="GROOM"; Ident="P"; }; }, 0, strTitle ) then
tblRel.P.Sex = "M" tblRel.S.Sex = "F"
doFind( arrPrimary, 0, strTitle )
elseif doFind( { { Label="BRIDE"; Ident="S"; }; }, 0, strTitle ) then
tblRel.S.Sex = "F" tblRel.P.Sex = "M"
doFind( arrSpouse, 0, strTitle )
elseif doFind( { { Label="SPOUSE"; Ident="S"; }; }, 0, strTitle ) then
doFind( arrSpouse, 0, strTitle )
end
end
-- Name
local arrNames = {
{ Label="SURNAME"; Tag="NAME"; };
{ Label="FAMILY"; Tag="NAME"; };
{ Label="GIVEN"; Tag="NAME"; };
{ Label="FORE"; Tag="NAME"; };
{ Label="_USED"; Tag="NAME"; }; -- V1.9.3
{ Label="NAME"; Tag="NAME"; };
}
if doFind( arrNames, 2, strTitle ) then
local arrNamed = {
{ Label="%u+.FIRST";Tag="SURNAME_FIRST"; };
{ Label="SURNAME"; Tag="SURNAME"; };
{ Label="FAMILY"; Tag="SURNAME"; };
{ Label="GIVEN"; Tag="GIVEN"; };
{ Label="FIRST"; Tag="GIVEN"; };
{ Label="FORE"; Tag="GIVEN"; };
{ Label="_USED"; Tag="_USED"; }; -- V1.9.3
{ Label="CIT[AE]"; Tag="SOUR"; };
{ Label="SOUR"; Tag="SOUR"; };
{ Label="COMM"; Tag="NOTE2"; };
{ Label="NOTE"; Tag="NOTE2"; };
}
if doFind( arrNamed, 3 ) then
wasFound = true
end
end
-- Field2
if not wasFound then
if not doFind( tblLists.Field2, 2 ) then
local arrOther = { -- try special terms
{ Label="DOB"; Tag="BIRT"; };
{ Label="POB"; Tag="BIRT"; };
{ Label="DOD"; Tag="DEAT"; };
{ Label="POD"; Tag="DEAT"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
}
if doFind( arrOther, 2 ) then
if arrLabel[2]:match("DO[BD]") then arrField[3] = "DATE" end
if arrLabel[2]:match("PO[BD]") then arrField[3] = "PLAC" end
wasFound = true
end
end
end
-- Field3
if not wasFound then
if IsSource(arrField[2]) then
arrField[2] = "SOUR"
doFind( arrSource, 3 )
else
local arrField3 = {
{ Label="PLACE"; Tag="PLAC"; };
{ Label="WHERE"; Tag="PLAC"; };
{ Label="DATE"; Tag="DATE"; };
{ Label="WHEN"; Tag="DATE"; };
{ Label="YEAR"; Tag="DATE"; };
{ Label="ADDR"; Tag="ADDR"; };
{ Label="CAUSE"; Tag="CAUS"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
{ Label="TYPE"; Tag="TYPE"; }; -- V1.9.2
{ Label="AGE"; Tag="AGE"; };
}
doFind( arrField3, 3 )
if arrField[3] == "AGE" then
doFind( { { Label="PRIM"; Ident="P"; }; { Label="SPOU"; Ident="S"; }; }, 4 )
end
end
end
if IsSource(arrField[3]) then
arrField[3] = "SOUR"
doFind( arrSource, 4 )
end
end
end -- function TitlesSecondChance
--[[CreatePeople
@name CreatePeople
@description Creates people for one line of the CSV file, and adds them to tblPeople
Increments the person count intPeople for each person created
@param intRow The csv line number
@param intPeople Count of Individual records
@return intPeople
]]
function CreatePeople(intRow,intPeople)
tblPeople[intRow] = tblPeople[intRow] or {}
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid == "INDI" and dicMap.Field[2] == "REFN" then
local strValue = arrRows[intRow][dicMap.Title]
if strValue then
local ptrInd = fhNewItemPtr()
if tblRefn[strValue] then -- reference number logged, so use it
ptrInd:MoveTo(tblRefn[strValue])
else
ptrInd:MoveToRecordById("INDI",tonumber(strValue) or 0) -- find person with matching Record Id -- V1.9.1
if tblSet.UseRecId == "Yes"
and ptrInd:IsNotNull()
and fhGetItemPtr(ptrInd,"~.REFN"):IsNull() then -- unsynced integer REFN so use that person -- V1.9.1
-- Use existing person with matching Rec Id
else
ptrInd = fhCreateItem("INDI") -- ...or create then log
intPeople = intPeople + 1
end
tblRefn[strValue] = ptrInd:Clone()
if tblSet.RetainID == "Yes" then
fhSetValueAsText(fhCreateItem("REFN",ptrInd),strValue)
end
end
local strIdent = dicMap.Field[0]
if strIdent == "P" then AddSources(ptrInd,intRow,"INDI") end
tblPeople[intRow][strIdent] = ptrInd:Clone() -- log in the person table
end
end
end
return intPeople
end -- function CreatePeople
--[[CreateFamilies
@name CreateFamilies
@description creates family records that link the individuals together. Adds the family pointer to tblFamilies.
@usage best run after individuals have had genders assigned
@param intRow Line number being used
@param intFamilies Count of Family records
@return intFamilies
]]
function CreateFamilies(intRow,intFamilies)
local ptrFam = fhNewItemPtr()
local dicRow = arrRows[intRow]
local dicPerson = tblPeople[intRow] or {}
tblFamilies[intRow] = tblFamilies[intRow] or {}
for strIdent, tblFam in pairs (tblRel) do -- reference table
if tblFam.RecTag == "FAM" then -- family definitions
local ptrSpouse1 = dicPerson[tblFam.Spouse1]
local ptrSpouse2 = dicPerson[tblFam.Spouse2]
local ptrChild = dicPerson[tblFam.Child]
local bSetSpouses = false -- test if there's already a family for the couple
if (ptrSpouse1 and ptrSpouse2) or ((ptrSpouse1 or ptrSpouse2) and ptrChild) then -- need to make a family
local strSpouse1 = dicRow[tblMap[tblFam.Spouse1..".REFN"]]
local strSpouse2 = dicRow[tblMap[tblFam.Spouse2..".REFN"]]
if tblRefn[strSpouse1] and tblRefn[strSpouse2] then -- both spouses have custom reference numbers
if tblRefn[strSpouse1.."&"..strSpouse2] then
ptrFam = tblRefn[strSpouse1.."&"..strSpouse2]
else
ptrFam = fhCreateItem("FAM") -- now create the family record
intFamilies = intFamilies +1
tblRefn[strSpouse1.."&"..strSpouse2] = ptrFam:Clone()
tblRefn[strSpouse2.."&"..strSpouse1] = ptrFam:Clone()
bSetSpouses = true
AddSources(ptrFam,intRow,"FAM")
end
else
ptrFam = fhCreateItem("FAM") -- now create the family record
intFamilies = intFamilies +1
bSetSpouses = true
AddSources(ptrFam,intRow,"FAM")
end
if bSetSpouses == true then -- create spouse links if family doesn't already have them
local strItemS1 = "HUSB" -- default if no other info is spouse1 male
local strItemS2 = "WIFE"
if ptrSpouse1 then -- determine sex for spouse1 and link -- V1.7
if fhGetItemText(ptrSpouse1,'~.SEX') ~= "Male" then
if fhGetItemText(ptrSpouse1,'~.SEX') == "Female" then
strItemS1 = "WIFE"
elseif ptrSpouse2 then -- sex of spouse 1 unknown, check other spouse
if fhGetItemText(ptrSpouse2,'~.SEX') == "Male" then
strItemS1 = "WIFE"
end
end
end
local ptrLink = fhCreateItem(strItemS1, ptrFam )
fhSetValueAsLink(ptrLink, ptrSpouse1)
end
if ptrSpouse2 then -- determine sex for spouse2 and link -- V1.7
if fhGetItemText(ptrSpouse2,'~.SEX') ~= "Female" then
if fhGetItemText(ptrSpouse2,'~.SEX') == "Male" then
strItemS2 = "HUSB"
elseif ptrSpouse1 then -- sex of spouse 1 unknown, check other spouse
if fhGetItemText(ptrSpouse1,'~.SEX') == "Female" then
strItemS2 = "HUSB"
end
end
end
local ptrLink = fhCreateItem(strItemS2, ptrFam )
fhSetValueAsLink(ptrLink, ptrSpouse2)
end
end
if ptrChild then -- create a CHILD field in the family record
local ptrLink = fhCreateItem("CHIL", ptrFam )
fhSetValueAsLink(ptrLink, ptrChild)
end
tblFamilies[intRow][strIdent] = ptrFam:Clone() -- add to tblFamilies
end -- if need to make a family
end
end
return intFamilies
end -- function CreateFamilies
--[[AddItems
@name AddItems
@description Adds facts to the identifier type given
@param intRow The line number being processed
@param strType "INDI" or "FAM"
@param tblType input table of pointers
]]
function AddItems(intRow, strType, tblType) -- e.g. AddItems(intRow, "INDI", tblPeople) or AddItems(intRow, "FAM", tblFamilies)
local tblFld = {}
local dicRow = arrRows[intRow] -- get the row of data values
local tblType = tblType[intRow] -- get all the record pointers
for intMap, dicMap in ipairs (tblMap) do
local strTitle = dicMap.Title
local strRefer = dicMap.Refer
local arrField = dicMap.Field
local strField0 = arrField[0]
local strField2 = arrField[2]
local strField3 = arrField[3]
if dicMap.Valid == strType then -- Valid entry for INDI/FAM
local ptrRec = tblType[strField0] -- get the person/family record pointer
local strValue = dicRow[strTitle] -- get the column data value
if ptrRec and strValue and
not ( strField2 == "REFN" and strType == "INDI" ) then -- custom id handled in CreatePeople above
local strFld = strField0.."."..strField2 -- V1.7
if strField2 == "NAME" then -- names get special treatment here
local ptrFld = tblFld[strFld]
if not ptrFld then -- V1.7
local strFullName = FullName(dicRow,strField0)
ptrFld = NameExists(ptrRec,strFullName) -- V1.7
if not ptrFld then
ptrFld = fhCreateItem("NAME",ptrRec)
tblFld[strFld] = ptrFld:Clone() -- V1.7
fhSetValueAsText(ptrFld,strFullName)
AddSources(ptrFld,intRow,strRefer)
end
end
if strField3 == "NOTE2" then -- can append multiple columns to note
local ptrSub = fhGetItemPtr(ptrFld,"~.NOTE2")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("NOTE2",ptrFld)
end
AppendText(ptrSub,strValue)
elseif strField3 == "TYPE" then -- Add Name Type -- V1.9.2
local ptrSub = fhGetItemPtr(ptrFld,"~.TYPE")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("TYPE",ptrFld)
end
fhSetValueAsText(ptrSub,strValue)
elseif strField3 == "_USED" then -- Add Name Used -- V1.9.3
local ptrSub = fhGetItemPtr(ptrFld,"~._USED")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("_USED",ptrFld)
end
fhSetValueAsText(ptrSub,strValue)
end
else
local ptrFld = tblFld[strFld] -- field already exists? -- V1.7
ptrFld = FindFact(ptrRec,strFld,ptrFld,strField2,dicRow)
if ptrFld then
tblFld[strFld] = ptrFld:Clone() -- V1.7
AddSources(ptrFld,intRow,strRefer)
else
ptrFld = fhCreateItem(strField2,ptrRec) -- create new entry
if ptrFld:IsNotNull() then
tblFld[strFld] = ptrFld:Clone() -- V1.7
if strField2 ~= "SEX" then AddSources(ptrFld,intRow,strRefer) end
end
end
if strField3 then
if strType == "FAM" and strField3 == "AGE" then -- handle ages at family event separately
if ptrFld:IsNull() then
ptrFld = fhCreateItem(strField2,ptrRec)
AddSources(ptrFld,intRow,strRefer)
end
local ptrInd = tblPeople[intRow][arrField[4]] -- get spouse individual record pointer
if ptrInd then
local strTag = "WIFE"
if fhGetItemText(ptrInd,"~.SEX") == "Male" then strTag = "HUSB" end
local ptrSpou = fhCreateItem(strTag,ptrFld) -- makes the HUSB or WIFE bit
local ptrLink = fhCreateItem("AGE",ptrSpou,true)
fhSetValueAsText(ptrLink,strValue)
end
else -- other subfields
local ptrSub = fhGetItemPtr(ptrFld,"~."..strField3)
if ptrSub:IsNull() then
ptrSub = fhCreateItem(strField3,ptrFld)
end
if strField3 == "DATE" then -- set date
SetDate(strValue,ptrSub)
elseif strField3 == "AGE" then -- set age
fhSetValueAsText(ptrSub,strValue)
else
AppendText(ptrSub,strValue) -- can append multiple columns to note, etc
end
end
else -- no subfield
AppendText(ptrFld,strValue) -- can append multiple columns to note, attribute, etc
end
end
end
end
end
if strType == "INDI" then
for strRec, ptrRec in pairs (tblType) do -- set SEX if not already specified
local ptrSex = fhGetItemPtr(ptrRec,"~.SEX")
local strSex = tblRel[strRec].Sex
if ptrSex:IsNull() and strSex ~= "" then
ptrSex = fhCreateItem("SEX",ptrRec)
fhSetValueAsText(ptrSex,strSex)
end
end
end
end -- function AddItems
--[[FindFact
@name FindFact
@description Finds a fact whose fields sync with CSV data
@param ptrRec pointer to current record
@param strRef reference to current fact
@param ptrFld pointer to current fact
@param strFld the tag of current fact
@param dicRow the line contents
]]
function FindFact(ptrRec,strRef,ptrFld,strFld,dicRow) -- Find fact whose fields sync with data -- V1.7
if not ptrFld then
ptrFld = fhGetItemPtr(ptrRec,"~."..strFld) -- First instance of existing fact type
end
while ptrFld:IsNotNull() do -- Check each instance of fact type
local isFound = true
for _,intMap in ipairs ( tblMap[strRef] or {} ) do -- Check each mapped data field
local dicMap = tblMap[intMap]
local strField3 = dicMap.Field[3]
local ptrSub = fhGetItemPtr(ptrFld,"~."..strField3) -- Get matching fact field, if any
if ptrSub:IsNotNull() then
local strValue = dicRow[dicMap.Title] or "" -- Get CSV field value, if any -- V1.9.4
if strField3 == "DATE" then -- Does its Date value sync with CSV field value
if fhGetValueAsDate(ptrSub):Compare(SetDate(strValue,ptrSub,true)) ~= 0 or strValue == "" then -- V1.9.4
isFound = false
break
end
elseif strField3 == "AGE" then -- Does its Age value sync with CSV field value -- V1.9
local strSubA,strSubY,strSubM,strSubD = (fhGetDisplayText(ptrSub):lower().." "):match("(%D+)(%d-)%D+(%d-)%D+(%d-)")
local strValA,strValY,strValM,strValD = ("age "..strValue:lower().." "):match("(%D+)(%d-)%D+(%d-)%D+(%d-)")
if strSubA ~= strValA or strSubY ~= strValY or strSubM ~= strValM or strSubD ~= strValD then
isFound = false
break
end
elseif not fhGetDataClass(ptrSub):match("%l%l%l%ltext") then -- Apart from long/richtext fields, i.e. Note, Address, Text from Source
if fhGetValueAsText(ptrSub) ~= strValue then -- Does its value sync with CSV field value, e.g. Place, Cause
isFound = false
break
end
end
end
end
if isFound then return ptrFld end -- Matching fact found
ptrFld:MoveNext("SAME_TAG")
end
return nil -- No matching fact
end -- function FindFact
--[[ChooseSource
@name ChooseSource
@description Opens sources selection dialog.
]]
function ChooseSource()
local ptrSource = fhNewItemPtr()
if tblSet.AddSource == "Yes" then
local strTitle = nil
if tblSet.SourceRec == "Yes" then
local tblSource = fhPromptUserForRecordSel("SOUR",1)
if #tblSource == 0 then
fhMessageBox("User cancelled source selection.\nNo global source citations will be added.")
tblSet.AddSource = "No"
else
ptrSource = tblSource[1]
strTitle = fhGetValueAsText(fhGetItemPtr(ptrSource,"~.TITL"))
end
elseif tblSet.SourceRec == "No" then
ptrSource = fhCreateItem("SOUR")
strTitle = string.match(strImportFile,".*\\(.-)%.")
local ptrLink = fhCreateItem("TITL", ptrSource)
fhSetValueAsText(ptrLink, strTitle)
end
strSourceTitle = strTitle -- MBT Used by AddSources function
end
dicSources = {}
ptrSource:MoveToFirstRecord("SOUR") -- MBT Obtain all Source Titles and record pointers
while ptrSource:IsNotNull() do
local strTitle = fhGetValueAsText(fhGetItemPtr(ptrSource,"~.TITL"))
dicSources[strTitle] = ptrSource:Clone()
ptrSource:MoveNext()
end
end -- function ChooseSource
--[[AddSources
@name AddSources -- MBT
@description Adds source citations to the indicated pointer
@param ptrItem any item pointer
@param intRow The line of CSV data being processed
@param strHead CSV heading of data item
]]
local dicAssess = {
["1"]="Primary evidence"; ["2"]="Secondary evidence"; ["3"]="Questionable"; ["4"]="Unreliable";
["P"]="Primary evidence"; ["S"]="Secondary evidence"; ["Q"]="Questionable"; ["U"]="Unreliable";
}
function AddSources(ptrItem,intRow,strHead)
-- Get a list of existing Source Titles cited by this item
local dicSour = {}
local ptrSour = fhGetItemPtr(ptrItem,"~.SOUR")
while ptrSour:IsNotNull() do
local strTitle = fhGetValueAsText(fhGetItemPtr(ptrSour,"~>TITL"))
dicSour[strTitle] = ptrSour:Clone()
ptrSour:MoveNext("SAME_TAG")
end
-- If global Source is enabled and is not already cited then add it to Item
if tblSet.AddSource == "Yes" and not dicSour[strSourceTitle] then
local ptrLink = fhCreateItem("SOUR",ptrItem)
if ptrLink:IsNull() then return end -- Quit if citations not allowed
fhSetValueAsLink(ptrLink,dicSources[strSourceTitle])
dicSour[strSourceTitle] = ptrLink:Clone()
end
-- If any Item Citation is defined then add it to Item
strHead = (strHead:gsub("([%.%>%:].+)[%.%>%:]%w+$","%1"))..".SOUR"
strHead = strHead:gsub("%-","%%-") -- Inhibit pattern 'magic' - hyphen symbol -- V1.9.1
-- i.e. "P" => "P.SOUR", "P.NAME" => "P.NAME.SOUR", "P.CHR.DATE" => "P.CHR.SOUR", "P_S.MARR.PLAC" => "P_S.MARR.SOUR"
local ptrLink = fhNewItemPtr()
local dicRow = arrRows[intRow]
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid then
local strField, strExtra = dicMap.Valid:match("^SOUR~([^~]+)~?([^~]-)$")
if strField then
local strValue = dicRow[dicMap.Title]
local strRefer = dicMap.Refer
-- Global Citations are only 9 chars, and explicit Citations must match heading of current Item
if strValue and ( #strRefer < 11 or strRefer:match(strHead) ) then
if strField == "TITL" then
if not dicSources[strValue] then -- Create a new Source record with Title
local ptrSour = fhCreateItem("SOUR")
local ptrTitl = fhCreateItem("TITL",ptrSour)
fhSetValueAsText(ptrTitl,strValue)
dicSources[strValue] = ptrSour:Clone()
end
if not dicSour[strValue] then -- Create a new Source Citation
local ptrLink = fhCreateItem("SOUR",ptrItem)
if ptrLink:IsNull() then return end -- Quit if citations not allowed
fhSetValueAsLink(ptrLink,dicSources[strValue])
dicSour[strValue] = ptrLink:Clone()
end
ptrLink = dicSour[strValue]:Clone()
elseif ptrLink:IsNotNull() then -- Create the new Citation fields
local ptrField = fhGetItemPtr(ptrLink,"~."..strField)
if ptrField:IsNull() then
ptrField = fhCreateItem(strField,ptrLink,true)
end
if strExtra then
local ptrExtra = fhGetItemPtr(ptrField,"~."..strExtra)
if ptrExtra:IsNull() then
ptrExtra = fhCreateItem(strExtra,ptrField)
end
ptrField = ptrExtra:Clone()
end
if strExtra == "DATE" then
SetDate(strValue,ptrField)
elseif strField == "QUAY" then
local strAssess = dicAssess[strValue] or strValue
local isOK = fhSetValueAsText(ptrField,strAssess)
else
AppendText(ptrField,strValue)
end
end
end
end
end
end
end -- function AddSources
--[[FullName
@name FullName
@description Provides full name if available, or combines given and surname.
@param dicRow The line contents
@param strPerson The identifier P, S, etc)
@return FullName
]]
function FullName(dicRow,strPerson)
local function CleanName(strName)
strName = strName or ""
strName = strName:gsub( "^ +", "" )
strName = strName:gsub( " +$", "" )
return strName
end -- local function CleanName
local strFullName = CleanName(dicRow[tblMap[strPerson..".NAME:"]]) -- try full name field
if #strFullName == 0 then -- no full name
local strRev = CleanName(dicRow[tblMap[strPerson..".NAME:SURNAME_FIRST"]]) -- try reversed name
if #strRev > 0 then
strFullName = string.gsub(strRev, "(.-)(%s)(.*)$", "%3%2%1")
else -- join given and surname
strFullName = CleanName(dicRow[tblMap[strPerson..".NAME:GIVEN"]]).." /"..CleanName(dicRow[tblMap[strPerson..".NAME:SURNAME"]]).."/"
end
end
if strFullName == " //" then strFullName = "" end
--# strFullName = strFullName:gsub("(%a)([%w_']*)", function(first, rest) return first:upper()..rest:lower() end ) -- convert to title case -- http://lua-users.org/wiki/StringRecipes
strFullName = strFullName:gsub("([a-zA-Z\194-\244][\128-\191]*)([^ ]*)", function(first, rest) return first:upper()..rest:lower() end ) -- V1.8 -- convert to title case -- http://lua-users.org/wiki/StringRecipes
return strFullName
end -- function FullName
--[[NameExists
@name NameExists
@description Check if the person indicated by the pointer already has the exact name listed in the CSV file
@param ptrRec Pointer to record
@param strFullName
]]
function NameExists(ptrRec,strFullName)
local function CleanName(strName)
strName = strName:gsub( "/" , " " )
strName = strName:gsub( "//", " " )
strName = strName:gsub( " ", " " )
strName = strName:gsub( " $", "" )
return strName
end -- local function CleanName
strFullName = CleanName(strFullName)
for ptrTag in tags(ptrRec) do
if fhGetTag(ptrTag) == "NAME" then
if CleanName(fhGetValueAsText(ptrTag)) == strFullName then
return ptrTag -- V1.7
end
end
end
return false
end -- function NameExists
function tags(pi) -- Tags iterator
local pf = fhNewItemPtr()
local pf2 = fhNewItemPtr()
pf:MoveToFirstChildItem(pi)
return function ()
while pf:IsNotNull() do
pf2:MoveTo(pf)
pf:MoveNext()
return pf2
end
end
end -- function tags
--[[AppendText
@name AppendText
@description Appends new multi-column text to any text field such as NOTE2 or TEXT or ADDR
@param ptrText pointer to field
@param strText string to append
]]
function AppendText(ptrText,strText)
if ptrText:IsNotNull() then
strText = strText or ""
local strSep = ""
local strOrig = fhGetValueAsText(ptrText)
if not strOrig:find(strText,1,"plain") then -- Ensure existing text from synchronized record is not duplicated -- V1.7
if strOrig ~= "" then strSep = tblSet.Separator end
fhSetValueAsText(ptrText,strOrig..strSep..strText)
if strSep:match("\n")
and fhGetValueAsText(ptrText) ~= strOrig..strSep..strText then
fhSetValueAsText(ptrText,strOrig..", "..strText)
end
end
end
end -- function AppendText
--[[SetDate
@name SetDate
@description Edits a date input as a text to make more acceptable to Family Historian and sets the date at the pointer given
@param strDate The date text to be considered
@param ptrDate The pointer to the date
]]
function SetDate(strDate,ptrDate,isValueOnly)
local datDate = fhNewDate()
strDate = strDate or "" -- Cater for no date -- V1.8
strDate = string.gsub(strDate, "before", "bef")
strDate = string.gsub(strDate, "after", "aft")
strDate = string.gsub(strDate, "between", "btw")
strDate = string.gsub(strDate, "from", "frm")
strDate = string.gsub(strDate, "<", "bef")
strDate = string.gsub(strDate, ">", "aft")
if not strDate:match("/ ?%d") then -- Allow / separators -- V1.9.2
strDate = string.gsub(strDate, "[%s%p]", " ")
end
datDate:SetValueAsText(strDate,true)
if isValueOnly then return datDate end
fhSetValueAsDate(ptrDate,datDate)
end -- function SetDate
--[[
@Module: +fh+progbar_v3
@Author: Mike Tate
@Version: 3.0
@LastUpdated: 27 Aug 2020
@Description: Progress Bar library module.
@V3.0: Function Prototype Closure version.
@V1.0: Initial version.
]]
local function progbar_v3()
local fh = {} -- Local environment table
require "iuplua" -- To access GUI window builder
iup.SetGlobal("CUSTOMQUITMESSAGE","YES") -- Needed for IUP 3.28
local tblBars = {} -- Table for optional external attributes
local strBack = "255 255 255" -- Background colour default is white
local strBody = "0 0 0" -- Body text colour default is black
local strFont = nil -- Font dialogue default is current font
local strStop = "255 0 0" -- Stop button colour default is red
local intPosX = iup.CENTER -- Show window default position is central
local intPosY = iup.CENTER
local intMax, intVal, intPercent, intStart, intDelta, intScale, strClock, isBarStop
local lblText, barGauge, lblDelta, btnStop, dlgGauge
local function doFocus() -- Bring the Progress Bar window into Focus
dlgGauge.BringFront="YES" -- If used too often, inhibits other windows scroll bars, etc
end -- local function doFocus
local function doUpdate() -- Update the Progress Gauge and the Delta % with clock
barGauge.Value = intVal
lblDelta.Title = string.format("%4d %% %s ",math.floor(intPercent),strClock)
end -- local function doUpdate
local function doReset() -- Reset all dialogue variables and Update display
intVal = 0 -- Current value of Progress Bar
intPercent= 0.01 -- Percentage of progress
intStart = os.time() -- Start time of progress
intDelta = 0 -- Delta time of progress
intScale = math.ceil( intMax / 1000 ) -- Scale of percentage per second of progress (initial guess is corrected in Step function)
strClock = "00 : 00 : 00" -- Clock delta time display
isBarStop = false -- Stop button pressed signal
doUpdate()
doFocus()
end -- local function doReset
function fh.Start(strTitle,intMaximum) -- Create & start Progress Bar window
if not dlgGauge then
strTitle = strTitle or "" -- Dialogue and button title
intMax = intMaximum or 100 -- Maximun range of Progress Bar, default is 100
local strSize = tostring( math.max( 100, string.len(" Stop "..strTitle) * 8 ) ).."x30" -- Adjust Stop button size to Title
lblText = iup.label { Title=" "; Expand="YES"; Alignment="ACENTER"; Tip="Progress Message"; }
barGauge = iup.progressbar { RasterSize="400x30"; Value=0; Max=intMax; Tip="Progress Bar"; }
lblDelta = iup.label { Title=" "; Expand="YES"; Alignment="ACENTER"; Tip="Percentage and Elapsed Time"; }
btnStop = iup.button { Title=" Stop "..strTitle; RasterSize=strSize; FgColor=strStop; Tip="Stop Progress Button"; action=function() isBarStop = true end; } -- Signal Stop button pressed return iup.CLOSE -- Often caused main GUI to close !!!
dlgGauge = iup.dialog { Title=strTitle.." Progress "; Font=strFont; FgColor=strBody; Background=strBack; DialogFrame="YES"; -- Remove Windows minimize/maximize menu
iup.vbox{ Alignment="ACENTER"; Gap="10"; Margin="10x10";
lblText;
barGauge;
lblDelta;
btnStop;
};
move_cb = function(self,x,y) tblBars.X = x tblBars.Y = y end;
close_cb = btnStop.action; -- Windows Close button = Stop button
}
if type(tblBars.GUI) == "table"
and type(tblBars.GUI.ShowDialogue) == "function" then
dlgGauge.move_cb = nil -- Use GUI library to show & move window
tblBars.GUI.ShowDialogue("Bars",dlgGauge,btnStop,"showxy")
else
dlgGauge:showxy(intPosX,intPosY) -- Show the Progress Bar window
end
doReset() -- Reset the Progress Bar display
end
end -- function Start
function fh.Message(strText) -- Show the Progress Bar message
if dlgGauge then lblText.Title = strText end
end -- function Message
function fh.Step(intStep) -- Step the Progress Bar forward
if dlgGauge then
intVal = intVal + ( intStep or 1 ) -- Default step is 1
local intNew = math.ceil( intVal / intMax * 100 * intScale ) / intScale
if intPercent ~= intNew then -- Update progress once per percent or per second, whichever is smaller
intPercent = math.max( 0.1, intNew ) -- Ensure percentage is greater than zero
if intVal > intMax then intVal = intMax intPercent = 100 end -- Ensure values do not exceed maximum
intNew = os.difftime(os.time(),intStart)
if intDelta < intNew then -- Update clock of elapsed time
intDelta = intNew
intScale = math.ceil( intDelta / intPercent ) -- Scale of seconds per percentage step
local intHour = math.floor( intDelta / 3600 )
local intMins = math.floor( intDelta / 60 - intHour * 60 )
local intSecs = intDelta - intMins * 60 - intHour * 3600
strClock = string.format("%02d : %02d : %02d",intHour,intMins,intSecs)
end
doUpdate() -- Update the Progress Bar display
end
iup.LoopStep()
end
end -- function Step
function fh.Focus() -- Bring the Progress Bar window to front
if dlgGauge then doFocus() end
end -- function Focus
function fh.Reset() -- Reset the Progress Bar display
if dlgGauge then doReset() end
end -- function Reset
function fh.Stop() -- Check if Stop button pressed
iup.LoopStep()
return isBarStop
end -- function Stop
function fh.Close() -- Close the Progress Bar window
isBarStop = false
if dlgGauge then dlgGauge:destroy() dlgGauge = nil end
end -- function Close
function fh.Setup(tblSetup) -- Setup optional table of external attributes
if tblSetup then
tblBars = tblSetup
strBack = tblBars.Back or strBack -- Background colour
strBody = tblBars.Body or strBody -- Body text colour
strFont = tblBars.Font or strFont -- Font dialogue
strStop = tblBars.Stop or strStop -- Stop button colour
intPosX = tblBars.X or intPosX -- Window position
intPosY = tblBars.Y or intPosY
end
end -- function Setup
return fh
end -- local function progbar_v3
local progbar = progbar_v3() -- To access FH progress bars module
--[[ListItemLookup
@name ListItemLookup
@description lookup value in Tag and optionally SubTag of a list
@param arrList the list
@param strReturn the entry in list to return
@param strValue the value to match
@param strSubTag the subtag to match
]]
function ListItemLookup(arrList,strReturn,strValue,strSubTag)
if strValue then
for _, dicList in ipairs (arrList) do
if strValue == dicList.Tag then
if not strSubTag or strSubTag == dicList.SubTag then
return dicList[strReturn]
end
end
end
end
return strValue or ""
end -- function ListItemLookup
--[[FillGridbox
@name FillGridbox
@description Adds a line of boxes to the GUI for each column in the input CSV
@param arrLines CSV data lines
@param arrTitles CSV column titles
]]
local dicTip = {
Header = " CSV data file column header ";
Sample = " CSV data file cell value ";
Record = " Type of record to be created ";
Field2 = " Label for data field level 1 ";
Field3 = " Label for data field level 2 ";
}
function FillGridbox(arrLines,arrTitles)
local function MakeDropList(intCol,strType) -- items added later via FillDropDowns(intCol,strPart)
return iup.list { DropDown = "Yes"; Visible_Items = "16"; Tip = dicTip[strType]; Sort = "No"; -- use tblLists[strType].Label order
action = function(self,text,item,state) ActionChange(intCol,strType,text,state) end; } -- killfocus_cb replaced by action
end -- local function MakeDropList
arrRows = arrLines
dicBox = {} -- used in the GUI
tblMap = {}
for intCol, strTitle in ipairs (arrTitles) do
-- interpret headings
tblMap[intCol] = SplitTitle(strTitle)
TestFields(intCol)
if not tblMap[intCol].Valid then
TitlesSecondChance(intCol)
TestFields(intCol)
end
-- add to GUI
if dicBox[strTitle] then -- cope with duplicate title
strTitle = strTitle.." "
tblMap[intCol].Title = strTitle
tblMap[intCol].Valid = false
end
dicBox[strTitle] = {}
dicBox[strTitle].Header = iup.label { Title = strTitle; Tip = dicTip.Header; }
dicBox[strTitle].Sample = iup.text { Value = arrLines[1][strTitle]; FgColor = strNavy; ReadOnly = "Yes"; Tip = dicTip.Sample; }
dicBox[strTitle].Record = MakeDropList(intCol,"Record")
dicBox[strTitle].Field2 = MakeDropList(intCol,"Field2")
dicBox[strTitle].Field3 = MakeDropList(intCol,"Field3")
for _, strData in ipairs({ "Header"; "Sample"; "Record"; "Field2"; "Field3"; }) do
local iupBox = dicBox[strTitle][strData]
iup.Append(iupGridbox,iupBox )
iup.Map(iupBox) -- need to do this so new fields will show
end
end
if not tblMap["P.REFN"] then -- V1.7
fhMessageBox("\nCSV Data File Syntax Error\n\nThe data has no P.REFN column defined.\n","MB_OK","MB_ICONEXCLAMATION")
end
end -- function FillGridbox
--[[FillItemLookup
@name FillItemLookup
@description lookup value in an item of a list and append list to iupBox
@param iupBox the box field to append list
@param arrList the list
@param ... the other ListItemLookup parameters
]]
function FillItemLookup(iupBox,arrList,...)
for _, dicList in ipairs (arrList) do
iupBox.AppendItem = dicList.Label or "?"
end
return ListItemLookup(arrList,...)
end -- function FillItemLookup
--[[SetListValue
@name SetListValue
@description set field list integer value matching item in list
@param iupBox the box field list
@param strVal the text value of item
]]
function SetListValue(iupBox,strVal)
if #strVal > 0 then
for intItem = 1, 99 do
if iupBox[tostring(intItem)] == strVal then
iupBox.Value = intItem
return
end
end
end
iupBox.Value = 0
end -- function SetListValue
--[[FillDropDowns
@name FillDropDowns
@description Populates dropdown lists in the GUI
@param intCol The column number in the CSV file
@param strPart Record or Field2 or Field3 (ignored) or All
]]
function FillDropDowns(intCol,strPart)
local dicMap = tblMap[intCol]
local iupBox = dicBox[dicMap.Title]
local arrField = dicMap.Field
local arrLabel = dicMap.Label
local intRecordVal = 0
local strField2Val = arrLabel[2]
local strField3Val = ""
if strPart == "Record" or strPart == "All" then
-- Record (primary, spouse, family, etc)
iupBox.Record.RemoveItem = "All"
iupBox.Field2.RemoveItem = "All"
iupBox.Field3.RemoveItem = "All"
for _, dicList in ipairs (tblLists.Record) do
iupBox.Record.AppendItem = dicList.Label
end
if strPart == "Record" or dicMap.Valid then
local dicRel = tblRel[arrField[0]]
if dicRel then
intRecordVal = dicRel.List
arrField[1] = dicRel.RecTag
end
else
arrField[1] = "EXCL"
arrField[2] = ""
end
iupBox.Record.Value = intRecordVal
local strRecTag = arrField[1]
-- Field2
if strRecTag == "SOUR" then
strField2Val = FillItemLookup(iupBox.Field2,tblLists.Source,"Label",arrField[2])
SetListValue(iupBox.Field2,strField2Val)
elseif strRecTag ~= "EXCL" then
strField2Val = ListItemLookup(tblLists.Field2,"Label",arrField[2])
for _, dicList in ipairs (tblLists.Field2) do
if strRecTag == "INDI" then
if dicList.RecTag == "INDI" or dicList.RecTag == "FAM" then
iupBox.Field2.AppendItem = dicList.Label
end
elseif dicList.RecTag == strRecTag or dicList.FactType == "MISC" or dicList.FactType == "Source" then
iupBox.Field2.AppendItem = dicList.Label
end
end
SetListValue(iupBox.Field2,strField2Val)
end
end
if strPart == "Field2" or strPart == "All" then
-- Field3
iupBox.Field3.RemoveItem = "All"
if strPart == "Field2" or dicMap.Valid then
if strField2Val == "Note" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Notes,"Label",arrField[3],arrField[4])
elseif strField2Val == "Name" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Names,"Label",arrField[3],arrField[4])
elseif strField2Val == "Source" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Source,"Label",arrField[3])
elseif strField2Val ~= "Custom Ref Id" and strField2Val ~= "Sex" and arrField[0] ~= "SOUR" then
strField3Val = ListItemLookup(tblLists.Field3,"Label",arrField[3],arrField[4])
for intList, dicList in ipairs (tblLists.Field3) do
if ( dicList.Usage == "ALL" )
or ( dicList.Usage == arrField[1] )
or ( dicList.Usage == strField2Val ) then
iupBox.Field3.AppendItem = dicList.Label
end
end
end
end
SetListValue(iupBox.Field3,strField3Val)
end
tblMap[intCol].Field = arrField
end -- function FillDropDowns
--[[ActionChange
@name ActionChange
@description Checks field lists for if the given fact label exits.
Triggered by the user entering a value in the field interpretation dialog.
Writes new values to tblMap, resets dropdowns for the line.
Then calls functions to test the new values and update the colour coding on the field interpretation dialog tab.
@param intCol the title column number
@param strPart whether record, field, subfield, etc
@param strText new chosen value
@param intState 1 when new item chosen
]]
function ActionChange(intCol,strPart,strText,intState) -- MBT -- rewritten
if intState == 1 and strText then
local dicMap = tblMap[intCol]
local arrLabel = dicMap.Label
local arrField = dicMap.Field
local dicField = {} -- Lookup for arrField index
dicField.Record = 0
dicField.Field2 = 2
dicField.Field3 = 3
local intField = dicField[strPart]
local tblList = tblLists[strPart]
if arrField[intField-1] == "NAME" then tblList = tblLists.Names end
if arrField[intField-1] == "NOTE2" then tblList = tblLists.Notes end
if arrField[intField-1] == "SOUR" then tblList = tblLists.Source end
local strValue = strText -- Grab text value chosen by user
local intIndex = tblList[strValue] -- Convert to List index -- or tonumber(strValue)
local dicEntry = tblList[intIndex] -- Get entry from List, if any
if dicEntry then strValue = dicEntry.Tag end -- Use entry Tag as value
if arrField[intField] ~= strValue then
if strValue then strValue = strValue:upper() end
arrField[intField] = strValue -- Update arrField & arrLabel values
if intField == 0 then -- Special case for Record
intField = 1
arrField[1] = dicEntry.RecTag or "INDI"
arrLabel[1] = arrField[1]
strValue = strText
else
arrField[intField] = strValue
arrLabel[intField] = strText
end
arrField[intField+1] = nil
arrLabel[intField+1] = nil
arrField[intField+2] = nil
arrLabel[intField+2] = nil
arrField[intField+3] = nil
arrLabel[intField+3] = nil
end
if strPart == "Field3" then
if dicEntry then
arrField[3] = dicEntry.Tag
local strSubTag = dicEntry.SubTag
if strSubTag then
arrField[4] = strSubTag
end
end
end
tblMap[intCol].Field = arrField
tblMap[intCol].Label = arrLabel
TestFields(intCol)
if not ( tblMap[intCol].Valid or strPart == "Record" ) then
TitlesSecondChance(intCol)
TestFields(intCol)
end
FillDropDowns(intCol,strPart)
MarkValid(intCol)
AmendLabelValidText()
end
end -- function ActionChange
----------------------------
-- MAIN CODE STARTS HERE
----------------------------
----------------------------
-- Establish tables
----------------------------
-- manage relationships
tblRel = {}
-- Individuals
tblRel.P = { List = 1 ; Label = "Primary" ; Sex= "" ; RecTag = "INDI"; Fams = "P_S" ; Spouse = "S" ;}
tblRel.S = { List = 2 ; Label = "Spouse" ; Sex= "" ; RecTag = "INDI"; Fams = "P_S" ; Spouse = "P" ;} -- spouse
tblRel.PF = { List = 3 ; Label = "Father" ; Sex= "M"; RecTag = "INDI"; Fams = "PF_PM"; Spouse = "PM";} -- primary's father
tblRel.PM = { List = 4 ; Label = "Mother" ; Sex= "F"; RecTag = "INDI"; Fams = "PF_PM"; Spouse = "PF";} -- primary's mother
tblRel.SF = { List = 5 ; Label = "Spouse's father"; Sex= "M"; RecTag = "INDI"; Fams = "SF_SM"; Spouse = "SM";} -- spouse's father
tblRel.SM = { List = 6 ; Label = "Spouse's mother"; Sex= "F"; RecTag = "INDI"; Fams = "SF_SM"; Spouse = "SF";} -- spouse's mother
-- Families -- Spouse1 will default to Husband (and become male) if no other gender information is present, Spouse2 to Wife/female.
tblRel.P_S = { List = 7 ; Label = "Family: Primary & Spouse" ; RecTag = "FAM"; Spouse1 = "P" ; Spouse2 = "S" ; } -- family primary-spouse
tblRel.PF_PM = { List = 8 ; Label = "Family: Primary's parents"; RecTag = "FAM"; Spouse1 = "PF"; Spouse2 = "PM"; Child = "P";} -- family primary's parents
tblRel.SF_SM = { List = 9 ; Label = "Family: Spouse's parents" ; RecTag = "FAM"; Spouse1 = "SF"; Spouse2 = "SM"; Child = "S";} -- family spouse's parents
-- Other
tblRel.SOUR = { List = 10; Label = "Source Citation" ; RecTag = "SOUR" ;}
tblRel.EXCL = { List = 11; Label = "Exclude from import" ; RecTag = "EXCL" ;} -- No record (column not to be imported)
tblPeople = {} -- will keep pointers for each person -- tblPeople[intRow][strIdent] = ptrInd:Clone()
tblFamilies = {} -- will keep pointers for each family -- tblFamilies[intRow][strIdent] = ptrFam:Clone()
tblRefn = {} -- reference numbers -> ptr
tblLists = {} -- keeps lookup information
-- record
tblLists.Record = {}
for strTag, dicRel in pairs(tblRel) do
tblLists.Record[dicRel.List] = { Label = dicRel.Label; Tag = strTag; RecTag = dicRel.RecTag; }
end
-- field
tblLists.Field2 = {}
table.insert(tblLists.Field2,{ Label = "Custom Ref Id" ; Tag = "REFN" ; FactType = "MISC" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Name" ; Tag = "NAME" ; FactType = "Misc" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Note" ; Tag = "NOTE2" ; FactType = "MISC" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Sex" ; Tag = "SEX" ; FactType = "Misc" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Flag" ; Tag = "_FLGS" ; FactType = "Misc" ; RecTag = "INDI"; }) -- V1.9.4
table.insert(tblLists.Field2,{ Label = "Source" ; Tag = "SOUR" ; FactType = "Source" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Adoption" ; Tag = "ADOP" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ADOPTED" ;})
table.insert(tblLists.Field2,{ Label = "Annulment" ; Tag = "ANUL" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ANNULLED" ;})
table.insert(tblLists.Field2,{ Label = "Baptism" ; Tag = "BAPM" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BAPTISED" ;})
table.insert(tblLists.Field2,{ Label = "Bar Mitzvah" ; Tag = "BARM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Bas Mitzvah" ; Tag = "BASM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Birth" ; Tag = "BIRT" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BORN" ;})
table.insert(tblLists.Field2,{ Label = "Blessing" ; Tag = "BLES" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Burial" ; Tag = "BURI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BURIED" ;})
table.insert(tblLists.Field2,{ Label = "Census" ; Tag = "CENS" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Child Count" ; Tag = "NCHI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Christening" ; Tag = "CHR" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CHIRISTENED" ;})
table.insert(tblLists.Field2,{ Label = "Christening (adult)" ; Tag = "CHRA" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Confirmation" ; Tag = "CONF" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CONFIRMED" ;})
table.insert(tblLists.Field2,{ Label = "Cremation" ; Tag = "CREM" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CREMATED" ;})
table.insert(tblLists.Field2,{ Label = "Death" ; Tag = "DEAT" ; FactType = "Event" ; RecTag = "INDI"; Verb = "DIED" ;})
table.insert(tblLists.Field2,{ Label = "Divorce" ; Tag = "DIV" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "DIVORCED" ;})
table.insert(tblLists.Field2,{ Label = "Divorce Filed" ; Tag = "DIVF" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Education" ; Tag = "EDUC" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "EDUCATED" ;})
table.insert(tblLists.Field2,{ Label = "Emigration" ; Tag = "EMIG" ; FactType = "Event" ; RecTag = "INDI"; Verb = "EMIGRATED" ;})
table.insert(tblLists.Field2,{ Label = "Engagement" ; Tag = "ENGA" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "ENGAGED" ;})
table.insert(tblLists.Field2,{ Label = "First communion" ; Tag = "FCOM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Graduation" ; Tag = "GRAD" ; FactType = "Event" ; RecTag = "INDI"; Verb = "GRADUATED" ;})
table.insert(tblLists.Field2,{ Label = "Group/Caste Membership" ; Tag = "CAST" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Immigration" ; Tag = "IMMI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "IMMIGRATED" ;})
table.insert(tblLists.Field2,{ Label = "Marriage" ; Tag = "MARR" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "MARRIED" ;})
table.insert(tblLists.Field2,{ Label = "Marriage Banns" ; Tag = "MARB" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Contract" ; Tag = "MARC" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Count" ; Tag = "NMR" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Marriage Licence" ; Tag = "MARL" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Settlement" ; Tag = "MARS" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "National Identity Number" ; Tag = "IDNO" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "National or Tribal Origin" ; Tag = "NATI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Naturalisation" ; Tag = "NATU" ; FactType = "Event" ; RecTag = "INDI"; Verb = "NATURALISED";})
table.insert(tblLists.Field2,{ Label = "Occupation" ; Tag = "OCCU" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "EMPLOYED" ;})
table.insert(tblLists.Field2,{ Label = "Ordination" ; Tag = "ORDN" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ORDAINED" ;})
table.insert(tblLists.Field2,{ Label = "Physical Description" ; Tag = "DSCR" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Possessions" ; Tag = "PROP" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "POSSESSED" ;})
table.insert(tblLists.Field2,{ Label = "Probate" ; Tag = "PROB" ; FactType = "Event" ; RecTag = "INDI"; Verb = "PROBATED" ;})
table.insert(tblLists.Field2,{ Label = "Religion" ; Tag = "RELI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Residence" ; Tag = "RESI" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "RESIDED" ;})
table.insert(tblLists.Field2,{ Label = "Retirement" ; Tag = "RETI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "RETIRED" ;})
table.insert(tblLists.Field2,{ Label = "Title" ; Tag = "TITL" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "US Social Security Number" ; Tag = "SSN" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Will" ; Tag = "WILL" ; FactType = "Event" ; RecTag = "INDI"; })
-- subfields
tblLists.Field3 = {}
table.insert(tblLists.Field3,{ Label = "Address" ; Tag = "ADDR" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Age" ; Tag = "AGE" ; Usage = "INDI" ;})
table.insert(tblLists.Field3,{ Label = "Age: Primary" ; Tag = "AGE" ; Usage = "FAM" ; SubTag = "P" ; })
table.insert(tblLists.Field3,{ Label = "Age: Spouse" ; Tag = "AGE" ; Usage = "FAM" ; SubTag = "S" ; })
table.insert(tblLists.Field3,{ Label = "Cause" ; Tag = "CAUS" ; Usage = "Death" ;}) -- death
table.insert(tblLists.Field3,{ Label = "Date" ; Tag = "DATE" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Note" ; Tag = "NOTE2" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Type" ; Tag = "TYPE" ; Usage = "ALL" ;}) -- V1.9.2
table.insert(tblLists.Field3,{ Label = "Place" ; Tag = "PLAC" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Source> Source Title" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "TITL" ; })
table.insert(tblLists.Field3,{ Label = "Source: Assessment" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "QUAY" ; })
table.insert(tblLists.Field3,{ Label = "Source: Entry Date" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "DATE" ; })
table.insert(tblLists.Field3,{ Label = "Source: Text From Source" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "TEXT" ; })
table.insert(tblLists.Field3,{ Label = "Source: Where in Source" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "PAGE" ; })
table.insert(tblLists.Field3,{ Label = "Source: Citation Note" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "NOTE2" ; })
-- citations field
tblLists.Source = {}
table.insert(tblLists.Source,{ Label = "Source Title" ; Tag = "TITL" ; SubTag ="TITL" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Assessment" ; Tag = "QUAY" ; SubTag ="QUAY" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Entry Date" ; Tag = "DATE" ; SubTag ="DATE" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Text From Source" ; Tag = "TEXT" ; SubTag ="TEXT" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Where in Source" ; Tag = "PAGE" ; SubTag ="PAGE" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Citation Note" ; Tag = "NOTE2" ; SubTag ="NOTE2" ; RecTag = "SOUR"; })
-- names subfield
tblLists.Names = {}
table.insert(tblLists.Names ,{ Label = "Full name" ; Tag = nil ; })
table.insert(tblLists.Names ,{ Label = "Given" ; Tag = "GIVEN" ; })
table.insert(tblLists.Names ,{ Label = "Used" ; Tag = "_USED" ; }) -- V1.9.3
table.insert(tblLists.Names ,{ Label = "Surname" ; Tag = "SURNAME" ; })
table.insert(tblLists.Names ,{ Label = "Surname First" ; Tag = "SURNAME_FIRST" ; })
table.insert(tblLists.Names ,{ Label = "Note" ; Tag = "NOTE2" ; })
table.insert(tblLists.Names ,{ Label = "Type" ; Tag = "TYPE" ; }) -- V1.9.2
table.insert(tblLists.Names ,{ Label = "Source> Source Title" ; Tag = "SOUR"; SubTag = "TITL" ; })
table.insert(tblLists.Names ,{ Label = "Source: Assessment" ; Tag = "SOUR"; SubTag = "QUAY" ; })
table.insert(tblLists.Names ,{ Label = "Source: Entry Date" ; Tag = "SOUR"; SubTag = "DATE" ; })
table.insert(tblLists.Names ,{ Label = "Source: Text From Source" ; Tag = "SOUR"; SubTag = "TEXT" ; })
table.insert(tblLists.Names ,{ Label = "Source: Where in Source" ; Tag = "SOUR"; SubTag = "PAGE" ; })
table.insert(tblLists.Names ,{ Label = "Source: Citation Note" ; Tag = "SOUR"; SubTag = "NOTE2" ; })
-- notes subfield
tblLists.Notes = {}
table.insert(tblLists.Notes ,{ Label = "Source> Source Title" ; Tag = "SOUR"; SubTag = "TITL" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Assessment" ; Tag = "SOUR"; SubTag = "QUAY" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Entry Date" ; Tag = "SOUR"; SubTag = "DATE" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Text From Source" ; Tag = "SOUR"; SubTag = "TEXT" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Where in Source" ; Tag = "SOUR"; SubTag = "PAGE" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Citation Note" ; Tag = "SOUR"; SubTag = "NOTE2"; })
-- Add Label to index translation
for _, tblListsInfo in ipairs ({ tblLists.Record; tblLists.Field2; tblLists.Field3; tblLists.Source; tblLists.Names; tblLists.Notes; }) do
for intList, tblList in ipairs (tblListsInfo) do
tblListsInfo[tblList.Label] = intList
end
end
-------------------------------
-- Default settings
-------------------------------
tblSet = {} -- Settings table
tblSet.AddSource = "Yes" -- "Yes" = All item entries, "No" = None, "TBD" = Names and fields only
tblSet.SourceRec = "Yes" -- "Yes" = Choose source record, "No" = Create from file name
tblSet.Separator = ", "
tblSet.RetainID = "Yes" -- "Yes" = Retain REFN in Custom ID
tblSet.SyncREFN = "Yes" -- "Yes" = Sync existing Custom ID with each REFN -- V1.7
tblSet.UseRecId = "No" -- "Yes" = Treat unsynced integer REFN as Rec Id -- V1.9.1
-------------------------------
-- Prepare GUI
-------------------------------
function PrepareGUI()
local tblActivate = {}
local strFontBold = "Helvetica, Bold 10"
local strFontNorm = "Helvetica, Normal 10"
local iupLabelValid = iup.label {} -- V1.7
local strLabelValid = [[ columns have been successfully mapped to fields.
The plugin will not attempt to import columns where the Header Title is red.]]
function AmendLabelValidText() -- V1.7 -- Always use same layout before and after loading file
local intValid = 0
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid then
intValid = intValid + 1
end
end
iupLabelValid.Title = "Check the Field Interpretation (next tab).\n\n"..intValid.."/"..#tblMap..(strLabelValid:gsub("\t",""))
end -- function AmendLabelValidText
AmendLabelValidText() -- V1.7
local function MakeButton(strTitle,strActive,strColor,strTip)
strColor = strColor or strBlack
return iup.button { Title = strTitle; Active = strActive; FgColor = strColor; Tip = strTip; Font = strFontBold; Padding = "10x2"; Size = "80"; }
end -- local function MakeButton
local function ChooseButton(btnChoose)
btnChoose = MakeButton("Choose file","Yes",strBlack," Select the CSV data file ")
btnChoose.action = function(self)
local intStatus, strFile = GetExistingFile("Select CSV File","*.csv","Comma Separated File","Documents")
if intStatus ~= "-1" then
btnChoose.Active = "No"
strImportFile = strFile
local strFile,wasAnsi = FileNameToANSI(strFile)
if not ( wasAnsi ) then -- V1.9 -- Cater for non-ANSI filename
FSO:CopyFile(strImportFile,strFile)
end
FillGridbox( table.loadcsv(strFile,true) )
if not ( wasAnsi ) then
FSO:DeleteFile(strFile) -- V1.9 -- Cater for non-ANSI filename
end
iupSample.SpinMax = #arrRows
for intCol = 1, #tblMap do
FillDropDowns(intCol,"All")
MarkValid(intCol)
end
AmendLabelValidText()
iupDialog.Size = iup.NULL
iup.Refresh(iupDialog)
iupDialog.MinSize = iupDialog.RasterSize
for _, iupActivate in ipairs (tblActivate) do
iupActivate.Active = "Yes"
end
end
end
return btnChoose
end -- local function ChooseButton
local function HelpButton(btnHelp)
btnHelp = MakeButton("Help","Yes",strGreen," Open the help and advice page ")
btnHelp.action = function(self)
fhShellExecute("https://pluginstore.family-historian.co.uk/page/help/flexible-csv-importer","","","open")
fhSleep(3000,500)
iupDialog.BringFront="YES"
end
return btnHelp
end -- local function HelpButton
local function ImportButton(btnImport)
btnImport = MakeButton("Import","No",strBlack," Import the CSV data file ")
btnImport.action = function(self)
ImportRecords()
return iup.CLOSE
end
table.insert(tblActivate,btnImport)
return btnImport
end -- local function ImportButton
local function CancelButton(btnCancel)
btnCancel = MakeButton("Cancel","Yes",strRed," Cancel the Plugin ")
btnCancel.action = function(self)
fhMessageBox("Import cancelled at user request.")
return iup.CLOSE
end
return btnCancel
end -- local function CancelButton
local function MakeDropList(...)
return iup.list { DropDown = "Yes"; Value = "1"; Visible_Items = "9"; Size = "80"; ...; }
end -- local function MakeDropList
iupListAddSource = MakeDropList( "Yes", "No" )
iupListAddSource.action = function(self,text,item,state)
if state == 1 then tblSet.AddSource = text end
end
iupListSourceRec = MakeDropList( "Yes", "No" )
iupListSourceRec.action = function(self,text,item,state)
if state == 1 then tblSet.SourceRec = text end
end
iupListSeparator = MakeDropList( "[comma] , ", "[semicolon] ; ", "[spaces]", "[newline]", "[newline][newline]" )
iupListSeparator.action = function(self,text,item,state)
if state == 1 then
tblSet.Separator = ({ ", "; "; "; " "; "\n"; "\n\n"; })[item]
end
end
iupListRetainID = MakeDropList( "Yes", "No" )
iupListRetainID.action = function(self,text,item,state)
if state == 1 then tblSet.RetainID = text end
end
iupListSyncREFN = MakeDropList( "Yes", "No" )
iupListSyncREFN.action = function(self,text,item,state)
if state == 1 then tblSet.SyncREFN = text end
end
iupListUseRecId = MakeDropList( "No", "Yes" )
iupListUseRecId.action = function(self,text,item,state)
if state == 1 then tblSet.UseRecId = text end
end
iupDirections = iup.vbox { Font = strFontNorm;
iup.label { title = "This plugin imports data from a chosen CSV data file to Family Historian fields."; };
iup.hbox { iup.label { Title = "Step 1: "}; Alignment = "ACENTER"; Gap = "10"; ChooseButton(btnChoose); iup.fill{}; };
iup.hbox { iup.label { Title = "Step 2: "}; Alignment = "ACENTER"; Gap = "10"; iup.label { Title = "Check the Settings (below)."}; };
iup.hbox { iup.label { Title = "Step 3: "}; Alignment = "ACENTRE"; Gap = "10"; iupLabelValid; };
iup.hbox { iup.label { Title = "Step 4: "}; Alignment = "ACENTER"; Gap = "10"; ImportButton(Import1); };
};
local strSeparator = "Select a separator for multiple entries in note fields or other text fields :"
iupSettings = iup.vbox { Font = strFontNorm;
iup.hbox { iup.label { Title = "Add a source citation to every item imported ?"; }; iup.fill{}; iupListAddSource; };
iup.hbox { iup.label { Title = "Select existing source record for such citations ?"; }; iup.fill{}; iupListSourceRec; };
iup.hbox { iup.label { Title = "Select a separator for multiple entries in Note or other text fields :"; }; iup.fill{}; iupListSeparator; };
iup.hbox { iup.label { Title = "Retain the REFN Custom IDs of each person ?"; }; iup.fill{}; iupListRetainID ; };
iup.hbox { iup.label { Title = "Sync any Custom IDs with REFN of each person ?"; }; iup.fill{}; iupListSyncREFN ; };
iup.hbox { iup.label { Title = "Treat any unsynced integer REFN as a Record ID ?"; }; iup.fill{}; iupListUseRecId ; };
};
iupTab1 = iup.vbox { TabTitle = "Directions and Settings"; Margin = "10x5"; Gap = "5";
iup.frame { Title = "Directions"; Font = strFontBold; Margin = "20x5"; iupDirections; };
iup.frame { Title = "Settings" ; Font = strFontBold; Margin = "20x5"; iupSettings; };
};
iupSample = iup.text { Spin = "Yes"; SpinValue = 1; SpinMin = 1; SpinMax = 1; Size = "40"; Tip = " Select CSV data row number "; }
iupSample.spin_cb = function(self,intRow) -- get new line of sample data to display
for intCol, dicMap in ipairs (tblMap) do
dicBox[dicMap.Title].Sample.Value = arrRows[intRow][dicMap.Title] or ""
end
end
local function MakeTitle(strSize,strTitle,strTip)
return iup.label { Title = strTitle.." "; Tip = strTip; Font = strFontBold; Size = strSize; Expand = "Horizontal"; Alignment = "ACenter"; }
end -- local function MakeTitle
local function MakeGrid(...) -- first line. The other lines are appended after file import.
return iup.gridbox { Font = strFontNorm; Orientation = "Horizontal"; NumDiv = "5"; GapLin = "9"; GapCol = "4"; ExpandChildren = "Horizontal"; ...; }
end -- local function MakeGrid
iupGridbox = MakeGrid(
MakeTitle("88x0","",dicTip.Header:gsub(" $","s ")),
MakeTitle("88x0","",dicTip.Sample:gsub(" $","s ")),
MakeTitle("99x0","",dicTip.Record),
MakeTitle("88x0","",dicTip.Field2),
MakeTitle("88x0","",dicTip.Field3) )
iupTab2 = iup.vbox { TabTitle = "Field Interpretation"; Margin = "10x13"; Gap = "5";
iup.frame { Margin = "0x2";
iup.vbox {
iup.vbox { Expand = "Horizontal"; Margin = "5x5";
MakeGrid(
MakeTitle("88","Header Title" ,dicTip.Header:gsub(" $","s ")),
iup.hbox { Size = "88"; MakeTitle("20","Sample",dicTip.Sample:gsub(" $","s ")); iupSample; Margin = "0x0"; },
MakeTitle("99","Record relates to" ,dicTip.Record),
MakeTitle("88","Field Label" ,dicTip.Field2),
MakeTitle("99","Detail Label " ,dicTip.Field3)
);
};
iup.scrollbox { iupGridbox; ScrollBar = "Vertical"; Margin = "10x5"; };
};
};
iup.label { Title = "Choose a CSV data file to begin. Column header titles shown in red will not be imported."; Font = strFontNorm; };
};
iupLab3 = iup.label{ Title = "TBD"; Padding = "10x5"; Tip = " Some basic advice is provided here, \n but for more detail use the Help button "; } -- V1.6
iupTab3 = iup.vbox { TabTitle = "More Information"; Margin = "10x5";
iup.frame { Title = "Field naming"; Font = strFontBold; Margin = "10x5";
iup.scrollbox { ScrollBar = "Vertical"; Font = strFontNorm; iupLab3;
};
};
};
iupTabs = iup.tabs { iupTab1; iupTab2; iupTab3; Font = strFontBold; }
iupBtns = iup.hbox { HelpButton(Help); ImportButton(Import); CancelButton(Cancel); Homogeneous="Yes"; Gap = "50"; Margin = "5x5"; };
if fhGetAppVersion() > 6 then -- FH V7 IUP 3.28 -- V1.6
iupTabs.TabPadding = "10x5"
else -- FH V6 IUP 3.11 -- V1.6
iupTabs.Padding = "10x5"
end
iupDialog = iup.dialog { Title = "Flexible CSV Importer "..strVersion; iup.vbox { iupTabs; iupBtns; Alignment = "ACENTER"; }; close_cb = function() return iup.CLOSE end; }
iupDialog:show()
iupDialog.MinSize = iupDialog.RasterSize
iupLab3.Title = strFieldNaming -- Delay setting full text to prevent dialogue adopting full height of More Information tab -- V1.6
iup.Refresh(iupDialog)
if (iup.MainLoopLevel()==0) then
iup.MainLoop()
end
iupDialog:destroy()
end -- function PrepareGUI
--------------------------
-- Import records
--------------------------
function ImportRecords()
ChooseSource()
local intRows = 0
local intCells = 0
local intPeople = 0
local intFamilies = 0
local numStartTime = os.clock()
for _, dicRows in ipairs (arrRows) do -- count cells with data
for _, _ in pairs (dicRows) do intCells = intCells + 1 end
end
if intCells > 1000 then
progbar.Start("CSV Import",#arrRows)
end
if tblSet.SyncREFN == "Yes" then -- Build Custom ID REFN to record pointer table -- V1.7
local ptrRec = fhNewItemPtr()
ptrRec:MoveToFirstRecord("INDI")
while ptrRec:IsNotNull() do
local strRefn = fhGetItemText(ptrRec,"~.REFN")
if #strRefn > 0 then
tblRefn[strRefn] = ptrRec:Clone()
end
ptrRec:MoveNext()
end
ptrRec:MoveToFirstRecord("FAM")
while ptrRec:IsNotNull() do -- Cater for any two HUSB or WIFE links
local arrRefn = {}
for _, strTag in ipairs ({"~.HUSB[1]>";"~.WIFE[1]>";"~.HUSB[2]>";"~.WIFE[2]>";}) do
local ptrPart = fhGetItemPtr(ptrRec,strTag)
if ptrPart:IsNotNull() then
local strRefn = fhGetItemText(ptrPart,"~.REFN")
if #strRefn > 0 then
table.insert(arrRefn,strRefn)
if #arrRefn == 2 then break end
end
end
end
if #arrRefn == 2 then
tblRefn[arrRefn[1].."&"..arrRefn[2]] = ptrRec:Clone()
tblRefn[arrRefn[2].."&"..arrRefn[1]] = ptrRec:Clone()
end
ptrRec:MoveNext()
end
end
intCells = 0
for intRow = 1, #arrRows do
progbar.Message("Adding records for row "..intRow..", please wait")
progbar.Step()
intPeople = CreatePeople(intRow,intPeople)
if tblPeople[intRow] then
AddItems(intRow,"INDI",tblPeople)
end
intFamilies = CreateFamilies(intRow,intFamilies)
if tblFamilies[intRow] then
AddItems(intRow,"FAM",tblFamilies)
end
intRows = intRow
for _, _ in pairs (arrRows[intRow]) do intCells = intCells + 1 end
if progbar.Stop() then break end
end
progbar.Close()
local numEndTime = os.clock()
if intCells > 0 then
local strMessage = ""
strMessage = strMessage..intRows.." rows with "..intCells.." cells of information processed.\n\n"
strMessage = strMessage..intPeople.." people added,\n"
strMessage = strMessage..intFamilies.." families created,\n"
strMessage = strMessage..string.format("in ".."%.2f", numEndTime - numStartTime).." seconds.\n\n"
strMessage = strMessage.."Please check that all information has loaded as intended."
fhMessageBox(strMessage)
else
fhMessageBox("No fields were mapped. No data could be imported.")
end
end -- function ImportRecords()
--[[
@Function: CheckVersionInStore
@Author: Mike Tate
@Version: 1.3
@LastUpdated: 03 May 2022
@Description: Check plugin version against version in Plugin Store
@Parameter: Plugin name and version
@Returns: None
@Requires: lfs & luacom
@V1.3: Save and retrieve latest version in file;
@V1.2: Ensure the Plugin Data folder exists;
@V1.1: Monthly interval between checks; Report if Internet is inaccessible;
@V1.0: Initial version;
]]
function CheckVersionInStore(strPlugin,strVersion) -- Check if later Version available in Plugin Store
require "lfs"
require "luacom"
local function OpenFile(strFileName,strMode) -- Open File and return Handle
local fileHandle, strError = io.open(strFileName,strMode)
if fileHandle == nil then
error("\n Unable to open file in \""..strMode.."\" mode. \n "..strFileName.." \n "..strError.." \n")
end
return fileHandle
end -- local function OpenFile
local function SaveStringToFile(strString,strFileName) -- Save string to file
local fileHandle = OpenFile(strFileName,"w")
fileHandle:write(strString)
assert(fileHandle:close())
end -- local function SaveStringToFile
local function StrLoadFromFile(strFileName) -- Load string from file
local fileHandle = OpenFile(strFileName,"r")
local strContents = fileHandle:read("*all")
assert(fileHandle:close())
return strContents
end -- local function StrLoadFromFile
local function httpRequest(strRequest) -- Luacom http request protected by pcall() below
local http = luacom.CreateObject("winhttp.winhttprequest.5.1")
http:Open("GET",strRequest,false)
http:Send()
return http.Responsebody
end -- local function httpRequest
local function intVersion(strVersion) -- Convert version string to comparable integer
local intVersion = 0
local arrNumbers = {}
strVersion:gsub("(%d+)", function(strDigits) table.insert(arrNumbers,strDigits) end)
for i = 1, 5 do
intVersion = intVersion * 100 + tonumber(arrNumbers[i] or 0)
end
return intVersion
end -- local function intVersion
local strLatest = "0"
if strPlugin then
local strPath = fhGetContextInfo("CI_APP_DATA_FOLDER").."\\Plugin Data\\"
local strFile = strPath.."VersionInStore "..strPlugin..".dat"
local intTime = os.time() - 2600000 -- Time in seconds a month ago
local tblAttr, strError = lfs.attributes(strFile) -- Obtain file attributes
if not tblAttr or tblAttr.modification < intTime then -- File does not exist or was modified long ago
if lfs.attributes(strPath,"mode") ~= "directory" then
if not lfs.mkdir(strPath) then return end -- Ensure the Plugin Data folder exists
end
local strErrFile = strPath.."VersionInStoreInternetError.dat"
local strRequest ="http://www.family-historian.co.uk/lnk/checkpluginversion.php?name="..tostring(strPlugin)
local isOK, strReturn = pcall(httpRequest,strRequest)
if not isOK then -- Problem with Internet access
local intTime = os.time() - 36000 -- Time in seconds 10 hours ago
local tblAttr, strError = lfs.attributes(strErrFile) -- Obtain file attributes
if not tblAttr or tblAttr.modification < intTime then -- File does not exist or was modified long ago
fhMessageBox(strReturn.."\n The Internet appears to be inaccessible. ")
end
SaveStringToFile(strErrFile,strErrFile) -- Update file modified time
else
os.remove(strErrFile) -- Delete file if Internet is OK
if strReturn then
strLatest = strReturn:match("([%d%.]*),%d*") -- Version digits & dots then comma and Id digits
SaveStringToFile(strLatest,strFile) -- Update file modified time and save version
end
end
else
strLatest = StrLoadFromFile(strFile) -- Retrieve saved latest version
end
end
if intVersion(strLatest) > intVersion(strVersion or "0") then
fhMessageBox("Later Version "..strLatest.." of this Plugin is available from the Plugin Store.")
end
end -- function CheckVersionInStore
-- Main Code Section Starts Here --
CheckVersionInStore("Flexible CSV Importer",strVersion) -- Notify if later Version -- V1.7
PrepareGUI()
--[[
@Title: Flexible CSV Importer
@Type: Standard
@Author: Mike Tate
@Contributors: Shelley Crawford & Graham Ward
@Version: 1.9.4
@Keywords:
@LastUpdated: 07 Nov 2025
@Licence: This plugin is copyright (c) 2025 Mike Tate & contributors and is licensed under the MIT License which is hereby incorporated by reference (see https://pluginstore.family-historian.co.uk/fh-plugin-licence)
@Description: Reads a CSV file usually with one line per record and imports people, facts and simple relationships. Provides an interface to map columns to fields.
]]
local strVersion = "1.9.4" -- Update this whenever Version changes
--[[Change history
v1.9.4 Support multiple P._FLGS... record flags; Workaround in FindFact(...) for Date:Compare(Date) anomaly;
v1.9.3 Support P.NAME._USED variants;
v1.9.2 Support P.NAME.TYPE and P.StandardFact.TYPE fields; Support custom facts; Allow / date separators, e.g. 10/4/1900;
v1.9.1 Check Version in Store v1.3; Fix bug that prevented custom facts from adding Sources; Add option to treat unsynced integer REFN as Record Id if record has no Custom Id;
v1.9 Sync fact AGE field; Cater for Unicode in CSV file path;
v1.8 Cater for no date in SetDate(); Cater for UTF-8 characters using stringx library and utf8data.lua; Detect ANSI CSV file and convert to UTF8; Monthly Check Version in Store;
v1.7 Make fields with any prefix work as intended; Fix problems handling Names; Sync current and new data; Update Directions Step 3; Update More Information tab; Check Version in Store;
v1.6 FH V7 Lua 3.5 IUP 3.28 compatible; progbar 3.0;
v1.5 Better load CSV syntax checks, added CSV column Source Citations, fixed multi-column text, new Help button, and more.
v1.5 Added a user option to select exisiting source or create during import.
v1.4 Fixes a problem with import of note fields.
v1.3 Fixes a problem that sometimes occurs when creating families.
v1.2 Adds option to discard custom reference numbers and fixes some bugs.
v1.1 Now appends rather than overwrites information if a citation or note field is duplicated. The separator used when appending can be selected.
Also creates an additional field instead of overwriting if an individual is attributed the same field type on more than one line
(details other than the note field will be overwritten if duplicated within the same line).
]]
require("iuplua")
require("iupluacontrols") -- for the tabs
if fhGetAppVersion() > 5 then -- Cater for Unicode UTF-8 from FH Version 6 onwards
fhSetStringEncoding("UTF-8")
iup.SetGlobal("UTF8MODE","YES")
iup.SetGlobal("UTF8MODE_FILE","YES") -- V1.9
iup.SetGlobal("CUSTOMQUITMESSAGE","YES") -- IUP 3.28 -- V1.6
end
require("luacom") -- To create File System Object -- V1.9
FSO = luacom.CreateObject("Scripting.FileSystemObject")
local strFieldNaming = [[
This plugin first assumes that column headings follow the conventions set out below.
If a valid data reference is not found, the plugin will attempt to construct one.
Simple terms like "DoB" or "Spouse's father" should be interpreted correctly.
Standard facts are imported by using their GEDCOM field tags, e.g. NAME, BIRT, MARR, DEAT, CENS.
Custom facts can be imported by entering the appropriate GEDCOM tag.
They are supplemented with detail tags, e.g. DATE, PLAC, AGE, NOTE.
Unique references (using the REFN tag) can be used to identify the same individual in several lines of data.
e.g. In birth record transcipts to identify the same father and mother across birth records for each of their children.
Most conventional column headings have the format RECORD.FIELD.DETAIL except for SPECIAL CASES shown below.
RECORD identifies the Individual or Family record:
P = Primary - the primary person of interest in the record
PF = Primary's father, PM = Primary's mother
S = Spouse - of the primary person
SF = Spouse's father, SM = Spouse's mother
Family identifiers are the two individuals separated by an underscore:
P_S = the primary person and their spouse
PF_PM = parent family of primary person
SF_SM = parent family of spouse
FIELD is the GEDCOM tag or label for the fact, e.g. REFN, BIRT, DEAT, CENS, NAME or Birth, Death, Census, Name
DETAIL is a GEDCOM tag or label for fact details, e.g. DATE, PLAC, ADDR, NOTE or When, Place, Address, Note
For example, the primary person's birth place is: P.BIRT.PLAC
Marriage date of primary person's parents is: PF_PM.Married.When
Parent and spouse relationships use PF.REFN, PM.REFN and S.REFN
SPECIAL CASES:
NAME fields are assumed to be full names, i.e. Given Names Surname,
unless followed by a name part: GIVEN, SURNAME or SURNAME_FIRST, e.g. P.NAME:GIVEN
Age at the time of a family event (e.g. Marriage) is indicated by individual's identifier, e.g. P_S.Marriage.Age:P
Separate source citation details can be added for each line.
Column headings for citation elements are of the form:
SOUR>TITL = Source Title
SOUR.DATE = Entry Date
SOUR.QUAY = Assessment ~ 1, 2, 3, 4 or P, S, Q, U
SOUR.PAGE = Where within Source
SOUR.TEXT = Text From Source
SOUR.NOTE = Citation Note
Without a prefix as above they are added to every new item.
With a RECORD prefix they add a whole record citation.
With a RECORD.FIELD prefix they add Name and Fact citations.
]]
---------------------------------
-- UPVALUE VARIABLES -- More visible than with globals needing Debug > Options > Dislay Global Types > Table
---------------------------------
local strImportFile = "" -- Import CSV filename
local strSourceTitle = "" -- Global Source Title
local arrRows = {} -- Import CSV file rows by Row number and Header Title
local tblMap = {} -- Mapping of CSV by Tags to Title, and Col number to Valid=false/Tag; Title=Header; Label=Header-Parts[1-4]; Field=Tags[0-4]; Refer=Data-Ref-Tags;
local tblPeople = {} -- Individual record pointers by CSV Row number and Header Ident (P,S,PF,PM,SF,SM)
local tblFamilies = {} -- The Family record pointers by CSV Row number and Header Ident (P_S,PF_PM,SF_SM)
local tblRefn = {} -- Custom ID refs to Individual record pointers
local tblLists = {} -- Lookup tables by Record; Field2; Field3; Source; Names;
local dicSources = {} -- Source Title exists by Tags during Field Interpretaion; Source record pointers by Title during Import Data
local tblRel = {} -- Relationships by Header Ident (P,S,PF,PM,SF,SM;P_S,PF_PM,SF_SM); SOUR; EXCL;
local tblSet = {} -- IUP GUI settings
local dicBox = {} -- IUP GUI gridbox cells
local strRed = "255 0 0" -- IUP GUI colours
local strGreen = "0 128 0"
local strBlue = "0 0 255"
local strNavy = "0 0 128"
local strBlack = "0 0 0"
local strWhite = "255 255 255"
---------------------------------
-- GLOBAL FUNCTIONS
---------------------------------
--[[
@Module: +fh+stringx_v3
@Author: Mike Tate
@Version: 3.0
@LastUpdated: 19 Sep 2020
@Description: Extended string functions to supplement LUA string library.
@V3.0: Function Prototype Closure version with Lua 5.1 & 5.3 comaptibility; Added inert(strTxt) function;
@V2.5: Support FH V6 Encoding = UTF-8;
@V2.4: Tolerant of integer & nil parameters just link match & gsub;
@V1.0: Initial version.
]]
local function stringx_v3()
local fh = {} -- Local environment table
-- Supply current file encoding format --
function fh.encoding()
if fhGetAppVersion() > 5 then return fhGetStringEncoding() end
return "ANSI"
end -- function encoding
-- Split a string using "," or chosen separator --
function fh.split(strTxt,strSep)
local tblFields = {}
local strPattern = string.format("([^%s]+)", strSep or ",")
strTxt = tostring(strTxt or "")
strTxt:gsub(strPattern, function(strField) tblFields[#tblFields+1] = strField end)
return tblFields
end -- function split
-- Split a string into numbers using " " or "," or "x" separators -- Any non-number remains as a string
function fh.splitnumbers(strTxt)
local tblNum = {}
strTxt = tostring(strTxt or "")
strTxt:gsub("([^ ,x]+)", function(strNum) tblNum[#tblNum+1] = tonumber(strNum) or strNum end)
return tblNum
end -- function splitnumbers
local strMagic = "([%^%$%(%)%%%.%[%]%*%+%-%?])" -- UTF-8 replacement for "(%W)"
-- Hide magic pattern symbols ^ $ ( ) % . [ ] * + - ?
function fh.plain(strTxt)
-- Prefix every magic pattern character with a % escape character,
-- where %% is the % escape, and %1 is the original character capture.
strTxt = tostring(strTxt or ""):gsub(strMagic,"%%%1")
return strTxt
end -- function plain
-- matches is plain text version of string.match()
function fh.matches(strTxt,strFind,intInit)
strFind = tostring(strFind or ""):gsub(strMagic,"%%%1") -- Hide magic pattern symbols
return tostring(strTxt or ""):match(strFind,tonumber(intInit))
end -- function matches
-- replace is plain text version of string.gsub()
function fh.replace(strTxt,strOld,strNew,intNum)
strOld = tostring(strOld or ""):gsub(strMagic,"%%%1") -- Hide magic pattern symbols
return tostring(strTxt or ""):gsub(strOld,function() return strNew end,tonumber(intNum)) -- Hide % capture symbols
end -- function replace
-- Hide % escape/capture symbols in replacement so they are inert
function fh.inert(strTxt)
strTxt = tostring(strTxt or ""):gsub("%%","%%%%") -- Hide all % symbols
return strTxt
end -- function inert
-- convert is pattern without captures version of string.gsub()
function fh.convert(strTxt,strOld,strNew,intNum)
return tostring(strTxt or ""):gsub(tostring(strOld or ""),function() return strNew end,tonumber(intNum)) -- Hide % capture symbols
end -- function convert
local dicUpper = { }
local dicLower = { }
local dicCaseX = { }
-- ASCII unaccented letter translations for Upper, Lower, and Case Insensitive
for intUpper = string.byte("A"), string.byte("Z") do
local strUpper = string.char(intUpper)
local strLower = string.char(intUpper - string.byte("A") + string.byte("a"))
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
local strCaseX = "["..strUpper..strLower.."]"
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
-- Supply character length of ANSI text --
function fh.length(strTxt)
return string.len(strTxt or "")
end -- function length
-- Supply character substring of ANSI text --
function fh.substring(strTxt,i,j)
return string.sub(strTxt or "",i,j)
end -- function substring
-- Translate upper/lower case ANSI letters to pattern that matches both --
function fh.caseless(strTxt)
strTxt = tostring(strTxt or ""):gsub("[A-Za-z]",dicCaseX)
return strTxt
end -- function caseless
if fh.encoding() == "UTF-8" then
-- Supply character length of UTF-8 text --
function fh.length(strTxt)
isFlag = fhIsConversionLossFlagSet()
strTxt = fhConvertUTF8toANSI(strTxt or "")
fhSetConversionLossFlag(isFlag)
return string.len(strTxt)
end -- function length
local strUTF8 = "([%z\1-\127\194-\244][\128-\191]*)" -- Cater for Lua 5.1 %z or Lua 5.3 \0
if fhGetAppVersion() > 6 then
strUTF8 = "([\0-\127\194-\244][\128-\191]*)"
end
-- Supply character substring of UTF-8 text --
function fh.substring(strTxt,i,j)
local strSub = ""
j = j or -1
if j < 0 then j = j + length(strTxt) + 1 end
if i < 0 then i = i + length(strTxt) + 1 end
for strChr in string.gmatch(strTxt or "",strUTF8) do
if j <= 0 then break end
j = j - 1
i = i - 1
if i <= 0 then strSub = strSub..strChr end
end
return strSub
end -- function substring
pcall(require,"utf8data") -- Cater for utf8data.lua optional mapping --
-- Translate lower case to upper case UTF-8 letters -- -- Cater for utf8data.lua optional mapping --
function fh.upper(strTxt)
strTxt = tostring(strTxt or ""):gsub("([a-z\194-\244][\128-\191]*)",(utf8_lc_uc or dicUpper))
return strTxt
end -- function upper
-- Translate upper case to lower case UTF-8 letters -- -- Cater for utf8data.lua optional mapping --
function fh.lower(strTxt)
strTxt = tostring(strTxt or ""):gsub("([A-Z\194-\244][\128-\191]*)",(utf8_uc_lc or dicLower))
return strTxt
end -- function lower
-- Translate upper/lower case UTF-8 letters to pattern that matches both --
function fh.caseless(strTxt)
strTxt = tostring(strTxt or ""):gsub("([A-Za-z\194-\244][\128-\191]*)",dicCaseX)
return strTxt
end -- function caseless
-- Following tables use ASCII numeric coding to be immune from ANSI/UTF-8 encoding --
local arrPairs = -- Upper & Lower case groups of UTF-8 letters with same prefix --
{-- { Prefix; Beg ; End ; Inc; Offset Upper > Lower }; -- These include all ANSI letters and many more
{ "\195"; 0x80; 0x96; 1 ; 32 }; -- 195=0xC3 À U+00C0 to Ö U+00D6 and à U+00E0 to ö U+00F6
{ "\195"; 0x98; 0x9E; 1 ; 32 }; -- 195=0xC3 Ø U+00D8 to Þ U+00DE and ø U+00F8 to þ U+00FE
{ "\196"; 0x80; 0xB6; 2 ; 1 }; -- 196=0xC4 A U+0100 to k U+0137 in pairs
{ "\196"; 0xB9; 0xBD; 2 ; 1 }; -- 196=0xC4 L U+0139 to l U+013E in pairs
{ "\197"; 0x81; 0x87; 2 ; 1 }; -- 197=0xC5 L U+0141 to n U+0148 in pairs
{ "\197"; 0x8A; 0xB6; 2 ; 1 }; -- 197=0xC5 ? U+014A to y U+0177 in pairs
{ "\197"; 0xB9; 0xBD; 2 ; 1 }; -- 197=0xC5 Z U+0179 to ž U+017E in pairs
{ "\198"; 0x82; 0x84; 2 ; 1 }; -- 198=0xC6 ? U+0182 to ? U+0185 in pairs
-- Add more Unicode groups here as usage increases --
}
local dicPairs = -- Upper v Lower case UTF-8 letters that don't fit groups above --
{ [string.char(0xC4,0xBF)] = string.char(0xC5,0x80); -- ? U+013F and ? U+0140
[string.char(0xC5,0xB8)] = string.char(0xC3,0xBF); -- Ÿ U+0178 and ÿ U+00FF
}
local intBeg1 = string.byte(string.sub("À",1))
local intBeg2 = string.byte(string.sub("À",2))
local intEnd1 = string.byte(string.sub("Z",1))
local intEnd2 = string.byte(string.sub("Z",2))
-- print(string.format("%#x %#x %#x %#x",intBeg1,intBeg2,intEnd1,intEnd2)) -- Useful to work out numeric coding
-- Populate the UTF-8 letter translation dictionaries --
for intGroup, tblGroup in ipairs ( arrPairs ) do -- UTF-8 accented letter groups
local strPrefix = tblGroup[1]
for intUpper = tblGroup[2], tblGroup[3], tblGroup[4] do
local strUpper = string.char(intUpper)
local strLower = string.char(intUpper + tblGroup[5])
local strCaseX = strPrefix.."["..strUpper..strLower.."]"
strUpper = strPrefix..strUpper
strLower = strPrefix..strLower
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
end
for strUpper, strLower in pairs ( dicPairs ) do -- UTF-8 accented letters where upper & lower have different prefix
dicUpper[strLower] = strUpper
dicLower[strUpper] = strLower
local strCaseX = ""
for intByte = 1, #strUpper do -- Matches more than just the two letters, but can't do any better
strCaseX = strCaseX.."["..strUpper:sub(intByte,intByte)..strLower:sub(intByte,intByte).."]"
end
dicCaseX[strLower] = strCaseX
dicCaseX[strUpper] = strCaseX
end
end -- fh.encoding() == "UTF-8"
-- overload fh functions into string table
for strIndex, anyValue in pairs(fh) do
if type(anyValue) == "function" then
string[strIndex] = anyValue
end
end
return fh
end -- local function stringx_v3
local stringx = stringx_v3() -- To access FH string extension module
-- Convert filename to ANSI alternative and indicate success -- -- V1.9
function FileNameToANSI(strFileName,strAnsiName)
-- strFileName ~ full file path
-- strAnsiFile ~ ANSI file name & type
-- return values ~ ANSI file path, true if original path was ANSI compatible
if stringx.encoding() == "ANSI" then return strFileName, true end
local isFlag = fhIsConversionLossFlagSet()
fhSetConversionLossFlag(false)
local strAnsi = fhConvertUTF8toANSI(strFileName)
local wasAnsi = true
if fhIsConversionLossFlagSet() then
strAnsiName = strAnsiName or "ANSI.ANSI"
strAnsi = fhGetContextInfo("CI_APP_DATA_FOLDER").."\\Plugin Data\\"..strAnsiName
wasAnsi = false
end
fhSetConversionLossFlag(isFlag)
return strAnsi, wasAnsi
end -- local function FileNameToANSI
--[[GetExistingFile
@name GetExistingFile
@param strTitle Prompt Title
@param strFilter Filter template e.g *.CSV;*.TXT
@param strFilterInfo Display Text for the Selection
@param strDir Directory folder
]]
function GetExistingFile(strTitle,strFilter,strFilterInfo,strDir)
-- Creates a file dialog and sets its type, title, filter and filter info
local filedlg = iup.filedlg{ dialogtype = "OPEN"; title = strTitle; filter = strFilter; filterinfo = strFilterInfo; directory = strDir; }
-- Shows file dialog in the center of the screen
filedlg:popup(iup.ANYWHERE,iup.ANYWHERE)
-- Gets file dialog status
local status = filedlg.status
return status,filedlg.value
end -- function GetExistingFile
--[[table.loadcsv
@name table.loadcsv
@description Loads a CSV file into a table of tables, the first line should contain descriptions
@usage provide valid filename, use ipairs loop to use resulting table
@param filename filename to load
@param bHasHeader true if the file contains a header line, false if not, defaults to false
If true
@return table of tables, if a header is used, indexed by the fieldnames from the header
@return table containing the header field names (if used)
]]
function table.loadcsv(filename,bHasHeader)
bHasHeader = bHasHeader or false
local bHeader = false
local header = {}
local contents = {}
local function report(what,where) -- MBT
local row = #contents + 1
if where then where = "\nnear: "..where else where = "" end
fhMessageBox("\nCSV Data File Syntax Error\n\n"..what.." on row "..row..where.."\n","MB_OK","MB_ICONEXCLAMATION")
end
local function fromCSV (s)
s = s .. ',' -- ending comma
local t = {} -- table to collect fields
local fieldstart = 1
repeat
-- next field is quoted? (start with '"'?)
if string.find(s, '^"', fieldstart) then
local a, c
local i = fieldstart
repeat
-- find closing quote
a, i, c = string.find(s, '"("?)', i+1)
until c ~= '"' -- quote not followed by quote?
if not i then
report('Unmatched trailing " ',string.sub(s, fieldstart-3,#s-1))
i = #s
end
local f = string.sub(s, fieldstart+1, i-1)
table.insert(t, (string.gsub(f, '""', '"')))
fieldstart = string.find(s, ',', i) + 1
local x = string.sub(s, i+1, fieldstart-3)
if string.find(x, "[^%s]") then
report('Unmatched opening " ',string.sub(s, i-20, fieldstart))
end
else -- unquoted; find next comma
local nexti = string.find(s, ',', fieldstart)
local x = string.sub(s, fieldstart, nexti-1)
if string.find(x, '"%s?$') then
report('Unmatched closing " ',string.sub(s, fieldstart-2, nexti))
end
table.insert(t, string.sub(s, fieldstart, nexti-1))
fieldstart = nexti + 1
end
until fieldstart > string.len(s)
return t
end
local first
for line in io.lines(filename) do
if line:match("[\128-\255]") and #line == string.length(line) -- Detect ANSI encoded file and convert to UTF8 -- V1.8
and fhGetAppVersion() > 5 then
line = fhConvertANSItoUTF8(line)
end
local fields = fromCSV(line)
local data = {}
if bHasHeader then
if bHeader then
-- Build Data Table with the Header Descriptions
for i,field in ipairs(fields) do
if field ~= '' then
local head = header[i] or " " -- V1.7
if data[head] then head = head.." " end -- avoid duplicate header
data[head] = field
end
end
if #fields ~= #header then
report(#fields.." data cols differ from "..#header.." header cols")
end
table.insert(contents,data)
else
-- Grab Column Names
header = fields
bHeader = true
end
else
first = first or #fields
if #fields ~= first then
report("First row "..first.." cols differ from "..#fields.." cols")
end
table.insert(contents,fields)
end
end
return contents,header
end -- function table.loadcsv
--[[SplitTitle
@name SplitTitle
@description Splits one column title into a table of the labels in use
and their interpretation as Family Historian field tags.
@param strTitle the column title
@return tblMap table of title, labels and fields
]]
function SplitTitle(strTitle) -- MBT -- Rewritten
local arrLabel = strTitle:upper():split(".:>")
local arrField = {}
for i = 1, 5 do
if arrLabel[i] then arrField[i] = arrLabel[i] end
if i <= 3 then arrLabel[i] = arrLabel[i] or "" end
end
arrField[0] = arrLabel[1]
local tblMap = { Valid=false; Title=strTitle; Label=arrLabel; Field=arrField; Refer="";}
return tblMap
end -- function SplitTitle
--[[IsSource
@name IsSource
@description check if field is valid source citation label
@param strField the field
@return true/false
]]
function IsSource(strField) -- MBT
return strField and ( strField:match("SOURC?E?") or strField:match("CIT[EA]") )
end -- function IsSource
--[[TestFields
@name TestFields
@description Inspects the field interpretations and checks if they produce valid Family Historian data reference.
Makes some corrections to fields where possible. Adds a true/false flag for the field interpretation validity.
@param intCol The column number of the titles table
]]
function TestFields(intCol) -- MBT -- Largely rewritten
-- look for INDI and FAM fact types
local arrLabel = tblMap[intCol].Label
local arrField = tblMap[intCol].Field
local strRefer = ""
local dicCodes = { TITLE="TITL"; TITL="TITL"; QUAY="QUAY"; DATE="DATA~DATE"; PAGE="PAGE"; TEXT="DATA~TEXT"; NOTE="NOTE2"; NOTE2="NOTE2"; }
local function ValidateSource(strType,intField)
local isValid = false
local strField = arrField[intField]
local strCodes = dicCodes[strField]
if strCodes then
if strCodes == "TITL" then dicSources[strType] = true end -- Ensure TITL precedes other Citation columns
if dicSources[strType] then isValid = "SOUR~"..strCodes end
strField = strCodes:match("[^~]+$")
end
return isValid, strField
end -- local function ValidateSource
local isValid = false
if arrField[1] then
local strTitle = tblMap[intCol].Title
if strTitle:match("^REF") or strTitle:match("^ID") then -- V1.7
arrLabel = { "P" ; "REFN"; }
arrField = { "INDI"; "REFN"; [0]="P"; }
end
local dicRel = tblRel[arrField[0]]
if dicRel then
if dicRel.RecTag == "INDI" and ( arrField[2] == "SEX" or arrField[2] == "REFN" or arrField[2] == "NOTE2" or arrField[2] == "_FLGS" ) then -- V1.9.4
-- "misc" indi fields
arrField[1] = "INDI"
if arrField[2] ~= "_FLGS" then arrField[3] = nil end -- V1.9.4
arrField[4] = nil
strRefer = table.concat(arrField,".")
isValid = "INDI" -- true
elseif dicRel.RecTag ~= "SOUR" then
-- anything other than citations
arrField[1] = dicRel.RecTag
if not arrField[2] then
arrLabel[3] = nil
arrField[3] = nil
isValid = false
elseif IsSource(arrField[2]) then
arrField[2] = "SOUR"
isValid, arrField[3] = ValidateSource(arrField[1],3)
strRefer = table.concat(arrField,".")
else
-- fix easy typo
local dicSubfield = { PLACE="PLAC"; NOTE="NOTE2"; }
arrField[3] = dicSubfield[arrField[3]] or arrField[3]
strRefer = table.concat(arrField,".")
strRefer = strRefer:gsub("%.NAME%.?",".NAME:"):gsub(":SOUR",".SOUR"):gsub(":NOTE2",".NOTE2"):gsub(":TYPE",".TYPE"):gsub("[:%.]_?USED","._USED") -- V1.9.2 -- V1.9.3
strRefer = strRefer:gsub("%.SOUR%.TITL",".SOUR>TITL")
strRefer = strRefer:gsub("%.SOUR%.DATE$",".SOUR.DATA.DATE"):gsub("%.SOUR%.TEXT$",".SOUR.DATA.TEXT")
strRefer = strRefer:gsub("%.AGE%.%w+",".AGE")
if fhIsValidDataRef("%"..strRefer.."%") then -- checks it's valid
-- valid field
isValid = arrField[1]
if IsSource(arrField[3]) then
arrField[3] = "SOUR"
isValid, arrField[4] = ValidateSource(arrField[1]..arrField[2],4)
elseif arrField[1] == "FAM" and arrField[3] == "AGE" then
if not tblRel[arrField[4]] or tblRel[arrField[4]].RecTag ~= "INDI" then -- need a reference in arrField[4] to which individual's age
isValid = false
end
elseif arrField[3] == "USED" then -- V1.9.3
arrField[3] = "_USED"
end
local strFactType = ListItemLookup(tblLists.Field2,"FactType",arrField[2])
if strFactType == "Event" and not arrField[3] then isValid = false end
else
if arrField[1] == "INDI" then
-- intended FAM field assigned to an INDI so change to FAM
strRefer = strRefer:gsub("^INDI","FAM")
if fhIsValidDataRef("%"..strRefer.."%") then -- checks it's valid
arrField[1] = "FAM"
if arrField[3] == "AGE" then arrField[4] = arrField[0] end
arrField[0] = dicRel.Fams -- selects the correct family record type for that individual from tblRel
isValid = "FAM"
end
end
end
end
end
end
if IsSource(arrField[1]) then
isValid, arrField[2] = ValidateSource("SOUR",2)
arrField[0] = "SOUR"
arrField[1] = "SOUR"
arrField[3] = nil
arrField[4] = nil
strRefer = table.concat(arrField,".")
end
end
if isValid then
if strRefer:match("^INDI%.NAME:") or strRefer:match("^INDI%.REFN") then
local strRef = strRefer:gsub("^INDI",arrField[0])
if tblMap[strRef] then
isValid = false
else
local strTitle = tblMap[intCol].Title
tblMap[strRef] = strTitle
if strRef:match(arrField[0]..".NAME:%u+") then -- Ensure NAME: mapping is removed when Field3 is defined -- V1.7
tblMap[arrField[0]..".NAME:"] = nil
end
end
elseif arrField[2] ~= "SOUR" and arrField[2] ~= "_FLGS" and arrField[3] and arrField[3] ~= "ADDR" and arrField[3] ~= "NOTE2" and arrField[3] ~= "SOUR" then -- V1.9.4
local strFld = arrField[2]
local strRef = arrField[0].."."..arrField[2]
tblMap[strRef] = tblMap[strRef] or {} -- Mapping for synchronising facts in FindFact() -- V1.7
table.insert(tblMap[strRef],intCol)
if #strFld > 4 then -- Handle any _ATTR- or EVEN- custom facts -- V1.9.2
if not tblLists[strFld] then
local strType = "Event"
if strFld:match("^_ATTR") then strType = "Attribute" end
table.insert(tblLists.Field2,{ Label = strFld; Tag = strFld; FactType = strType; RecTag = arrField[1]; })
tblLists[strFld] = true
end
if arrField[3] == "TYPE" then -- TYPE invalid for _ATTR- and EVEN- custom facts -- V1.9.2
isValid = false
end
end
end
end
tblMap[intCol].Valid = isValid
tblMap[intCol].Label = arrLabel
tblMap[intCol].Field = arrField
tblMap[intCol].Refer = strRefer
end -- function TestFields
--[[MarkValid
@name MarkValid
@description Colour codes column titles in the GUI according to field validity
@param intCol The column number of the titles table to mark
]]
function MarkValid(intCol)
-- colours the titles in the gridbox red or green, depending on if the field is valid
local strColor = strRed
if tblMap[intCol].Valid then strColor = strGreen end
dicBox[tblMap[intCol].Title].Header.FgColor = strColor
end -- function MarkValid
--[[TitlesSecondChance
@name TitlesSecondChance
@description Automagically interprets fields based on words found
@param intCol The title index column number
]]
function TitlesSecondChance(intCol) -- MBT -- rewritten
local arrLabel = tblMap[intCol].Label
local arrField = tblMap[intCol].Field
local strTitle = tblMap[intCol].Title:upper()
local wasFound = false
local arrSource = {
{ Label="TITLE"; Tag="TITL"; };
{ Label="ASSESS"; Tag="QUAY"; };
{ Label="QUAL"; Tag="QUAY"; };
{ Label="DATE"; Tag="DATE"; };
{ Label="ENTRY"; Tag="DATE"; };
{ Label="YEAR"; Tag="DATE"; };
{ Label="WHEN"; Tag="DATE"; };
{ Label="WHERE"; Tag="PAGE"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
{ Label="TRANS"; Tag="TEXT"; };
{ Label="TEXT"; Tag="TEXT"; };
}
local function doFind(arrFind,intData,strLook) -- search data array for matching lookup text
local isFound = false
strLook = strLook or arrLabel[intData] or arrField[intData] or ""
strLook = strLook:upper()
for _, dicFind in ipairs ( arrFind ) do
for _, strFind in ipairs ( { "Label"; "Tag"; "Verb"; } ) do
if dicFind[strFind] then
if strLook:find( dicFind[strFind]:upper() ) then
arrField[intData] = dicFind.Tag or dicFind.Ident
if dicFind.RecTag then
arrField[1] = dicFind.RecTag
end
isFound = true
break
end
end
end
if isFound then break end
end
return isFound
end -- local function doFind
-- Record
if doFind( { { Label="CIT[AE]"; }; { Label="SOUR"; }; }, 0 ) then
arrField[0] = "SOUR"
arrField[1] = "SOUR"
doFind( arrSource, 2 )
else
if not arrField[0] or not tblRel[arrField[0]] then
local arrPrimary = { { Label="FATHER"; Ident="PF"; }; { Label="MOTHER"; Ident="PM"; }; }
local arrSpouse = { { Label="FATHER"; Ident="SF"; }; { Label="MOTHER"; Ident="SM"; }; }
arrField[0] = "P"
arrField[1] = "INDI"
doFind( arrPrimary, 0, strTitle )
if doFind( { { Label="GROOM"; Ident="P"; }; }, 0, strTitle ) then
tblRel.P.Sex = "M" tblRel.S.Sex = "F"
doFind( arrPrimary, 0, strTitle )
elseif doFind( { { Label="BRIDE"; Ident="S"; }; }, 0, strTitle ) then
tblRel.S.Sex = "F" tblRel.P.Sex = "M"
doFind( arrSpouse, 0, strTitle )
elseif doFind( { { Label="SPOUSE"; Ident="S"; }; }, 0, strTitle ) then
doFind( arrSpouse, 0, strTitle )
end
end
-- Name
local arrNames = {
{ Label="SURNAME"; Tag="NAME"; };
{ Label="FAMILY"; Tag="NAME"; };
{ Label="GIVEN"; Tag="NAME"; };
{ Label="FORE"; Tag="NAME"; };
{ Label="_USED"; Tag="NAME"; }; -- V1.9.3
{ Label="NAME"; Tag="NAME"; };
}
if doFind( arrNames, 2, strTitle ) then
local arrNamed = {
{ Label="%u+.FIRST";Tag="SURNAME_FIRST"; };
{ Label="SURNAME"; Tag="SURNAME"; };
{ Label="FAMILY"; Tag="SURNAME"; };
{ Label="GIVEN"; Tag="GIVEN"; };
{ Label="FIRST"; Tag="GIVEN"; };
{ Label="FORE"; Tag="GIVEN"; };
{ Label="_USED"; Tag="_USED"; }; -- V1.9.3
{ Label="CIT[AE]"; Tag="SOUR"; };
{ Label="SOUR"; Tag="SOUR"; };
{ Label="COMM"; Tag="NOTE2"; };
{ Label="NOTE"; Tag="NOTE2"; };
}
if doFind( arrNamed, 3 ) then
wasFound = true
end
end
-- Field2
if not wasFound then
if not doFind( tblLists.Field2, 2 ) then
local arrOther = { -- try special terms
{ Label="DOB"; Tag="BIRT"; };
{ Label="POB"; Tag="BIRT"; };
{ Label="DOD"; Tag="DEAT"; };
{ Label="POD"; Tag="DEAT"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
}
if doFind( arrOther, 2 ) then
if arrLabel[2]:match("DO[BD]") then arrField[3] = "DATE" end
if arrLabel[2]:match("PO[BD]") then arrField[3] = "PLAC" end
wasFound = true
end
end
end
-- Field3
if not wasFound then
if IsSource(arrField[2]) then
arrField[2] = "SOUR"
doFind( arrSource, 3 )
else
local arrField3 = {
{ Label="PLACE"; Tag="PLAC"; };
{ Label="WHERE"; Tag="PLAC"; };
{ Label="DATE"; Tag="DATE"; };
{ Label="WHEN"; Tag="DATE"; };
{ Label="YEAR"; Tag="DATE"; };
{ Label="ADDR"; Tag="ADDR"; };
{ Label="CAUSE"; Tag="CAUS"; };
{ Label="COMM"; Tag="NOTE2";};
{ Label="NOTE"; Tag="NOTE2";};
{ Label="TYPE"; Tag="TYPE"; }; -- V1.9.2
{ Label="AGE"; Tag="AGE"; };
}
doFind( arrField3, 3 )
if arrField[3] == "AGE" then
doFind( { { Label="PRIM"; Ident="P"; }; { Label="SPOU"; Ident="S"; }; }, 4 )
end
end
end
if IsSource(arrField[3]) then
arrField[3] = "SOUR"
doFind( arrSource, 4 )
end
end
end -- function TitlesSecondChance
--[[CreatePeople
@name CreatePeople
@description Creates people for one line of the CSV file, and adds them to tblPeople
Increments the person count intPeople for each person created
@param intRow The csv line number
@param intPeople Count of Individual records
@return intPeople
]]
function CreatePeople(intRow,intPeople)
tblPeople[intRow] = tblPeople[intRow] or {}
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid == "INDI" and dicMap.Field[2] == "REFN" then
local strValue = arrRows[intRow][dicMap.Title]
if strValue then
local ptrInd = fhNewItemPtr()
if tblRefn[strValue] then -- reference number logged, so use it
ptrInd:MoveTo(tblRefn[strValue])
else
ptrInd:MoveToRecordById("INDI",tonumber(strValue) or 0) -- find person with matching Record Id -- V1.9.1
if tblSet.UseRecId == "Yes"
and ptrInd:IsNotNull()
and fhGetItemPtr(ptrInd,"~.REFN"):IsNull() then -- unsynced integer REFN so use that person -- V1.9.1
-- Use existing person with matching Rec Id
else
ptrInd = fhCreateItem("INDI") -- ...or create then log
intPeople = intPeople + 1
end
tblRefn[strValue] = ptrInd:Clone()
if tblSet.RetainID == "Yes" then
fhSetValueAsText(fhCreateItem("REFN",ptrInd),strValue)
end
end
local strIdent = dicMap.Field[0]
if strIdent == "P" then AddSources(ptrInd,intRow,"INDI") end
tblPeople[intRow][strIdent] = ptrInd:Clone() -- log in the person table
end
end
end
return intPeople
end -- function CreatePeople
--[[CreateFamilies
@name CreateFamilies
@description creates family records that link the individuals together. Adds the family pointer to tblFamilies.
@usage best run after individuals have had genders assigned
@param intRow Line number being used
@param intFamilies Count of Family records
@return intFamilies
]]
function CreateFamilies(intRow,intFamilies)
local ptrFam = fhNewItemPtr()
local dicRow = arrRows[intRow]
local dicPerson = tblPeople[intRow] or {}
tblFamilies[intRow] = tblFamilies[intRow] or {}
for strIdent, tblFam in pairs (tblRel) do -- reference table
if tblFam.RecTag == "FAM" then -- family definitions
local ptrSpouse1 = dicPerson[tblFam.Spouse1]
local ptrSpouse2 = dicPerson[tblFam.Spouse2]
local ptrChild = dicPerson[tblFam.Child]
local bSetSpouses = false -- test if there's already a family for the couple
if (ptrSpouse1 and ptrSpouse2) or ((ptrSpouse1 or ptrSpouse2) and ptrChild) then -- need to make a family
local strSpouse1 = dicRow[tblMap[tblFam.Spouse1..".REFN"]]
local strSpouse2 = dicRow[tblMap[tblFam.Spouse2..".REFN"]]
if tblRefn[strSpouse1] and tblRefn[strSpouse2] then -- both spouses have custom reference numbers
if tblRefn[strSpouse1.."&"..strSpouse2] then
ptrFam = tblRefn[strSpouse1.."&"..strSpouse2]
else
ptrFam = fhCreateItem("FAM") -- now create the family record
intFamilies = intFamilies +1
tblRefn[strSpouse1.."&"..strSpouse2] = ptrFam:Clone()
tblRefn[strSpouse2.."&"..strSpouse1] = ptrFam:Clone()
bSetSpouses = true
AddSources(ptrFam,intRow,"FAM")
end
else
ptrFam = fhCreateItem("FAM") -- now create the family record
intFamilies = intFamilies +1
bSetSpouses = true
AddSources(ptrFam,intRow,"FAM")
end
if bSetSpouses == true then -- create spouse links if family doesn't already have them
local strItemS1 = "HUSB" -- default if no other info is spouse1 male
local strItemS2 = "WIFE"
if ptrSpouse1 then -- determine sex for spouse1 and link -- V1.7
if fhGetItemText(ptrSpouse1,'~.SEX') ~= "Male" then
if fhGetItemText(ptrSpouse1,'~.SEX') == "Female" then
strItemS1 = "WIFE"
elseif ptrSpouse2 then -- sex of spouse 1 unknown, check other spouse
if fhGetItemText(ptrSpouse2,'~.SEX') == "Male" then
strItemS1 = "WIFE"
end
end
end
local ptrLink = fhCreateItem(strItemS1, ptrFam )
fhSetValueAsLink(ptrLink, ptrSpouse1)
end
if ptrSpouse2 then -- determine sex for spouse2 and link -- V1.7
if fhGetItemText(ptrSpouse2,'~.SEX') ~= "Female" then
if fhGetItemText(ptrSpouse2,'~.SEX') == "Male" then
strItemS2 = "HUSB"
elseif ptrSpouse1 then -- sex of spouse 1 unknown, check other spouse
if fhGetItemText(ptrSpouse1,'~.SEX') == "Female" then
strItemS2 = "HUSB"
end
end
end
local ptrLink = fhCreateItem(strItemS2, ptrFam )
fhSetValueAsLink(ptrLink, ptrSpouse2)
end
end
if ptrChild then -- create a CHILD field in the family record
local ptrLink = fhCreateItem("CHIL", ptrFam )
fhSetValueAsLink(ptrLink, ptrChild)
end
tblFamilies[intRow][strIdent] = ptrFam:Clone() -- add to tblFamilies
end -- if need to make a family
end
end
return intFamilies
end -- function CreateFamilies
--[[AddItems
@name AddItems
@description Adds facts to the identifier type given
@param intRow The line number being processed
@param strType "INDI" or "FAM"
@param tblType input table of pointers
]]
function AddItems(intRow, strType, tblType) -- e.g. AddItems(intRow, "INDI", tblPeople) or AddItems(intRow, "FAM", tblFamilies)
local tblFld = {}
local dicRow = arrRows[intRow] -- get the row of data values
local tblType = tblType[intRow] -- get all the record pointers
for intMap, dicMap in ipairs (tblMap) do
local strTitle = dicMap.Title
local strRefer = dicMap.Refer
local arrField = dicMap.Field
local strField0 = arrField[0]
local strField2 = arrField[2]
local strField3 = arrField[3]
if dicMap.Valid == strType then -- Valid entry for INDI/FAM
local ptrRec = tblType[strField0] -- get the person/family record pointer
local strValue = dicRow[strTitle] -- get the column data value
if ptrRec and strValue and
not ( strField2 == "REFN" and strType == "INDI" ) then -- custom id handled in CreatePeople above
local strFld = strField0.."."..strField2 -- V1.7
if strField2 == "NAME" then -- names get special treatment here
local ptrFld = tblFld[strFld]
if not ptrFld then -- V1.7
local strFullName = FullName(dicRow,strField0)
ptrFld = NameExists(ptrRec,strFullName) -- V1.7
if not ptrFld then
ptrFld = fhCreateItem("NAME",ptrRec)
tblFld[strFld] = ptrFld:Clone() -- V1.7
fhSetValueAsText(ptrFld,strFullName)
AddSources(ptrFld,intRow,strRefer)
end
end
if strField3 == "NOTE2" then -- can append multiple columns to note
local ptrSub = fhGetItemPtr(ptrFld,"~.NOTE2")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("NOTE2",ptrFld)
end
AppendText(ptrSub,strValue)
elseif strField3 == "TYPE" then -- Add Name Type -- V1.9.2
local ptrSub = fhGetItemPtr(ptrFld,"~.TYPE")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("TYPE",ptrFld)
end
fhSetValueAsText(ptrSub,strValue)
elseif strField3 == "_USED" then -- Add Name Used -- V1.9.3
local ptrSub = fhGetItemPtr(ptrFld,"~._USED")
if ptrSub:IsNull() then
ptrSub = fhCreateItem("_USED",ptrFld)
end
fhSetValueAsText(ptrSub,strValue)
end
else
local ptrFld = tblFld[strFld] -- field already exists? -- V1.7
ptrFld = FindFact(ptrRec,strFld,ptrFld,strField2,dicRow)
if ptrFld then
tblFld[strFld] = ptrFld:Clone() -- V1.7
AddSources(ptrFld,intRow,strRefer)
else
ptrFld = fhCreateItem(strField2,ptrRec) -- create new entry
if ptrFld:IsNotNull() then
tblFld[strFld] = ptrFld:Clone() -- V1.7
if strField2 ~= "SEX" then AddSources(ptrFld,intRow,strRefer) end
end
end
if strField3 then
if strType == "FAM" and strField3 == "AGE" then -- handle ages at family event separately
if ptrFld:IsNull() then
ptrFld = fhCreateItem(strField2,ptrRec)
AddSources(ptrFld,intRow,strRefer)
end
local ptrInd = tblPeople[intRow][arrField[4]] -- get spouse individual record pointer
if ptrInd then
local strTag = "WIFE"
if fhGetItemText(ptrInd,"~.SEX") == "Male" then strTag = "HUSB" end
local ptrSpou = fhCreateItem(strTag,ptrFld) -- makes the HUSB or WIFE bit
local ptrLink = fhCreateItem("AGE",ptrSpou,true)
fhSetValueAsText(ptrLink,strValue)
end
else -- other subfields
local ptrSub = fhGetItemPtr(ptrFld,"~."..strField3)
if ptrSub:IsNull() then
ptrSub = fhCreateItem(strField3,ptrFld)
end
if strField3 == "DATE" then -- set date
SetDate(strValue,ptrSub)
elseif strField3 == "AGE" then -- set age
fhSetValueAsText(ptrSub,strValue)
else
AppendText(ptrSub,strValue) -- can append multiple columns to note, etc
end
end
else -- no subfield
AppendText(ptrFld,strValue) -- can append multiple columns to note, attribute, etc
end
end
end
end
end
if strType == "INDI" then
for strRec, ptrRec in pairs (tblType) do -- set SEX if not already specified
local ptrSex = fhGetItemPtr(ptrRec,"~.SEX")
local strSex = tblRel[strRec].Sex
if ptrSex:IsNull() and strSex ~= "" then
ptrSex = fhCreateItem("SEX",ptrRec)
fhSetValueAsText(ptrSex,strSex)
end
end
end
end -- function AddItems
--[[FindFact
@name FindFact
@description Finds a fact whose fields sync with CSV data
@param ptrRec pointer to current record
@param strRef reference to current fact
@param ptrFld pointer to current fact
@param strFld the tag of current fact
@param dicRow the line contents
]]
function FindFact(ptrRec,strRef,ptrFld,strFld,dicRow) -- Find fact whose fields sync with data -- V1.7
if not ptrFld then
ptrFld = fhGetItemPtr(ptrRec,"~."..strFld) -- First instance of existing fact type
end
while ptrFld:IsNotNull() do -- Check each instance of fact type
local isFound = true
for _,intMap in ipairs ( tblMap[strRef] or {} ) do -- Check each mapped data field
local dicMap = tblMap[intMap]
local strField3 = dicMap.Field[3]
local ptrSub = fhGetItemPtr(ptrFld,"~."..strField3) -- Get matching fact field, if any
if ptrSub:IsNotNull() then
local strValue = dicRow[dicMap.Title] or "" -- Get CSV field value, if any -- V1.9.4
if strField3 == "DATE" then -- Does its Date value sync with CSV field value
if fhGetValueAsDate(ptrSub):Compare(SetDate(strValue,ptrSub,true)) ~= 0 or strValue == "" then -- V1.9.4
isFound = false
break
end
elseif strField3 == "AGE" then -- Does its Age value sync with CSV field value -- V1.9
local strSubA,strSubY,strSubM,strSubD = (fhGetDisplayText(ptrSub):lower().." "):match("(%D+)(%d-)%D+(%d-)%D+(%d-)")
local strValA,strValY,strValM,strValD = ("age "..strValue:lower().." "):match("(%D+)(%d-)%D+(%d-)%D+(%d-)")
if strSubA ~= strValA or strSubY ~= strValY or strSubM ~= strValM or strSubD ~= strValD then
isFound = false
break
end
elseif not fhGetDataClass(ptrSub):match("%l%l%l%ltext") then -- Apart from long/richtext fields, i.e. Note, Address, Text from Source
if fhGetValueAsText(ptrSub) ~= strValue then -- Does its value sync with CSV field value, e.g. Place, Cause
isFound = false
break
end
end
end
end
if isFound then return ptrFld end -- Matching fact found
ptrFld:MoveNext("SAME_TAG")
end
return nil -- No matching fact
end -- function FindFact
--[[ChooseSource
@name ChooseSource
@description Opens sources selection dialog.
]]
function ChooseSource()
local ptrSource = fhNewItemPtr()
if tblSet.AddSource == "Yes" then
local strTitle = nil
if tblSet.SourceRec == "Yes" then
local tblSource = fhPromptUserForRecordSel("SOUR",1)
if #tblSource == 0 then
fhMessageBox("User cancelled source selection.\nNo global source citations will be added.")
tblSet.AddSource = "No"
else
ptrSource = tblSource[1]
strTitle = fhGetValueAsText(fhGetItemPtr(ptrSource,"~.TITL"))
end
elseif tblSet.SourceRec == "No" then
ptrSource = fhCreateItem("SOUR")
strTitle = string.match(strImportFile,".*\\(.-)%.")
local ptrLink = fhCreateItem("TITL", ptrSource)
fhSetValueAsText(ptrLink, strTitle)
end
strSourceTitle = strTitle -- MBT Used by AddSources function
end
dicSources = {}
ptrSource:MoveToFirstRecord("SOUR") -- MBT Obtain all Source Titles and record pointers
while ptrSource:IsNotNull() do
local strTitle = fhGetValueAsText(fhGetItemPtr(ptrSource,"~.TITL"))
dicSources[strTitle] = ptrSource:Clone()
ptrSource:MoveNext()
end
end -- function ChooseSource
--[[AddSources
@name AddSources -- MBT
@description Adds source citations to the indicated pointer
@param ptrItem any item pointer
@param intRow The line of CSV data being processed
@param strHead CSV heading of data item
]]
local dicAssess = {
["1"]="Primary evidence"; ["2"]="Secondary evidence"; ["3"]="Questionable"; ["4"]="Unreliable";
["P"]="Primary evidence"; ["S"]="Secondary evidence"; ["Q"]="Questionable"; ["U"]="Unreliable";
}
function AddSources(ptrItem,intRow,strHead)
-- Get a list of existing Source Titles cited by this item
local dicSour = {}
local ptrSour = fhGetItemPtr(ptrItem,"~.SOUR")
while ptrSour:IsNotNull() do
local strTitle = fhGetValueAsText(fhGetItemPtr(ptrSour,"~>TITL"))
dicSour[strTitle] = ptrSour:Clone()
ptrSour:MoveNext("SAME_TAG")
end
-- If global Source is enabled and is not already cited then add it to Item
if tblSet.AddSource == "Yes" and not dicSour[strSourceTitle] then
local ptrLink = fhCreateItem("SOUR",ptrItem)
if ptrLink:IsNull() then return end -- Quit if citations not allowed
fhSetValueAsLink(ptrLink,dicSources[strSourceTitle])
dicSour[strSourceTitle] = ptrLink:Clone()
end
-- If any Item Citation is defined then add it to Item
strHead = (strHead:gsub("([%.%>%:].+)[%.%>%:]%w+$","%1"))..".SOUR"
strHead = strHead:gsub("%-","%%-") -- Inhibit pattern 'magic' - hyphen symbol -- V1.9.1
-- i.e. "P" => "P.SOUR", "P.NAME" => "P.NAME.SOUR", "P.CHR.DATE" => "P.CHR.SOUR", "P_S.MARR.PLAC" => "P_S.MARR.SOUR"
local ptrLink = fhNewItemPtr()
local dicRow = arrRows[intRow]
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid then
local strField, strExtra = dicMap.Valid:match("^SOUR~([^~]+)~?([^~]-)$")
if strField then
local strValue = dicRow[dicMap.Title]
local strRefer = dicMap.Refer
-- Global Citations are only 9 chars, and explicit Citations must match heading of current Item
if strValue and ( #strRefer < 11 or strRefer:match(strHead) ) then
if strField == "TITL" then
if not dicSources[strValue] then -- Create a new Source record with Title
local ptrSour = fhCreateItem("SOUR")
local ptrTitl = fhCreateItem("TITL",ptrSour)
fhSetValueAsText(ptrTitl,strValue)
dicSources[strValue] = ptrSour:Clone()
end
if not dicSour[strValue] then -- Create a new Source Citation
local ptrLink = fhCreateItem("SOUR",ptrItem)
if ptrLink:IsNull() then return end -- Quit if citations not allowed
fhSetValueAsLink(ptrLink,dicSources[strValue])
dicSour[strValue] = ptrLink:Clone()
end
ptrLink = dicSour[strValue]:Clone()
elseif ptrLink:IsNotNull() then -- Create the new Citation fields
local ptrField = fhGetItemPtr(ptrLink,"~."..strField)
if ptrField:IsNull() then
ptrField = fhCreateItem(strField,ptrLink,true)
end
if strExtra then
local ptrExtra = fhGetItemPtr(ptrField,"~."..strExtra)
if ptrExtra:IsNull() then
ptrExtra = fhCreateItem(strExtra,ptrField)
end
ptrField = ptrExtra:Clone()
end
if strExtra == "DATE" then
SetDate(strValue,ptrField)
elseif strField == "QUAY" then
local strAssess = dicAssess[strValue] or strValue
local isOK = fhSetValueAsText(ptrField,strAssess)
else
AppendText(ptrField,strValue)
end
end
end
end
end
end
end -- function AddSources
--[[FullName
@name FullName
@description Provides full name if available, or combines given and surname.
@param dicRow The line contents
@param strPerson The identifier P, S, etc)
@return FullName
]]
function FullName(dicRow,strPerson)
local function CleanName(strName)
strName = strName or ""
strName = strName:gsub( "^ +", "" )
strName = strName:gsub( " +$", "" )
return strName
end -- local function CleanName
local strFullName = CleanName(dicRow[tblMap[strPerson..".NAME:"]]) -- try full name field
if #strFullName == 0 then -- no full name
local strRev = CleanName(dicRow[tblMap[strPerson..".NAME:SURNAME_FIRST"]]) -- try reversed name
if #strRev > 0 then
strFullName = string.gsub(strRev, "(.-)(%s)(.*)$", "%3%2%1")
else -- join given and surname
strFullName = CleanName(dicRow[tblMap[strPerson..".NAME:GIVEN"]]).." /"..CleanName(dicRow[tblMap[strPerson..".NAME:SURNAME"]]).."/"
end
end
if strFullName == " //" then strFullName = "" end
--# strFullName = strFullName:gsub("(%a)([%w_']*)", function(first, rest) return first:upper()..rest:lower() end ) -- convert to title case -- http://lua-users.org/wiki/StringRecipes
strFullName = strFullName:gsub("([a-zA-Z\194-\244][\128-\191]*)([^ ]*)", function(first, rest) return first:upper()..rest:lower() end ) -- V1.8 -- convert to title case -- http://lua-users.org/wiki/StringRecipes
return strFullName
end -- function FullName
--[[NameExists
@name NameExists
@description Check if the person indicated by the pointer already has the exact name listed in the CSV file
@param ptrRec Pointer to record
@param strFullName
]]
function NameExists(ptrRec,strFullName)
local function CleanName(strName)
strName = strName:gsub( "/" , " " )
strName = strName:gsub( "//", " " )
strName = strName:gsub( " ", " " )
strName = strName:gsub( " $", "" )
return strName
end -- local function CleanName
strFullName = CleanName(strFullName)
for ptrTag in tags(ptrRec) do
if fhGetTag(ptrTag) == "NAME" then
if CleanName(fhGetValueAsText(ptrTag)) == strFullName then
return ptrTag -- V1.7
end
end
end
return false
end -- function NameExists
function tags(pi) -- Tags iterator
local pf = fhNewItemPtr()
local pf2 = fhNewItemPtr()
pf:MoveToFirstChildItem(pi)
return function ()
while pf:IsNotNull() do
pf2:MoveTo(pf)
pf:MoveNext()
return pf2
end
end
end -- function tags
--[[AppendText
@name AppendText
@description Appends new multi-column text to any text field such as NOTE2 or TEXT or ADDR
@param ptrText pointer to field
@param strText string to append
]]
function AppendText(ptrText,strText)
if ptrText:IsNotNull() then
strText = strText or ""
local strSep = ""
local strOrig = fhGetValueAsText(ptrText)
if not strOrig:find(strText,1,"plain") then -- Ensure existing text from synchronized record is not duplicated -- V1.7
if strOrig ~= "" then strSep = tblSet.Separator end
fhSetValueAsText(ptrText,strOrig..strSep..strText)
if strSep:match("\n")
and fhGetValueAsText(ptrText) ~= strOrig..strSep..strText then
fhSetValueAsText(ptrText,strOrig..", "..strText)
end
end
end
end -- function AppendText
--[[SetDate
@name SetDate
@description Edits a date input as a text to make more acceptable to Family Historian and sets the date at the pointer given
@param strDate The date text to be considered
@param ptrDate The pointer to the date
]]
function SetDate(strDate,ptrDate,isValueOnly)
local datDate = fhNewDate()
strDate = strDate or "" -- Cater for no date -- V1.8
strDate = string.gsub(strDate, "before", "bef")
strDate = string.gsub(strDate, "after", "aft")
strDate = string.gsub(strDate, "between", "btw")
strDate = string.gsub(strDate, "from", "frm")
strDate = string.gsub(strDate, "<", "bef")
strDate = string.gsub(strDate, ">", "aft")
if not strDate:match("/ ?%d") then -- Allow / separators -- V1.9.2
strDate = string.gsub(strDate, "[%s%p]", " ")
end
datDate:SetValueAsText(strDate,true)
if isValueOnly then return datDate end
fhSetValueAsDate(ptrDate,datDate)
end -- function SetDate
--[[
@Module: +fh+progbar_v3
@Author: Mike Tate
@Version: 3.0
@LastUpdated: 27 Aug 2020
@Description: Progress Bar library module.
@V3.0: Function Prototype Closure version.
@V1.0: Initial version.
]]
local function progbar_v3()
local fh = {} -- Local environment table
require "iuplua" -- To access GUI window builder
iup.SetGlobal("CUSTOMQUITMESSAGE","YES") -- Needed for IUP 3.28
local tblBars = {} -- Table for optional external attributes
local strBack = "255 255 255" -- Background colour default is white
local strBody = "0 0 0" -- Body text colour default is black
local strFont = nil -- Font dialogue default is current font
local strStop = "255 0 0" -- Stop button colour default is red
local intPosX = iup.CENTER -- Show window default position is central
local intPosY = iup.CENTER
local intMax, intVal, intPercent, intStart, intDelta, intScale, strClock, isBarStop
local lblText, barGauge, lblDelta, btnStop, dlgGauge
local function doFocus() -- Bring the Progress Bar window into Focus
dlgGauge.BringFront="YES" -- If used too often, inhibits other windows scroll bars, etc
end -- local function doFocus
local function doUpdate() -- Update the Progress Gauge and the Delta % with clock
barGauge.Value = intVal
lblDelta.Title = string.format("%4d %% %s ",math.floor(intPercent),strClock)
end -- local function doUpdate
local function doReset() -- Reset all dialogue variables and Update display
intVal = 0 -- Current value of Progress Bar
intPercent= 0.01 -- Percentage of progress
intStart = os.time() -- Start time of progress
intDelta = 0 -- Delta time of progress
intScale = math.ceil( intMax / 1000 ) -- Scale of percentage per second of progress (initial guess is corrected in Step function)
strClock = "00 : 00 : 00" -- Clock delta time display
isBarStop = false -- Stop button pressed signal
doUpdate()
doFocus()
end -- local function doReset
function fh.Start(strTitle,intMaximum) -- Create & start Progress Bar window
if not dlgGauge then
strTitle = strTitle or "" -- Dialogue and button title
intMax = intMaximum or 100 -- Maximun range of Progress Bar, default is 100
local strSize = tostring( math.max( 100, string.len(" Stop "..strTitle) * 8 ) ).."x30" -- Adjust Stop button size to Title
lblText = iup.label { Title=" "; Expand="YES"; Alignment="ACENTER"; Tip="Progress Message"; }
barGauge = iup.progressbar { RasterSize="400x30"; Value=0; Max=intMax; Tip="Progress Bar"; }
lblDelta = iup.label { Title=" "; Expand="YES"; Alignment="ACENTER"; Tip="Percentage and Elapsed Time"; }
btnStop = iup.button { Title=" Stop "..strTitle; RasterSize=strSize; FgColor=strStop; Tip="Stop Progress Button"; action=function() isBarStop = true end; } -- Signal Stop button pressed return iup.CLOSE -- Often caused main GUI to close !!!
dlgGauge = iup.dialog { Title=strTitle.." Progress "; Font=strFont; FgColor=strBody; Background=strBack; DialogFrame="YES"; -- Remove Windows minimize/maximize menu
iup.vbox{ Alignment="ACENTER"; Gap="10"; Margin="10x10";
lblText;
barGauge;
lblDelta;
btnStop;
};
move_cb = function(self,x,y) tblBars.X = x tblBars.Y = y end;
close_cb = btnStop.action; -- Windows Close button = Stop button
}
if type(tblBars.GUI) == "table"
and type(tblBars.GUI.ShowDialogue) == "function" then
dlgGauge.move_cb = nil -- Use GUI library to show & move window
tblBars.GUI.ShowDialogue("Bars",dlgGauge,btnStop,"showxy")
else
dlgGauge:showxy(intPosX,intPosY) -- Show the Progress Bar window
end
doReset() -- Reset the Progress Bar display
end
end -- function Start
function fh.Message(strText) -- Show the Progress Bar message
if dlgGauge then lblText.Title = strText end
end -- function Message
function fh.Step(intStep) -- Step the Progress Bar forward
if dlgGauge then
intVal = intVal + ( intStep or 1 ) -- Default step is 1
local intNew = math.ceil( intVal / intMax * 100 * intScale ) / intScale
if intPercent ~= intNew then -- Update progress once per percent or per second, whichever is smaller
intPercent = math.max( 0.1, intNew ) -- Ensure percentage is greater than zero
if intVal > intMax then intVal = intMax intPercent = 100 end -- Ensure values do not exceed maximum
intNew = os.difftime(os.time(),intStart)
if intDelta < intNew then -- Update clock of elapsed time
intDelta = intNew
intScale = math.ceil( intDelta / intPercent ) -- Scale of seconds per percentage step
local intHour = math.floor( intDelta / 3600 )
local intMins = math.floor( intDelta / 60 - intHour * 60 )
local intSecs = intDelta - intMins * 60 - intHour * 3600
strClock = string.format("%02d : %02d : %02d",intHour,intMins,intSecs)
end
doUpdate() -- Update the Progress Bar display
end
iup.LoopStep()
end
end -- function Step
function fh.Focus() -- Bring the Progress Bar window to front
if dlgGauge then doFocus() end
end -- function Focus
function fh.Reset() -- Reset the Progress Bar display
if dlgGauge then doReset() end
end -- function Reset
function fh.Stop() -- Check if Stop button pressed
iup.LoopStep()
return isBarStop
end -- function Stop
function fh.Close() -- Close the Progress Bar window
isBarStop = false
if dlgGauge then dlgGauge:destroy() dlgGauge = nil end
end -- function Close
function fh.Setup(tblSetup) -- Setup optional table of external attributes
if tblSetup then
tblBars = tblSetup
strBack = tblBars.Back or strBack -- Background colour
strBody = tblBars.Body or strBody -- Body text colour
strFont = tblBars.Font or strFont -- Font dialogue
strStop = tblBars.Stop or strStop -- Stop button colour
intPosX = tblBars.X or intPosX -- Window position
intPosY = tblBars.Y or intPosY
end
end -- function Setup
return fh
end -- local function progbar_v3
local progbar = progbar_v3() -- To access FH progress bars module
--[[ListItemLookup
@name ListItemLookup
@description lookup value in Tag and optionally SubTag of a list
@param arrList the list
@param strReturn the entry in list to return
@param strValue the value to match
@param strSubTag the subtag to match
]]
function ListItemLookup(arrList,strReturn,strValue,strSubTag)
if strValue then
for _, dicList in ipairs (arrList) do
if strValue == dicList.Tag then
if not strSubTag or strSubTag == dicList.SubTag then
return dicList[strReturn]
end
end
end
end
return strValue or ""
end -- function ListItemLookup
--[[FillGridbox
@name FillGridbox
@description Adds a line of boxes to the GUI for each column in the input CSV
@param arrLines CSV data lines
@param arrTitles CSV column titles
]]
local dicTip = {
Header = " CSV data file column header ";
Sample = " CSV data file cell value ";
Record = " Type of record to be created ";
Field2 = " Label for data field level 1 ";
Field3 = " Label for data field level 2 ";
}
function FillGridbox(arrLines,arrTitles)
local function MakeDropList(intCol,strType) -- items added later via FillDropDowns(intCol,strPart)
return iup.list { DropDown = "Yes"; Visible_Items = "16"; Tip = dicTip[strType]; Sort = "No"; -- use tblLists[strType].Label order
action = function(self,text,item,state) ActionChange(intCol,strType,text,state) end; } -- killfocus_cb replaced by action
end -- local function MakeDropList
arrRows = arrLines
dicBox = {} -- used in the GUI
tblMap = {}
for intCol, strTitle in ipairs (arrTitles) do
-- interpret headings
tblMap[intCol] = SplitTitle(strTitle)
TestFields(intCol)
if not tblMap[intCol].Valid then
TitlesSecondChance(intCol)
TestFields(intCol)
end
-- add to GUI
if dicBox[strTitle] then -- cope with duplicate title
strTitle = strTitle.." "
tblMap[intCol].Title = strTitle
tblMap[intCol].Valid = false
end
dicBox[strTitle] = {}
dicBox[strTitle].Header = iup.label { Title = strTitle; Tip = dicTip.Header; }
dicBox[strTitle].Sample = iup.text { Value = arrLines[1][strTitle]; FgColor = strNavy; ReadOnly = "Yes"; Tip = dicTip.Sample; }
dicBox[strTitle].Record = MakeDropList(intCol,"Record")
dicBox[strTitle].Field2 = MakeDropList(intCol,"Field2")
dicBox[strTitle].Field3 = MakeDropList(intCol,"Field3")
for _, strData in ipairs({ "Header"; "Sample"; "Record"; "Field2"; "Field3"; }) do
local iupBox = dicBox[strTitle][strData]
iup.Append(iupGridbox,iupBox )
iup.Map(iupBox) -- need to do this so new fields will show
end
end
if not tblMap["P.REFN"] then -- V1.7
fhMessageBox("\nCSV Data File Syntax Error\n\nThe data has no P.REFN column defined.\n","MB_OK","MB_ICONEXCLAMATION")
end
end -- function FillGridbox
--[[FillItemLookup
@name FillItemLookup
@description lookup value in an item of a list and append list to iupBox
@param iupBox the box field to append list
@param arrList the list
@param ... the other ListItemLookup parameters
]]
function FillItemLookup(iupBox,arrList,...)
for _, dicList in ipairs (arrList) do
iupBox.AppendItem = dicList.Label or "?"
end
return ListItemLookup(arrList,...)
end -- function FillItemLookup
--[[SetListValue
@name SetListValue
@description set field list integer value matching item in list
@param iupBox the box field list
@param strVal the text value of item
]]
function SetListValue(iupBox,strVal)
if #strVal > 0 then
for intItem = 1, 99 do
if iupBox[tostring(intItem)] == strVal then
iupBox.Value = intItem
return
end
end
end
iupBox.Value = 0
end -- function SetListValue
--[[FillDropDowns
@name FillDropDowns
@description Populates dropdown lists in the GUI
@param intCol The column number in the CSV file
@param strPart Record or Field2 or Field3 (ignored) or All
]]
function FillDropDowns(intCol,strPart)
local dicMap = tblMap[intCol]
local iupBox = dicBox[dicMap.Title]
local arrField = dicMap.Field
local arrLabel = dicMap.Label
local intRecordVal = 0
local strField2Val = arrLabel[2]
local strField3Val = ""
if strPart == "Record" or strPart == "All" then
-- Record (primary, spouse, family, etc)
iupBox.Record.RemoveItem = "All"
iupBox.Field2.RemoveItem = "All"
iupBox.Field3.RemoveItem = "All"
for _, dicList in ipairs (tblLists.Record) do
iupBox.Record.AppendItem = dicList.Label
end
if strPart == "Record" or dicMap.Valid then
local dicRel = tblRel[arrField[0]]
if dicRel then
intRecordVal = dicRel.List
arrField[1] = dicRel.RecTag
end
else
arrField[1] = "EXCL"
arrField[2] = ""
end
iupBox.Record.Value = intRecordVal
local strRecTag = arrField[1]
-- Field2
if strRecTag == "SOUR" then
strField2Val = FillItemLookup(iupBox.Field2,tblLists.Source,"Label",arrField[2])
SetListValue(iupBox.Field2,strField2Val)
elseif strRecTag ~= "EXCL" then
strField2Val = ListItemLookup(tblLists.Field2,"Label",arrField[2])
for _, dicList in ipairs (tblLists.Field2) do
if strRecTag == "INDI" then
if dicList.RecTag == "INDI" or dicList.RecTag == "FAM" then
iupBox.Field2.AppendItem = dicList.Label
end
elseif dicList.RecTag == strRecTag or dicList.FactType == "MISC" or dicList.FactType == "Source" then
iupBox.Field2.AppendItem = dicList.Label
end
end
SetListValue(iupBox.Field2,strField2Val)
end
end
if strPart == "Field2" or strPart == "All" then
-- Field3
iupBox.Field3.RemoveItem = "All"
if strPart == "Field2" or dicMap.Valid then
if strField2Val == "Note" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Notes,"Label",arrField[3],arrField[4])
elseif strField2Val == "Name" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Names,"Label",arrField[3],arrField[4])
elseif strField2Val == "Source" then
strField3Val = FillItemLookup(iupBox.Field3,tblLists.Source,"Label",arrField[3])
elseif strField2Val ~= "Custom Ref Id" and strField2Val ~= "Sex" and arrField[0] ~= "SOUR" then
strField3Val = ListItemLookup(tblLists.Field3,"Label",arrField[3],arrField[4])
for intList, dicList in ipairs (tblLists.Field3) do
if ( dicList.Usage == "ALL" )
or ( dicList.Usage == arrField[1] )
or ( dicList.Usage == strField2Val ) then
iupBox.Field3.AppendItem = dicList.Label
end
end
end
end
SetListValue(iupBox.Field3,strField3Val)
end
tblMap[intCol].Field = arrField
end -- function FillDropDowns
--[[ActionChange
@name ActionChange
@description Checks field lists for if the given fact label exits.
Triggered by the user entering a value in the field interpretation dialog.
Writes new values to tblMap, resets dropdowns for the line.
Then calls functions to test the new values and update the colour coding on the field interpretation dialog tab.
@param intCol the title column number
@param strPart whether record, field, subfield, etc
@param strText new chosen value
@param intState 1 when new item chosen
]]
function ActionChange(intCol,strPart,strText,intState) -- MBT -- rewritten
if intState == 1 and strText then
local dicMap = tblMap[intCol]
local arrLabel = dicMap.Label
local arrField = dicMap.Field
local dicField = {} -- Lookup for arrField index
dicField.Record = 0
dicField.Field2 = 2
dicField.Field3 = 3
local intField = dicField[strPart]
local tblList = tblLists[strPart]
if arrField[intField-1] == "NAME" then tblList = tblLists.Names end
if arrField[intField-1] == "NOTE2" then tblList = tblLists.Notes end
if arrField[intField-1] == "SOUR" then tblList = tblLists.Source end
local strValue = strText -- Grab text value chosen by user
local intIndex = tblList[strValue] -- Convert to List index -- or tonumber(strValue)
local dicEntry = tblList[intIndex] -- Get entry from List, if any
if dicEntry then strValue = dicEntry.Tag end -- Use entry Tag as value
if arrField[intField] ~= strValue then
if strValue then strValue = strValue:upper() end
arrField[intField] = strValue -- Update arrField & arrLabel values
if intField == 0 then -- Special case for Record
intField = 1
arrField[1] = dicEntry.RecTag or "INDI"
arrLabel[1] = arrField[1]
strValue = strText
else
arrField[intField] = strValue
arrLabel[intField] = strText
end
arrField[intField+1] = nil
arrLabel[intField+1] = nil
arrField[intField+2] = nil
arrLabel[intField+2] = nil
arrField[intField+3] = nil
arrLabel[intField+3] = nil
end
if strPart == "Field3" then
if dicEntry then
arrField[3] = dicEntry.Tag
local strSubTag = dicEntry.SubTag
if strSubTag then
arrField[4] = strSubTag
end
end
end
tblMap[intCol].Field = arrField
tblMap[intCol].Label = arrLabel
TestFields(intCol)
if not ( tblMap[intCol].Valid or strPart == "Record" ) then
TitlesSecondChance(intCol)
TestFields(intCol)
end
FillDropDowns(intCol,strPart)
MarkValid(intCol)
AmendLabelValidText()
end
end -- function ActionChange
----------------------------
-- MAIN CODE STARTS HERE
----------------------------
----------------------------
-- Establish tables
----------------------------
-- manage relationships
tblRel = {}
-- Individuals
tblRel.P = { List = 1 ; Label = "Primary" ; Sex= "" ; RecTag = "INDI"; Fams = "P_S" ; Spouse = "S" ;}
tblRel.S = { List = 2 ; Label = "Spouse" ; Sex= "" ; RecTag = "INDI"; Fams = "P_S" ; Spouse = "P" ;} -- spouse
tblRel.PF = { List = 3 ; Label = "Father" ; Sex= "M"; RecTag = "INDI"; Fams = "PF_PM"; Spouse = "PM";} -- primary's father
tblRel.PM = { List = 4 ; Label = "Mother" ; Sex= "F"; RecTag = "INDI"; Fams = "PF_PM"; Spouse = "PF";} -- primary's mother
tblRel.SF = { List = 5 ; Label = "Spouse's father"; Sex= "M"; RecTag = "INDI"; Fams = "SF_SM"; Spouse = "SM";} -- spouse's father
tblRel.SM = { List = 6 ; Label = "Spouse's mother"; Sex= "F"; RecTag = "INDI"; Fams = "SF_SM"; Spouse = "SF";} -- spouse's mother
-- Families -- Spouse1 will default to Husband (and become male) if no other gender information is present, Spouse2 to Wife/female.
tblRel.P_S = { List = 7 ; Label = "Family: Primary & Spouse" ; RecTag = "FAM"; Spouse1 = "P" ; Spouse2 = "S" ; } -- family primary-spouse
tblRel.PF_PM = { List = 8 ; Label = "Family: Primary's parents"; RecTag = "FAM"; Spouse1 = "PF"; Spouse2 = "PM"; Child = "P";} -- family primary's parents
tblRel.SF_SM = { List = 9 ; Label = "Family: Spouse's parents" ; RecTag = "FAM"; Spouse1 = "SF"; Spouse2 = "SM"; Child = "S";} -- family spouse's parents
-- Other
tblRel.SOUR = { List = 10; Label = "Source Citation" ; RecTag = "SOUR" ;}
tblRel.EXCL = { List = 11; Label = "Exclude from import" ; RecTag = "EXCL" ;} -- No record (column not to be imported)
tblPeople = {} -- will keep pointers for each person -- tblPeople[intRow][strIdent] = ptrInd:Clone()
tblFamilies = {} -- will keep pointers for each family -- tblFamilies[intRow][strIdent] = ptrFam:Clone()
tblRefn = {} -- reference numbers -> ptr
tblLists = {} -- keeps lookup information
-- record
tblLists.Record = {}
for strTag, dicRel in pairs(tblRel) do
tblLists.Record[dicRel.List] = { Label = dicRel.Label; Tag = strTag; RecTag = dicRel.RecTag; }
end
-- field
tblLists.Field2 = {}
table.insert(tblLists.Field2,{ Label = "Custom Ref Id" ; Tag = "REFN" ; FactType = "MISC" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Name" ; Tag = "NAME" ; FactType = "Misc" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Note" ; Tag = "NOTE2" ; FactType = "MISC" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Sex" ; Tag = "SEX" ; FactType = "Misc" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Flag" ; Tag = "_FLGS" ; FactType = "Misc" ; RecTag = "INDI"; }) -- V1.9.4
table.insert(tblLists.Field2,{ Label = "Source" ; Tag = "SOUR" ; FactType = "Source" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Adoption" ; Tag = "ADOP" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ADOPTED" ;})
table.insert(tblLists.Field2,{ Label = "Annulment" ; Tag = "ANUL" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ANNULLED" ;})
table.insert(tblLists.Field2,{ Label = "Baptism" ; Tag = "BAPM" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BAPTISED" ;})
table.insert(tblLists.Field2,{ Label = "Bar Mitzvah" ; Tag = "BARM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Bas Mitzvah" ; Tag = "BASM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Birth" ; Tag = "BIRT" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BORN" ;})
table.insert(tblLists.Field2,{ Label = "Blessing" ; Tag = "BLES" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Burial" ; Tag = "BURI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "BURIED" ;})
table.insert(tblLists.Field2,{ Label = "Census" ; Tag = "CENS" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Child Count" ; Tag = "NCHI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Christening" ; Tag = "CHR" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CHIRISTENED" ;})
table.insert(tblLists.Field2,{ Label = "Christening (adult)" ; Tag = "CHRA" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Confirmation" ; Tag = "CONF" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CONFIRMED" ;})
table.insert(tblLists.Field2,{ Label = "Cremation" ; Tag = "CREM" ; FactType = "Event" ; RecTag = "INDI"; Verb = "CREMATED" ;})
table.insert(tblLists.Field2,{ Label = "Death" ; Tag = "DEAT" ; FactType = "Event" ; RecTag = "INDI"; Verb = "DIED" ;})
table.insert(tblLists.Field2,{ Label = "Divorce" ; Tag = "DIV" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "DIVORCED" ;})
table.insert(tblLists.Field2,{ Label = "Divorce Filed" ; Tag = "DIVF" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Education" ; Tag = "EDUC" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "EDUCATED" ;})
table.insert(tblLists.Field2,{ Label = "Emigration" ; Tag = "EMIG" ; FactType = "Event" ; RecTag = "INDI"; Verb = "EMIGRATED" ;})
table.insert(tblLists.Field2,{ Label = "Engagement" ; Tag = "ENGA" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "ENGAGED" ;})
table.insert(tblLists.Field2,{ Label = "First communion" ; Tag = "FCOM" ; FactType = "Event" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Graduation" ; Tag = "GRAD" ; FactType = "Event" ; RecTag = "INDI"; Verb = "GRADUATED" ;})
table.insert(tblLists.Field2,{ Label = "Group/Caste Membership" ; Tag = "CAST" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Immigration" ; Tag = "IMMI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "IMMIGRATED" ;})
table.insert(tblLists.Field2,{ Label = "Marriage" ; Tag = "MARR" ; FactType = "Event" ; RecTag = "FAM" ; Verb = "MARRIED" ;})
table.insert(tblLists.Field2,{ Label = "Marriage Banns" ; Tag = "MARB" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Contract" ; Tag = "MARC" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Count" ; Tag = "NMR" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Marriage Licence" ; Tag = "MARL" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "Marriage Settlement" ; Tag = "MARS" ; FactType = "Event" ; RecTag = "FAM" ; })
table.insert(tblLists.Field2,{ Label = "National Identity Number" ; Tag = "IDNO" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "National or Tribal Origin" ; Tag = "NATI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Naturalisation" ; Tag = "NATU" ; FactType = "Event" ; RecTag = "INDI"; Verb = "NATURALISED";})
table.insert(tblLists.Field2,{ Label = "Occupation" ; Tag = "OCCU" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "EMPLOYED" ;})
table.insert(tblLists.Field2,{ Label = "Ordination" ; Tag = "ORDN" ; FactType = "Event" ; RecTag = "INDI"; Verb = "ORDAINED" ;})
table.insert(tblLists.Field2,{ Label = "Physical Description" ; Tag = "DSCR" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Possessions" ; Tag = "PROP" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "POSSESSED" ;})
table.insert(tblLists.Field2,{ Label = "Probate" ; Tag = "PROB" ; FactType = "Event" ; RecTag = "INDI"; Verb = "PROBATED" ;})
table.insert(tblLists.Field2,{ Label = "Religion" ; Tag = "RELI" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Residence" ; Tag = "RESI" ; FactType = "Attribute" ; RecTag = "INDI"; Verb = "RESIDED" ;})
table.insert(tblLists.Field2,{ Label = "Retirement" ; Tag = "RETI" ; FactType = "Event" ; RecTag = "INDI"; Verb = "RETIRED" ;})
table.insert(tblLists.Field2,{ Label = "Title" ; Tag = "TITL" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "US Social Security Number" ; Tag = "SSN" ; FactType = "Attribute" ; RecTag = "INDI"; })
table.insert(tblLists.Field2,{ Label = "Will" ; Tag = "WILL" ; FactType = "Event" ; RecTag = "INDI"; })
-- subfields
tblLists.Field3 = {}
table.insert(tblLists.Field3,{ Label = "Address" ; Tag = "ADDR" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Age" ; Tag = "AGE" ; Usage = "INDI" ;})
table.insert(tblLists.Field3,{ Label = "Age: Primary" ; Tag = "AGE" ; Usage = "FAM" ; SubTag = "P" ; })
table.insert(tblLists.Field3,{ Label = "Age: Spouse" ; Tag = "AGE" ; Usage = "FAM" ; SubTag = "S" ; })
table.insert(tblLists.Field3,{ Label = "Cause" ; Tag = "CAUS" ; Usage = "Death" ;}) -- death
table.insert(tblLists.Field3,{ Label = "Date" ; Tag = "DATE" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Note" ; Tag = "NOTE2" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Type" ; Tag = "TYPE" ; Usage = "ALL" ;}) -- V1.9.2
table.insert(tblLists.Field3,{ Label = "Place" ; Tag = "PLAC" ; Usage = "ALL" ;})
table.insert(tblLists.Field3,{ Label = "Source> Source Title" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "TITL" ; })
table.insert(tblLists.Field3,{ Label = "Source: Assessment" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "QUAY" ; })
table.insert(tblLists.Field3,{ Label = "Source: Entry Date" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "DATE" ; })
table.insert(tblLists.Field3,{ Label = "Source: Text From Source" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "TEXT" ; })
table.insert(tblLists.Field3,{ Label = "Source: Where in Source" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "PAGE" ; })
table.insert(tblLists.Field3,{ Label = "Source: Citation Note" ; Tag = "SOUR" ; Usage = "ALL" ; SubTag = "NOTE2" ; })
-- citations field
tblLists.Source = {}
table.insert(tblLists.Source,{ Label = "Source Title" ; Tag = "TITL" ; SubTag ="TITL" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Assessment" ; Tag = "QUAY" ; SubTag ="QUAY" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Entry Date" ; Tag = "DATE" ; SubTag ="DATE" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Text From Source" ; Tag = "TEXT" ; SubTag ="TEXT" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Where in Source" ; Tag = "PAGE" ; SubTag ="PAGE" ; RecTag = "SOUR"; })
table.insert(tblLists.Source,{ Label = "Citation Note" ; Tag = "NOTE2" ; SubTag ="NOTE2" ; RecTag = "SOUR"; })
-- names subfield
tblLists.Names = {}
table.insert(tblLists.Names ,{ Label = "Full name" ; Tag = nil ; })
table.insert(tblLists.Names ,{ Label = "Given" ; Tag = "GIVEN" ; })
table.insert(tblLists.Names ,{ Label = "Used" ; Tag = "_USED" ; }) -- V1.9.3
table.insert(tblLists.Names ,{ Label = "Surname" ; Tag = "SURNAME" ; })
table.insert(tblLists.Names ,{ Label = "Surname First" ; Tag = "SURNAME_FIRST" ; })
table.insert(tblLists.Names ,{ Label = "Note" ; Tag = "NOTE2" ; })
table.insert(tblLists.Names ,{ Label = "Type" ; Tag = "TYPE" ; }) -- V1.9.2
table.insert(tblLists.Names ,{ Label = "Source> Source Title" ; Tag = "SOUR"; SubTag = "TITL" ; })
table.insert(tblLists.Names ,{ Label = "Source: Assessment" ; Tag = "SOUR"; SubTag = "QUAY" ; })
table.insert(tblLists.Names ,{ Label = "Source: Entry Date" ; Tag = "SOUR"; SubTag = "DATE" ; })
table.insert(tblLists.Names ,{ Label = "Source: Text From Source" ; Tag = "SOUR"; SubTag = "TEXT" ; })
table.insert(tblLists.Names ,{ Label = "Source: Where in Source" ; Tag = "SOUR"; SubTag = "PAGE" ; })
table.insert(tblLists.Names ,{ Label = "Source: Citation Note" ; Tag = "SOUR"; SubTag = "NOTE2" ; })
-- notes subfield
tblLists.Notes = {}
table.insert(tblLists.Notes ,{ Label = "Source> Source Title" ; Tag = "SOUR"; SubTag = "TITL" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Assessment" ; Tag = "SOUR"; SubTag = "QUAY" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Entry Date" ; Tag = "SOUR"; SubTag = "DATE" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Text From Source" ; Tag = "SOUR"; SubTag = "TEXT" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Where in Source" ; Tag = "SOUR"; SubTag = "PAGE" ; })
table.insert(tblLists.Notes ,{ Label = "Source: Citation Note" ; Tag = "SOUR"; SubTag = "NOTE2"; })
-- Add Label to index translation
for _, tblListsInfo in ipairs ({ tblLists.Record; tblLists.Field2; tblLists.Field3; tblLists.Source; tblLists.Names; tblLists.Notes; }) do
for intList, tblList in ipairs (tblListsInfo) do
tblListsInfo[tblList.Label] = intList
end
end
-------------------------------
-- Default settings
-------------------------------
tblSet = {} -- Settings table
tblSet.AddSource = "Yes" -- "Yes" = All item entries, "No" = None, "TBD" = Names and fields only
tblSet.SourceRec = "Yes" -- "Yes" = Choose source record, "No" = Create from file name
tblSet.Separator = ", "
tblSet.RetainID = "Yes" -- "Yes" = Retain REFN in Custom ID
tblSet.SyncREFN = "Yes" -- "Yes" = Sync existing Custom ID with each REFN -- V1.7
tblSet.UseRecId = "No" -- "Yes" = Treat unsynced integer REFN as Rec Id -- V1.9.1
-------------------------------
-- Prepare GUI
-------------------------------
function PrepareGUI()
local tblActivate = {}
local strFontBold = "Helvetica, Bold 10"
local strFontNorm = "Helvetica, Normal 10"
local iupLabelValid = iup.label {} -- V1.7
local strLabelValid = [[ columns have been successfully mapped to fields.
The plugin will not attempt to import columns where the Header Title is red.]]
function AmendLabelValidText() -- V1.7 -- Always use same layout before and after loading file
local intValid = 0
for _, dicMap in ipairs (tblMap) do
if dicMap.Valid then
intValid = intValid + 1
end
end
iupLabelValid.Title = "Check the Field Interpretation (next tab).\n\n"..intValid.."/"..#tblMap..(strLabelValid:gsub("\t",""))
end -- function AmendLabelValidText
AmendLabelValidText() -- V1.7
local function MakeButton(strTitle,strActive,strColor,strTip)
strColor = strColor or strBlack
return iup.button { Title = strTitle; Active = strActive; FgColor = strColor; Tip = strTip; Font = strFontBold; Padding = "10x2"; Size = "80"; }
end -- local function MakeButton
local function ChooseButton(btnChoose)
btnChoose = MakeButton("Choose file","Yes",strBlack," Select the CSV data file ")
btnChoose.action = function(self)
local intStatus, strFile = GetExistingFile("Select CSV File","*.csv","Comma Separated File","Documents")
if intStatus ~= "-1" then
btnChoose.Active = "No"
strImportFile = strFile
local strFile,wasAnsi = FileNameToANSI(strFile)
if not ( wasAnsi ) then -- V1.9 -- Cater for non-ANSI filename
FSO:CopyFile(strImportFile,strFile)
end
FillGridbox( table.loadcsv(strFile,true) )
if not ( wasAnsi ) then
FSO:DeleteFile(strFile) -- V1.9 -- Cater for non-ANSI filename
end
iupSample.SpinMax = #arrRows
for intCol = 1, #tblMap do
FillDropDowns(intCol,"All")
MarkValid(intCol)
end
AmendLabelValidText()
iupDialog.Size = iup.NULL
iup.Refresh(iupDialog)
iupDialog.MinSize = iupDialog.RasterSize
for _, iupActivate in ipairs (tblActivate) do
iupActivate.Active = "Yes"
end
end
end
return btnChoose
end -- local function ChooseButton
local function HelpButton(btnHelp)
btnHelp = MakeButton("Help","Yes",strGreen," Open the help and advice page ")
btnHelp.action = function(self)
fhShellExecute("https://pluginstore.family-historian.co.uk/page/help/flexible-csv-importer","","","open")
fhSleep(3000,500)
iupDialog.BringFront="YES"
end
return btnHelp
end -- local function HelpButton
local function ImportButton(btnImport)
btnImport = MakeButton("Import","No",strBlack," Import the CSV data file ")
btnImport.action = function(self)
ImportRecords()
return iup.CLOSE
end
table.insert(tblActivate,btnImport)
return btnImport
end -- local function ImportButton
local function CancelButton(btnCancel)
btnCancel = MakeButton("Cancel","Yes",strRed," Cancel the Plugin ")
btnCancel.action = function(self)
fhMessageBox("Import cancelled at user request.")
return iup.CLOSE
end
return btnCancel
end -- local function CancelButton
local function MakeDropList(...)
return iup.list { DropDown = "Yes"; Value = "1"; Visible_Items = "9"; Size = "80"; ...; }
end -- local function MakeDropList
iupListAddSource = MakeDropList( "Yes", "No" )
iupListAddSource.action = function(self,text,item,state)
if state == 1 then tblSet.AddSource = text end
end
iupListSourceRec = MakeDropList( "Yes", "No" )
iupListSourceRec.action = function(self,text,item,state)
if state == 1 then tblSet.SourceRec = text end
end
iupListSeparator = MakeDropList( "[comma] , ", "[semicolon] ; ", "[spaces]", "[newline]", "[newline][newline]" )
iupListSeparator.action = function(self,text,item,state)
if state == 1 then
tblSet.Separator = ({ ", "; "; "; " "; "\n"; "\n\n"; })[item]
end
end
iupListRetainID = MakeDropList( "Yes", "No" )
iupListRetainID.action = function(self,text,item,state)
if state == 1 then tblSet.RetainID = text end
end
iupListSyncREFN = MakeDropList( "Yes", "No" )
iupListSyncREFN.action = function(self,text,item,state)
if state == 1 then tblSet.SyncREFN = text end
end
iupListUseRecId = MakeDropList( "No", "Yes" )
iupListUseRecId.action = function(self,text,item,state)
if state == 1 then tblSet.UseRecId = text end
end
iupDirections = iup.vbox { Font = strFontNorm;
iup.label { title = "This plugin imports data from a chosen CSV data file to Family Historian fields."; };
iup.hbox { iup.label { Title = "Step 1: "}; Alignment = "ACENTER"; Gap = "10"; ChooseButton(btnChoose); iup.fill{}; };
iup.hbox { iup.label { Title = "Step 2: "}; Alignment = "ACENTER"; Gap = "10"; iup.label { Title = "Check the Settings (below)."}; };
iup.hbox { iup.label { Title = "Step 3: "}; Alignment = "ACENTRE"; Gap = "10"; iupLabelValid; };
iup.hbox { iup.label { Title = "Step 4: "}; Alignment = "ACENTER"; Gap = "10"; ImportButton(Import1); };
};
local strSeparator = "Select a separator for multiple entries in note fields or other text fields :"
iupSettings = iup.vbox { Font = strFontNorm;
iup.hbox { iup.label { Title = "Add a source citation to every item imported ?"; }; iup.fill{}; iupListAddSource; };
iup.hbox { iup.label { Title = "Select existing source record for such citations ?"; }; iup.fill{}; iupListSourceRec; };
iup.hbox { iup.label { Title = "Select a separator for multiple entries in Note or other text fields :"; }; iup.fill{}; iupListSeparator; };
iup.hbox { iup.label { Title = "Retain the REFN Custom IDs of each person ?"; }; iup.fill{}; iupListRetainID ; };
iup.hbox { iup.label { Title = "Sync any Custom IDs with REFN of each person ?"; }; iup.fill{}; iupListSyncREFN ; };
iup.hbox { iup.label { Title = "Treat any unsynced integer REFN as a Record ID ?"; }; iup.fill{}; iupListUseRecId ; };
};
iupTab1 = iup.vbox { TabTitle = "Directions and Settings"; Margin = "10x5"; Gap = "5";
iup.frame { Title = "Directions"; Font = strFontBold; Margin = "20x5"; iupDirections; };
iup.frame { Title = "Settings" ; Font = strFontBold; Margin = "20x5"; iupSettings; };
};
iupSample = iup.text { Spin = "Yes"; SpinValue = 1; SpinMin = 1; SpinMax = 1; Size = "40"; Tip = " Select CSV data row number "; }
iupSample.spin_cb = function(self,intRow) -- get new line of sample data to display
for intCol, dicMap in ipairs (tblMap) do
dicBox[dicMap.Title].Sample.Value = arrRows[intRow][dicMap.Title] or ""
end
end
local function MakeTitle(strSize,strTitle,strTip)
return iup.label { Title = strTitle.." "; Tip = strTip; Font = strFontBold; Size = strSize; Expand = "Horizontal"; Alignment = "ACenter"; }
end -- local function MakeTitle
local function MakeGrid(...) -- first line. The other lines are appended after file import.
return iup.gridbox { Font = strFontNorm; Orientation = "Horizontal"; NumDiv = "5"; GapLin = "9"; GapCol = "4"; ExpandChildren = "Horizontal"; ...; }
end -- local function MakeGrid
iupGridbox = MakeGrid(
MakeTitle("88x0","",dicTip.Header:gsub(" $","s ")),
MakeTitle("88x0","",dicTip.Sample:gsub(" $","s ")),
MakeTitle("99x0","",dicTip.Record),
MakeTitle("88x0","",dicTip.Field2),
MakeTitle("88x0","",dicTip.Field3) )
iupTab2 = iup.vbox { TabTitle = "Field Interpretation"; Margin = "10x13"; Gap = "5";
iup.frame { Margin = "0x2";
iup.vbox {
iup.vbox { Expand = "Horizontal"; Margin = "5x5";
MakeGrid(
MakeTitle("88","Header Title" ,dicTip.Header:gsub(" $","s ")),
iup.hbox { Size = "88"; MakeTitle("20","Sample",dicTip.Sample:gsub(" $","s ")); iupSample; Margin = "0x0"; },
MakeTitle("99","Record relates to" ,dicTip.Record),
MakeTitle("88","Field Label" ,dicTip.Field2),
MakeTitle("99","Detail Label " ,dicTip.Field3)
);
};
iup.scrollbox { iupGridbox; ScrollBar = "Vertical"; Margin = "10x5"; };
};
};
iup.label { Title = "Choose a CSV data file to begin. Column header titles shown in red will not be imported."; Font = strFontNorm; };
};
iupLab3 = iup.label{ Title = "TBD"; Padding = "10x5"; Tip = " Some basic advice is provided here, \n but for more detail use the Help button "; } -- V1.6
iupTab3 = iup.vbox { TabTitle = "More Information"; Margin = "10x5";
iup.frame { Title = "Field naming"; Font = strFontBold; Margin = "10x5";
iup.scrollbox { ScrollBar = "Vertical"; Font = strFontNorm; iupLab3;
};
};
};
iupTabs = iup.tabs { iupTab1; iupTab2; iupTab3; Font = strFontBold; }
iupBtns = iup.hbox { HelpButton(Help); ImportButton(Import); CancelButton(Cancel); Homogeneous="Yes"; Gap = "50"; Margin = "5x5"; };
if fhGetAppVersion() > 6 then -- FH V7 IUP 3.28 -- V1.6
iupTabs.TabPadding = "10x5"
else -- FH V6 IUP 3.11 -- V1.6
iupTabs.Padding = "10x5"
end
iupDialog = iup.dialog { Title = "Flexible CSV Importer "..strVersion; iup.vbox { iupTabs; iupBtns; Alignment = "ACENTER"; }; close_cb = function() return iup.CLOSE end; }
iupDialog:show()
iupDialog.MinSize = iupDialog.RasterSize
iupLab3.Title = strFieldNaming -- Delay setting full text to prevent dialogue adopting full height of More Information tab -- V1.6
iup.Refresh(iupDialog)
if (iup.MainLoopLevel()==0) then
iup.MainLoop()
end
iupDialog:destroy()
end -- function PrepareGUI
--------------------------
-- Import records
--------------------------
function ImportRecords()
ChooseSource()
local intRows = 0
local intCells = 0
local intPeople = 0
local intFamilies = 0
local numStartTime = os.clock()
for _, dicRows in ipairs (arrRows) do -- count cells with data
for _, _ in pairs (dicRows) do intCells = intCells + 1 end
end
if intCells > 1000 then
progbar.Start("CSV Import",#arrRows)
end
if tblSet.SyncREFN == "Yes" then -- Build Custom ID REFN to record pointer table -- V1.7
local ptrRec = fhNewItemPtr()
ptrRec:MoveToFirstRecord("INDI")
while ptrRec:IsNotNull() do
local strRefn = fhGetItemText(ptrRec,"~.REFN")
if #strRefn > 0 then
tblRefn[strRefn] = ptrRec:Clone()
end
ptrRec:MoveNext()
end
ptrRec:MoveToFirstRecord("FAM")
while ptrRec:IsNotNull() do -- Cater for any two HUSB or WIFE links
local arrRefn = {}
for _, strTag in ipairs ({"~.HUSB[1]>";"~.WIFE[1]>";"~.HUSB[2]>";"~.WIFE[2]>";}) do
local ptrPart = fhGetItemPtr(ptrRec,strTag)
if ptrPart:IsNotNull() then
local strRefn = fhGetItemText(ptrPart,"~.REFN")
if #strRefn > 0 then
table.insert(arrRefn,strRefn)
if #arrRefn == 2 then break end
end
end
end
if #arrRefn == 2 then
tblRefn[arrRefn[1].."&"..arrRefn[2]] = ptrRec:Clone()
tblRefn[arrRefn[2].."&"..arrRefn[1]] = ptrRec:Clone()
end
ptrRec:MoveNext()
end
end
intCells = 0
for intRow = 1, #arrRows do
progbar.Message("Adding records for row "..intRow..", please wait")
progbar.Step()
intPeople = CreatePeople(intRow,intPeople)
if tblPeople[intRow] then
AddItems(intRow,"INDI",tblPeople)
end
intFamilies = CreateFamilies(intRow,intFamilies)
if tblFamilies[intRow] then
AddItems(intRow,"FAM",tblFamilies)
end
intRows = intRow
for _, _ in pairs (arrRows[intRow]) do intCells = intCells + 1 end
if progbar.Stop() then break end
end
progbar.Close()
local numEndTime = os.clock()
if intCells > 0 then
local strMessage = ""
strMessage = strMessage..intRows.." rows with "..intCells.." cells of information processed.\n\n"
strMessage = strMessage..intPeople.." people added,\n"
strMessage = strMessage..intFamilies.." families created,\n"
strMessage = strMessage..string.format("in ".."%.2f", numEndTime - numStartTime).." seconds.\n\n"
strMessage = strMessage.."Please check that all information has loaded as intended."
fhMessageBox(strMessage)
else
fhMessageBox("No fields were mapped. No data could be imported.")
end
end -- function ImportRecords()
--[[
@Function: CheckVersionInStore
@Author: Mike Tate
@Version: 1.3
@LastUpdated: 03 May 2022
@Description: Check plugin version against version in Plugin Store
@Parameter: Plugin name and version
@Returns: None
@Requires: lfs & luacom
@V1.3: Save and retrieve latest version in file;
@V1.2: Ensure the Plugin Data folder exists;
@V1.1: Monthly interval between checks; Report if Internet is inaccessible;
@V1.0: Initial version;
]]
function CheckVersionInStore(strPlugin,strVersion) -- Check if later Version available in Plugin Store
require "lfs"
require "luacom"
local function OpenFile(strFileName,strMode) -- Open File and return Handle
local fileHandle, strError = io.open(strFileName,strMode)
if fileHandle == nil then
error("\n Unable to open file in \""..strMode.."\" mode. \n "..strFileName.." \n "..strError.." \n")
end
return fileHandle
end -- local function OpenFile
local function SaveStringToFile(strString,strFileName) -- Save string to file
local fileHandle = OpenFile(strFileName,"w")
fileHandle:write(strString)
assert(fileHandle:close())
end -- local function SaveStringToFile
local function StrLoadFromFile(strFileName) -- Load string from file
local fileHandle = OpenFile(strFileName,"r")
local strContents = fileHandle:read("*all")
assert(fileHandle:close())
return strContents
end -- local function StrLoadFromFile
local function httpRequest(strRequest) -- Luacom http request protected by pcall() below
local http = luacom.CreateObject("winhttp.winhttprequest.5.1")
http:Open("GET",strRequest,false)
http:Send()
return http.Responsebody
end -- local function httpRequest
local function intVersion(strVersion) -- Convert version string to comparable integer
local intVersion = 0
local arrNumbers = {}
strVersion:gsub("(%d+)", function(strDigits) table.insert(arrNumbers,strDigits) end)
for i = 1, 5 do
intVersion = intVersion * 100 + tonumber(arrNumbers[i] or 0)
end
return intVersion
end -- local function intVersion
local strLatest = "0"
if strPlugin then
local strPath = fhGetContextInfo("CI_APP_DATA_FOLDER").."\\Plugin Data\\"
local strFile = strPath.."VersionInStore "..strPlugin..".dat"
local intTime = os.time() - 2600000 -- Time in seconds a month ago
local tblAttr, strError = lfs.attributes(strFile) -- Obtain file attributes
if not tblAttr or tblAttr.modification < intTime then -- File does not exist or was modified long ago
if lfs.attributes(strPath,"mode") ~= "directory" then
if not lfs.mkdir(strPath) then return end -- Ensure the Plugin Data folder exists
end
local strErrFile = strPath.."VersionInStoreInternetError.dat"
local strRequest ="http://www.family-historian.co.uk/lnk/checkpluginversion.php?name="..tostring(strPlugin)
local isOK, strReturn = pcall(httpRequest,strRequest)
if not isOK then -- Problem with Internet access
local intTime = os.time() - 36000 -- Time in seconds 10 hours ago
local tblAttr, strError = lfs.attributes(strErrFile) -- Obtain file attributes
if not tblAttr or tblAttr.modification < intTime then -- File does not exist or was modified long ago
fhMessageBox(strReturn.."\n The Internet appears to be inaccessible. ")
end
SaveStringToFile(strErrFile,strErrFile) -- Update file modified time
else
os.remove(strErrFile) -- Delete file if Internet is OK
if strReturn then
strLatest = strReturn:match("([%d%.]*),%d*") -- Version digits & dots then comma and Id digits
SaveStringToFile(strLatest,strFile) -- Update file modified time and save version
end
end
else
strLatest = StrLoadFromFile(strFile) -- Retrieve saved latest version
end
end
if intVersion(strLatest) > intVersion(strVersion or "0") then
fhMessageBox("Later Version "..strLatest.." of this Plugin is available from the Plugin Store.")
end
end -- function CheckVersionInStore
-- Main Code Section Starts Here --
CheckVersionInStore("Flexible CSV Importer",strVersion) -- Notify if later Version -- V1.7
PrepareGUI()
Source:Flexible-CSV-Importer-9.fh_lua