Desktop App Documentation for Windows
Install and First Run
Download the installer from the desktop page and run it. The installer is digitally signed, and the desktop page publishes the certificate and the file size so you can check both before you install anything. Windows shows a warning on first run for any software that is not from the Microsoft Store. That warning is about the publisher, not about the file, and the signature is how you tell the difference between our installer and a copy someone else renamed.
The app opens with the demo dataset already loaded, so you can see what a result looks like before you open anything of your own. The seven modules run down the left side, and they unlock once a licence is entered. Work in the app is saved to a workbook in your Windows user profile as you go, and the app saves again when you close it, so a job interrupted by a restart does not lose everything.
Enter your licence in
My Account
before the paid modules will run. The licence id is in the purchase email, in the form
FLD-XXXX-XXXX-XXXX-XXXX, and it is paired with the email address you bought with.
The Data Grid and Ranges
Open a file with the Open button in the File menu. Commas, tab separated text and Excel workbooks are all read, and a workbook with several sheets imports every sheet with each one named. Files over 256 MB are refused with their size rather than attempting to load them, so split a larger file and open the parts.
Every module takes a range, and you give it one in three ways:
- Click a column header in the data grid to select that whole column
-
Type a range into the field, for example
A1:B200 - Select cells in the grid, which fills the field in for you
The grid shows the first 300 rows of the active sheet. The row count under the column picker is the count for the whole sheet rather than the part on screen, so it tells you the size of what you are working with. The status bar states what ran and what it found.
The area to the right of the controls has two tabs, Data and Result. Data is the grid. Result is the table a module produces, and it takes the full width of that area rather than sitting inside the form. A profiling table has eight columns and squeezing it into a form column leaves about 38 pixels for each one, which is too narrow to read. Pressing a button that produces a result switches to the Result tab for you, and changing module returns you to Data.
Profile and Insights
Profile reads a range and reports what is in it, so you can decide what to fix before you change anything.
| Control | What It Does |
|---|---|
| Range to profile | The cells to inspect. Defaults to the current selection. |
| Values in the top list | How many of the most frequent values to list, from 1 to 50. The default is 10. |
Run profile returns a table with one row per column: the detected type, how many cells are filled and empty, how many distinct values there are, how many duplicates, the average length, and any issues found. Each issue carries a severity tag, green for low, amber for medium and red for high, so the rows that need attention are visible without reading the counts. Below it you get the row, column and issue counts.
Every common issue offers a repair beside the statistics. Pressing it runs the fix for that column and writes the corrected values to the first free column on the right, with a header naming the source column and the repair. The originals stay exactly as they were, so the diagnosis and the fix sit side by side. Issues with no safe automatic fix, such as inconsistent lengths or punctuation in every value, are reported only.
Column groups
is the other half of this module. It groups values that are probably the same thing written differently, and for each group gives you the value, how many times it appears, how many case variants it has, and the near matches it found. This is where a column written as
ACME LTD,
Acme Ltd
and
Acme Limited
stops being three entries and becomes one group with three members.
Clean and Transform
Clean rewrites the values in a range so they are consistent, which is what makes the rest of the app work on them.
| Control | What It Does |
|---|---|
| Values to clean | The range to read. What happens to it depends on the write target below. |
| Stop list range | Optional. The words to act on, for the operations that need a list. |
| Operation | What to do to each value. There are sixteen, and the fields underneath change to match the one you picked. |
| Threshold | How close a value must be to an entry in the stop list to count as a match. |
| Where to write | Overwrite the column you selected, or write beside your data in the first free column on the right. Multi-column results, such as a split name, always write beside your data so they cannot overwrite a neighbour. |
The sixteen operations are:
- Remove stop words, using the words in your stop list range
- Remove listed punctuation, using the punctuation in your stop list range
-
Remove diacritics, turning
ZürichintoZurich - Extract domain, taking the domain out of an email address or a URL
- Extract path, taking the path out of a URL
- Trim and collapse spaces, removing leading and trailing spaces and turning every run of spaces inside the value into one
- Remove control characters, deleting the invisible codes that pasted data often carries while leaving tabs and line breaks alone
- Remove invisible characters, deleting zero width characters, the byte order mark and the soft hyphen
- Remove HTML tags and entities, stripping tags, decoding the five basic entities and keeping the words apart
- Convert letter case, to Proper Case, UPPERCASE or lowercase
- Find and replace text, replacing every occurrence of a literal piece of text
- Replace with a regular expression, using a pattern body with
$1groups. Test it on a small range first, because a pattern that never finishes will stall the run - Split a full name into parts, writing title, first, middle, last and suffix into five columns. A value with a comma reads surname first, and a company name is refused rather than split into wrong parts
- Standardise a phone number, keeping a leading plus, converting a leading 00 and moving an extension into its own column
- Standardise an email address, removing brackets and lowercasing the domain while leaving the local part alone
- Convert text to numbers, reading a thousands separator and a decimal point in the form the profiler detects, and refusing anything else rather than guessing
Removing diacritics is the one that earns its place most often. It is the difference between
Globex
and
Globex
not matching. When the target is set to write beside your data, the original column stays exactly as it was and the cleaned column carries a header naming the operation, so the pair can be compared before anything else runs.
Match and Dedupe
This module is the core of the app. It finds records in one range that are the same entity as records in another, on a scale of similarity rather than on an exact key, and then either merges them or removes the duplicates.
| Control | What It Does |
|---|---|
| Data range | The records to search, usually the ones with the problem. |
| Master range to match against | The records that hold the correct values, usually your existing customer list. |
| Key columns | Which column or columns identify a record. A name, or a name and a postcode. |
| Match threshold | How similar two records must be to count as the same, from 0 to 1. The default is 0.85, and the field steps by 0.01. |
| Merge and purge keeps | Which side wins when a match is found. |
| Dedupe in place keeps | Which occurrence survives when a duplicate group is collapsed. |
Five buttons run it, and they differ in what they change:
- Find duplicates reports the groups and changes nothing. Run this first. It is the only one of the five that cannot damage your data, and it tells you how big the problem is before you commit to a threshold.
- Explain takes one record and shows you its nearest matches with the score for each, so you can see why a threshold behaves the way it does rather than guessing at it.
- Golden records picks the best record from each group and shows you the choice, without writing it yet.
- Merge and purge writes the master values onto your data range and removes the rows that were merged away.
- Remove duplicates collapses the duplicate groups in place, keeping the occurrence you chose.
On the threshold: 0.85 is a reasonable starting point and it is the wrong one to keep for every column.
Acme Ltd
and
Acme Limited
should match, and at 0.9 they may not. Run Find duplicates, look at the scores, and lower it until you are seeing the matches you expect. Use
Explain
on anything surprising before you let the run write.
Data Verification
Verification checks a list against a master list and tells you what is wrong, which is different from cleaning it.
| Control | What It Does |
|---|---|
| Values to verify | The list to check. |
| Master range to match against | The list that holds the correct values. |
| Entity type | What the values are, which decides how strictly they are checked. Four types. |
| Fields to add | Which values from the master list to bring across when a match is found. |
The four entity types are Country, US state, Company suffix and Top level domain. The type matters because it sets how much variation is tolerated. A company name can be written
Acme Ltd,
Acme Limited
or
Acme, Ltd.
and still be the same company, so matching has to be forgiving. A country cannot, so the same matching applied to a country column quietly accepts errors that look like typos and are not.
Verify and enrich reports what it found and adds the fields you asked for. Cross match compares the two lists against each other without adding anything, which tells you how much of your data is trustworthy before you rely on it.
Automation
Automation runs a job on a schedule without you, on the same computer, against the same workbook.
| Control | What It Does |
|---|---|
| Minutes or Hours | How often the job runs, and which module it belongs to. |
| Hour and Minute | When it runs, on a repeating schedule. |
| Repeat | How many times, up to 3, or continuously. |
| Run on startup if missed | Whether a run that was missed while the app was closed happens when it next opens. |
| Job target | Which of your scheduled jobs to point the schedule at. |
| Data range | The range the job works on. |
Each scheduled job in the list has its own controls:
- Pause stops the timer and keeps the job and its run history. Resume starts it again and keeps the remaining wait rather than restarting the interval
- Run now runs it immediately without waiting for the schedule
- Stop removes the job entirely
- Clear all removes every scheduled job
A job that runs while you are asleep is only possible while the app is running, because a scheduled job is a task on your computer rather than a service somewhere. Run on startup if missed is what covers a machine that was off. If you need something to run whether or not your computer is on, that is what the Sheets add-on does, and the automation documentation for the add-on covers it.
Import from a Source
Import from a source pulls a table out of an online system or a database and opens it as a normal table in the workbook. Every other module then works on it exactly as it would on a file you opened, and the table can be saved to a file afterwards like any other.
Four sources are supported: Google Sheets, Salesforce, Airtable and Database.
| Control | What It Does |
|---|---|
| Source | Chooses Google Sheets, Salesforce, Airtable or Database. The fields underneath change to match the chosen source. |
| Status | Shows which sources are connected on this machine. |
| Connect | Signs in. Google Sheets and Salesforce open your own browser and ask you to approve access. Airtable asks for a personal access token instead. Database opens the connection you described, using the typed password or a saved one. |
| Disconnect | Removes the stored sign-in for the chosen source. |
| Save password | Stores the typed database password under the connection name, encrypted with the Windows key store. A saved password is never shown back, only whether one is stored. |
| Forget password | Deletes the saved password for the named connection. |
| List tables |
Lists the tables in an Airtable base. The token needs the
schema.bases:read
scope for this.
|
| Pull data | Fetches the table and opens it in the workbook, then shows the first five rows and the row and column counts. |
Google Sheets
Enter the spreadsheet id from the spreadsheet's url, and a range in A1 notation. The default range is
A1:Z10000, which reads the used area of the first sheet. The sign-in asks for read-only access to spreadsheets, so the token this creates cannot change anything in your Google account.
Google requires every application that signs in to be registered before it can do so, which is why a client id is needed in Settings. Create a Google Cloud project, enable the Google Sheets API, set up the OAuth consent screen and create an OAuth client of type Desktop app, then paste its client id into Settings. Until a client id is entered the connector reports that it is not configured. The app does not ship one, and no client secret is used or stored.
Salesforce
Enter a SOQL query, for example
SELECT Id, Name, BillingCity FROM Account. Each page holds at most 2000 records and the remaining pages are fetched automatically. The sign-in needs a connected app with the API and refresh token scopes enabled, whose consumer key goes into Settings. A sandbox org is enough to try this; no external review is needed.
Airtable
Paste a personal access token with the
data.records:read
scope, the base id which begins
app, and the table name or id. Pages hold up to 100 records and are fetched in turn. Airtable omits empty fields from its responses, so those cells arrive empty.
Database
Choose the database type first, because the fields change to match it. SQLite asks for the database file alone. PostgreSQL, MySQL and SQL Server ask for host, port, database, user and password, with the standard port filled in. Oracle asks for a connect string, user and password. Then enter a single SELECT query, for example
SELECT * FROM customers. Only one SELECT statement is accepted and anything else is refused, so the connector can never write to your database. SQLite opens the file read only.
A database password can be left empty for SQLite, typed each time, or saved under a name you give the connection. A saved password is encrypted with the Windows key store before it is written and is never shown back to you, only whether one is stored. SQL Server sign-in is by user name and password; Windows integrated authentication is not supported by the driver this build uses.
Where the sign-in is stored: tokens are written to your Windows user profile alongside the rest of the app's data, and never leave the machine.
Nothing is sent to us. If you would rather not keep a sign-in, press Disconnect, which removes it.
Writing back
Write back sends your cleaned values to the source again, for Google Sheets, Salesforce and Airtable. A database is read only and offers no write back; its panel hides those controls rather than refusing them.
It updates records that already exist and nothing else: no record is created and none is deleted. You name the column that identifies a record, usually an id or an email address, and tick the columns to write. The app then matches each local row to a record on that column and writes only the rows that matched.
Because this changes data outside the app, the first press is always Preview write back. It reports how many rows matched, how many were skipped, and shows the first ten changes without sending anything. Apply write back is only live after a preview, and the button then reads Confirm write back, so a write cannot happen by one press or by accident. Every write is recorded on the activity list in My Account.
| Control | What It Does |
|---|---|
| Identify records by | The local column whose value matches the source, such as Id or Email. |
| Columns to write | Which columns to send. Each one is written under its own name in the source, because the pull keeps the source field names as the column headings. |
| Preview write back | Plans the change and writes nothing. |
| Apply write back | Makes the planned changes, then reports how many were written, skipped and rejected. |
| Undo last write back | Puts the values that were there before the last applied write back. Like the write itself it asks for a second press, and the list above it shows what each applied write back changed. |
Undo keeps the real previous values, captured while the records were being read, rather than reconstructing them later. A field that was empty before the write is restored as empty. An undo that has been applied is dropped from the list, so the same write cannot be rolled back twice.
A write back counts only the records the service confirmed. If Airtable or Salesforce reports a record as rejected, that record is named in the result and it is left out of the undo record, because it never changed. A write that stops part way through still records an undo for the records that did change, so those can be rolled back on their own.
The write back addresses the cells or fields the pulled table actually occupies. A Google Sheets table pulled from a range that does not begin at column A, or from a named sheet, is written back to that sheet and those columns rather than to the top left of the first sheet, and columns that are not next to each other are written as separate requests so a column between two written ones is never overwritten.
Each pull remembers where its table came from. When you open a pulled table and go back to Import, the panel says which source it belongs to and fills the spreadsheet id, the range, the object or the base and table in for you, so a write back does not need an id typed a second time. Only the locator is kept, never a token.
Permissions differ between the three. Airtable needs a token with the
data.records:write
scope. Salesforce writes through the same sign-in, which already carries the API scope. Google Sheets needs a broader permission than reading alone: its
values.update
resource accepts only the full spreadsheet scope, so a Google sign-in created for write back can change any spreadsheet that account can open. Reconnect Google Sheets after this release, because a token issued earlier for reading cannot write.
My Account
My Account holds the licence, the plans, the update check and the list of computers the licence is active on. It is grouped into Status, Activate, Plans, Updates and This device.
| Control | What It Does |
|---|---|
| Licence id |
From the purchase email, in the form
FLD-XXXX-XXXX-XXXX-XXXX.
|
| Account email | The address the licence was bought with. It has to match, which is what stops a licence id that leaks being usable by whoever found it. |
| Activate | Enters the two values and unlocks the paid modules on this computer. |
| Refresh | Rechecks the licence against the service and shows the current state. |
| Check integrity | Verifies the engine files have not been altered, and says so plainly either way. |
| Check for updates | Looks for a newer release and tells you before it downloads anything. |
| Release device | Frees this seat so the licence can be activated on a different computer. |
The version number and the integrity result are both in this panel, and both are worth including if you contact support. The integrity check is the one that matters: it compares the engine files against the hashes the app was built with, so a clean result is arithmetic rather than a promise.
To move to a new computer, use Release device on the old one first. One licence covers one seat on one computer, so the seat has to be free before the new machine can take it. Releasing does not cancel the subscription, it only detaches the machine.
Licences Are Bound to Your Device
A desktop licence is tied to the machine rather than to your account. The binding combines the machine GUID, the disk volume serial, the processor identifier, your Windows account name and your profile path, hashed together. A licence id that leaks therefore cannot be used on someone else's computer, and a second Windows account on the same machine does not inherit the licence.
The cost of that choice is that a licence stops matching if you reinstall Windows or rename your account, because both change the binding. You enter the licence id again in My Account and you are working on the same licence the same day. Your other devices are unaffected, and a reinstallation is not a cancellation.
We would rather you know this before you rebuild a machine than discover it afterwards.
The licence is verified on your own machine, so nothing about it depends on a server answering at the moment you need it. It does need to hear from us once every seven days, and the app checks by itself about once a day, so that happens without you doing anything. If the app cannot reach us for seven days running, the paid modules pause and scheduled jobs are skipped, and they resume on their normal schedule once it can check again. My Account shows the days remaining.
Your work is never deleted and no file of yours is held back because a server is unreachable.
Reporting a Problem
If something behaves in a way you cannot account for, tell us through the contact page. Three things make a report answerable rather than a guessing game:
- The version number, shown in My Account under Updates
- The result of Check integrity in the same panel
- What you expected and what happened instead, with a row or two of the data that caused it if you can share it
The app writes a log file at
%APPDATA%\flookup-desktop\logs\app.log, capped at 1 MB with the oldest lines dropped. It records what the app did and never what your data contained, so it is safe to attach. Type
%APPDATA%\flookup-desktop
into Explorer to reach it.
If a signature check fails, or a hash does not match, do not run the file. That is not a licensing problem and support cannot help with it, because the file is not the one we published. The desktop page has the certificate and the size to compare against.
Frequently Asked Questions
Does the desktop app send my data anywhere?
No. Files you open, the columns you select and the results you produce are written to a workbook in your own user profile. Left alone the app makes two network calls, a licence check and an update check, and neither carries the contents of a file. When you connect a source, the app also talks directly to that provider or database server for the records you select, and to nothing else. The desktop page lists every call in full.
Where does the app keep my work?
In a workbook in your Windows user profile, in the application data directory. The app writes a log file capped at 1 MB alongside it, which records what the app did and never what your data contained.
Why does my licence stop matching after I reinstall Windows?
A licence is bound to the machine rather than to your account, so it is combined with hardware identifiers and your Windows account name and profile path. Reinstalling Windows or renaming your account changes those, so you enter the licence id again in My Account. Your other devices are unaffected.
How do I move a licence to a new computer?
Open My Account and use Release device to free the seat on the machine you no longer use, then enter the licence id on the new machine. One licence covers one seat on one computer, so the seat has to be released before the new machine can use it.