Bulk add people to CRM

Tricks & Techniques
1

I have a /persons page where in I create new pages for each person I have contact with. So /persons/mike and /persons/jake . All have consistent frontmatter variables like contactdetails.

Now I have a table of persons in Excel that I want to bulk import. I'd like to have a page for each person, using the template that I already own (template/person). It's about 80 persons, so I don't want to add each page manually.

How can I achieve this? And is having a subpage for each person the most ideal way to roll-up and query those persons based on their parameters (like who's birthday is coming or who I've not walked with lately)?

1 Like
2

I did the same a while back but I used a separate python script to do this for my use case ( was quicker and easier for me).

My workflow was:
I exported the Excel sheet as a CSV file, and wrote a code in python to create the fronttmatter/object attributes based on a given format/template (not an SB Template though).

Back then (2 years ago) i wasn't too proefficient in lua, and lua wasn't even widely available back then in Silverbullet(correct me if i'm wrong).

What you try to achieve is definitely possible to be done in LUA or other languages like python.

What i would recommend is to try to create a simple lua script which makes a lua table from the CSV string data, then for each row of this table create a page in a given folder with the given name (variable from the CSV) based on a template defined in the lua script.

LLM's can help you write the code, but be aware to give it enouogh context, and also sometimes pointing the LLM's to the Silverbullet API's you want to use gives a much better result. If you could share the exact columns from your excel table(or CSV file) and the page template for your contacts we could come up with a good prompt or even the lua script itself to do this.

My contact sheet isn't that complex as I read between your lines, but if you have the code draft it's scalable to as many attributes/information as you need.

Here is an example of my own setup.

I used separate codeblocks with #persons for my list as oposite to your use case 1 contact/page, but this doesn't really matter if you have your contacts on one page or on separate ones, as long as they are indexed.

image
image609×939 39.2 KB

and here is also my birthday list from the example above:

image
image838×400 27.6 KB

2 Likes
3

Thats inspiring @Mr.Red ! I actually like the custom codeblocks per person on one page. Easier to manage.

How do I query those after I made them? I've tried the code below, but I miss the context to google my way through. Is "person" now a custom codeblock that I refer to?

${query[[
from index.tag "persons"
select{ Naam="[["..name.."]]", Functie=functie}
]]}

1 Like
4

I've got it working now! This is cool, but inline, within the page I am unable to prefill the persons with [[persons/Henk]] sadly, so I'm back to suppages per person. Thanks for the help!

1 Like
5

I do this for the query List:

${query[[from index.tag "persons"
order by daysTilBirthday(birthday) 
select {
  Emoji = emoji or "",
  Name = "[[" .. ref .. "|" .. name .. "]]",
  Birthday = birthday,
  Age = calculateAge(birthday),
  Days = daysTilBirthday(birthday)
}
limit 5
]]}

And here are my two functions for: DaysTilBirthday() and calculateAge():

Days Until Birthday

function daysTilBirthday(birthday)
    -- Validate input
    if not birthday or type(birthday) ~= "string" then
        return "NA"
    end

    -- Parse the input date string YYYY-MM-DD
    local year, month, day = birthday:match("(%d+)%-(%d+)%-(%d+)")
    if not year then
        return "NA"
    end

    year, month, day = tonumber(year), tonumber(month), tonumber(day)
    if not year or not month or not day then
        return "NA"
    end

    -- Today's date
    local today = os.date("*t")

    -- Birthday for this year
    local birthdayThisYear = {year = today.year, month = month, day = day}
    local birthdayTime = os.time(birthdayThisYear)
    local todayTime = os.time{year=today.year, month=today.month, day=today.day}

    -- If birthday is today
    if todayTime == birthdayTime then
        return 0
    end

    -- If birthday has already passed this year, calculate for next year
    if birthdayTime < todayTime then
        birthdayThisYear.year = today.year + 1
        birthdayTime = os.time(birthdayThisYear)
    end

    -- Difference in seconds, convert to days
    local diffSeconds = birthdayTime - todayTime
    local diffDays = math.floor(diffSeconds / (24 * 60 * 60))

    return diffDays
end

Calculate Age

function calculateAge(birthday)
    local y, m, d = birthday:match("(%d+)%-(%d+)%-(%d+)")
    if not y then return "NA" end

    y, m, d = tonumber(y), tonumber(m), tonumber(d)
    local now = os.time()
    local today = os.date("*t", now)

    local age = today.year - y
    local thisBirthday = os.time{year = today.year, month = m, day = d}

    if now < thisBirthday then
        age = age - 1
    end

    local lastBirthday = now < thisBirthday and os.time{year = today.year - 1, month = m, day = d} or thisBirthday
    local daysSince = (now - lastBirthday) / 86400

    if daysSince >= 183 then
        age = age + 1
    end

    return age
end
2 Likes
6

what do you mean by :

to prefill the persons with [[persons/Henk]]

If you use this synthax with the hashtag, that is how you index an object. Object documentation:

```#person
name: Mary
birthday: "2000-02-14"
emoji: 🍪 
```

this will be indexed as an object, and you can use them however you want.

2 Likes
7

Here you go I made both variants for reference and might be useful for others too.

  1. make sure your exported CSV is formatted (your values are all quoted, and use , as separator) as the example below, of course with your own fields and values:
"First Name","Middle Name","Last Name","Organization Name","Organization Title","Organization Department","Birthday","Photo","E-mail 1 - Label","E-mail 1 - Value","E-mail 2 - Label","E-mail 2 - Value","E-mail 3 - Label","E-mail 3 - Value","Phone 1 - Label","Phone 1 - Value","Phone 2 - Label","Phone 2 - Value","Phone 3 - Label","Address 1 - Label","Address 1 - Formatted","Website 1 - Label","Website 1 - Value","Website 2 - Label","Website 2 - Value"
"John","Michael","Smith","ABC Inc","CFO","Finance","1984-01-01","example.jpg","Home","[email protected]","Work","[email protected]",,,"Home","0123-456-789","Work","0987-654-321",,,"3rd Street 69, New York City, USA","Home","home.example.com","Work","work.example.com"
  1. replace the example CSV in the code with your actual CSV
  2. then run the command
    Convert Contacts From CSV
    or
    Create Contacts Pages From CSV

space-lua scripts:

Create Contacts Pages With Frontmatter Metadate From CSV and save them as page: persons/lastName_firstName

local contactsCSV = [["First Name","Middle Name","Last Name","Organization Name","Organization Title","Organization Department","Birthday","Photo","E-mail 1 - Label","E-mail 1 - Value","E-mail 2 - Label","E-mail 2 - Value","E-mail 3 - Label","E-mail 3 - Value","Phone 1 - Label","Phone 1 - Value","Phone 2 - Label","Phone 2 - Value","Phone 3 - Label","Address 1 - Label","Address 1 - Formatted","Website 1 - Label","Website 1 - Value","Website 2 - Label","Website 2 - Value"
"John","Michael","Smith","ABC Inc","CFO","Finance","1984-01-01","example.jpg","Home","[email protected]","Work","[email protected]",,,"Home","0123-456-789","Work","0987-654-321",,,"3rd Street 69, New York City, USA","Home","home.example.com","Work","work.example.com"
]]

function parseCSV(text)
    local res = {}
    local pos = 1
    while pos <= #text do
        local line = {}
        while true do
            local next_char = text:sub(pos, pos)
            local val = ""
            if next_char == '"' then
                -- Handle quoted fields
                local end_quote = text:find('"', pos + 1)
                while end_quote and text:sub(end_quote + 1, end_quote + 1) == '"' do
                    end_quote = text:find('"', end_quote + 2)
                end
                val = text:sub(pos + 1, end_quote - 1):gsub('""', '"')
                pos = end_quote + 1
            else
                -- Handle unquoted fields
                local end_val = text:find('[,\r\n]', pos) or (#text + 1)
                val = text:sub(pos, end_val - 1)
                pos = end_val
            end
            table.insert(line, val)
            if text:sub(pos, pos) == "," then
                pos = pos + 1
            else
                break
            end
        end
        if #line > 0 then table.insert(res, line) end
        if text:sub(pos, pos) == "\r" then pos = pos + 1 end
        if text:sub(pos, pos) == "\n" then pos = pos + 1 end
    end
    return res
end

function createContactsPages()

    local rows = parseCSV(contactsCSV)
    if #rows < 2 then return end

    local headers = rows[1]

    for i = 2, #rows do
        local r = rows[i]
        local frontmatter = "---\n"
        local firstName = ""
        local lastName = ""
        
        for j = 1, #headers do
            local label = headers[j] or "Unknown"
            local value = r[j] or ""
            -- Only add the field if the value isn't empty, to keep it clean
            if value ~= "" then
                -- 1. Remove all spaces from the labels
                local cleanLabel = label:gsub("%s+", "")
                      cleanLabel = cleanLabel:gsub("-", "")
                
                -- Capture names for page title logic
                if cleanLabel == "FirstName" then firstName = value end
                if cleanLabel == "LastName" then lastName = value end

                -- 2. Remove all new lines from within the same field value (replaced with comma-space)
                -- 3. Add the field values between quotes
                local cleanValue = value:gsub("[\r\n]+", ", ")
                      cleanValue = cleanValue:gsub('"', '\\"')
                frontmatter = frontmatter .. cleanLabel .. ": \"" .. cleanValue .. "\"\n"
            end
        end
        
        frontmatter = frontmatter .. "---"
        
        -- Create the page for the person
        local pagePath = "persons/" .. lastName .. "_" .. firstName
        space.writePage(pagePath, frontmatter)
    end

    editor.flashNotification("Conversion complete. Pages created in person/ directory.", "info")
end

command.define {
  name = "Create Contacts Pages From CSV",
  run = function()
   createContactsPages()
  end
}

Convert Contacts From CSV to Objects with #person tag


    local contactsCSV = [["First Name","Middle Name","Last Name","Organization Name","Organization Title","Organization Department","Birthday","Photo","E-mail 1 - Label","E-mail 1 - Value","E-mail 2 - Label","E-mail 2 - Value","E-mail 3 - Label","E-mail 3 - Value","Phone 1 - Label","Phone 1 - Value","Phone 2 - Label","Phone 2 - Value","Phone 3 - Label","Address 1 - Label","Address 1 - Formatted","Website 1 - Label","Website 1 - Value","Website 2 - Label","Website 2 - Value"
"John","Michael","Smith","ABC Inc","CFO","Finance","1984-01-01","example.jpg","Home","[email protected]","Work","[email protected]",,,"Home","0123-456-789","Work","0987-654-321",,,"3rd Street 69, New York City, USA","Home","home.example.com","Work","work.example.com"
]]

function parseCSV(text)
    local res = {}
    local pos = 1
    while pos <= #text do
        local line = {}
        while true do
            local next_char = text:sub(pos, pos)
            local val = ""
            if next_char == '"' then
                -- Handle quoted fields
                local end_quote = text:find('"', pos + 1)
                while end_quote and text:sub(end_quote + 1, end_quote + 1) == '"' do
                    end_quote = text:find('"', end_quote + 2)
                end
                val = text:sub(pos + 1, end_quote - 1):gsub('""', '"')
                pos = end_quote + 1
            else
                -- Handle unquoted fields
                local end_val = text:find('[,\r\n]', pos) or (#text + 1)
                val = text:sub(pos, end_val - 1)
                pos = end_val
            end
            table.insert(line, val)
            if text:sub(pos, pos) == "," then
                pos = pos + 1
            else
                break
            end
        end
        if #line > 0 then table.insert(res, line) end
        if text:sub(pos, pos) == "\r" then pos = pos + 1 end
        if text:sub(pos, pos) == "\n" then pos = pos + 1 end
    end
    return res
end

function convertContactsWithLabels()

    local rows = parseCSV(contactsCSV)
    local result = ""
    if #rows < 2 then return end

    local headers = rows[1]
    local result = ""

    for i = 2, #rows do
        local r = rows[i]
        local block = "```#person\n"
        
        for j = 1, #headers do
            local label = headers[j] or "Unknown"
            local value = r[j] or ""
            -- Only add the field if the value isn't empty, to keep it clean
            if value ~= "" then
                -- 1. Remove all spaces from the labels
                local cleanLabel = label:gsub("%s+", "")
                      cleanLabel = cleanLabel:gsub("-", "")
                -- 2. Remove all new lines from within the same field value (replaced with comma-space)
                -- 3. Add the field values between quotes
                local cleanValue = value:gsub("[\r\n]+", ", ")
                      cleanValue = cleanValue:gsub('"', '\\"')
                block = block .. cleanLabel .. ": \"" .. cleanValue .. "\"\n"
            end
        end
        
        block = block .. "```\n\n"
        result = result .. block
    end

    editor.insertAtCursor(result)
    editor.flashNotification("Conversion complete, labels included.", "info")
end

command.define {
  name = "Convert Contacts From CSV",
  run = function()
   convertContactsWithLabels()
  end
}
1 Like
8

Thanks @Mr.Red ! You're inspiring. I'll continue fiddling with my setup and will definitely try your code. I'm just getting started, but this opens up tons of possibilities.

2 Likes