User: Do you know where macOS stores the plist file that it saves the user preferred Text Replacement data? It is pairs of text symbols that get replaced on the fly by macOS to a replacement string of glyphs, including all UTF-16 characters. If you don't know where the plist or similar file is stored in the user Library folder, can you think of a way to deduce its location. I tried amending the Text Replacements in Settings and filtering the files in ~/Library by .plist and todays date but that didn't reveal anything of relevance to me.
Assistant: On macOS, Text Replacement data is typically stored in a SQLite database rather than in a .plist file, which might explain why filtering for .plist files didn't help. The relevant database is usually found at:

~/Library/KeyboardServices/TextReplacements.db

You can inspect this file using SQLite commands. If you're familiar with the sqlite3 command-line tool, you could query this database to view the stored text replacement pairs.

To deduce the location of such files in the future, you might consider using macOS's fs_usage tool to monitor file system activity when you modify text replacements. This could help you trace which files are accessed or written to during that operation.

User: Where would I find macOS fs_usage tools?
Assistant: On macOS, the fs_usage tool is a built-in command-line utility, so you don't need to install anything to use it. It is located in /usr/sbin/. You can run it directly from the Terminal.

To use fs_usage, you can open the Terminal and type:

sudo fs_usage

This command requires sudo because it monitors system-wide file system activities, and elevated privileges are necessary for such tasks.

You can refine its output by filtering to show only file system activity for specific processes or directories. For example, if you want to monitor activity related to the System Preferences (where text replacements are managed), you can do something like:

sudo fs_usage | grep 'System Preferences'

To track specific file modifications while you are editing Text Replacements in System Preferences, keep the tool running and then apply the changes, watching for any relevant file operations in the output.

User: Does macOS have a SQL tool I can extract the data for this file with?
Assistant: Yes, macOS includes the sqlite3 command-line tool, which allows you to interact with SQLite databases like the TextReplacements.db file.

To extract data from the TextReplacements.db file, follow these steps:

1. Open Terminal.
2. Navigate to the location of the TextReplacements.db file:

   cd ~/Library/KeyboardServices/

3. Open the SQLite database:

   sqlite3 TextReplacements.db

4. Once you're inside the SQLite shell, you can list the tables in the database to see what data is available:

   .tables

5. To extract all the text replacement pairs, you can query the relevant table (assuming it's named something like replacements or text_replacements). For example:

   SELECT * FROM ZTEXTREPLACEMENTSENTRY;

   Replace ZTEXTREPLACEMENTSENTRY with the actual table name if different.

6. To exit the SQLite shell, type:

   .exit

This will allow you to explore the text replacement data stored in the database.

User: Can I just export the key name and text replacement string from the table using sqlite? I only need those two fields in the line pairs. As a JSON would be great but even as is or as CSV is ok, I can convert to JSON
Assistant: Yes, you can export specific fields (such as the key name and text replacement string) from the TextReplacements.db table using sqlite3, and you can export them in CSV format. Here’s how you can do that:

1. Open Terminal and navigate to the folder containing the TextReplacements.db file:

   cd ~/Library/KeyboardServices/

2. Open the SQLite database with sqlite3:

   sqlite3 TextReplacements.db

3. To export specific columns (let’s assume they are ZSHORTCUT for the key and ZPHRASE for the replacement string, but you'll need to adjust based on the actual column names), you can export the data as CSV using:

   .headers on
   .mode csv
   .output text_replacements.csv
   SELECT ZSHORTCUT, ZPHRASE FROM ZTEXTREPLACEMENTSENTRY;
   .exit

This will create a text_replacements.csv file in the current directory with two columns: the key name (shortcut) and the text replacement string.

If you'd prefer JSON, you can convert the CSV file to JSON using tools like jq or manually. Here's a simple way to convert the CSV to JSON using Python:

1. Install jq (if not installed, you can install it using Homebrew):

   brew install jq

2. Convert the CSV file to JSON using a script or directly in Terminal. Here's a basic approach using Python:

   python3 -c "
   import csv, json
   with open('text_replacements.csv') as f:
       reader = csv.DictReader(f)
       print(json.dumps(list(reader), indent=4))
   " > text_replacements.json

This will create a text_replacements.json file containing the key-value pairs.

User: These are the tables I have. Not sure we have the header for the text replacement here, but Key would be Z_PRIMARYKEY I am guessing.
ZTEXTREPLACEMENTENTRY  Z_METADATA             Z_PRIMARYKEY         
ZTRCLOUDKITSYNCSTATE   Z_MODELCACHE          
Assistant: From the tables you listed, it seems like the relevant table for text replacements is likely ZTEXTREPLACEMENTENTRY. You can inspect the structure of this table to see the columns it contains, and that should reveal which fields correspond to the shortcut (key) and the replacement text.

Here's how to check the structure of the ZTEXTREPLACEMENTENTRY table:

1. Open the SQLite database in Terminal:

   sqlite3 ~/Library/KeyboardServices/TextReplacements.db

2. To see the column names and types in the ZTEXTREPLACEMENTENTRY table, run:

   PRAGMA table_info(ZTEXTREPLACEMENTENTRY);

This command will list the columns in that table. Look for names that could correspond to the text replacement shortcut and the replacement phrase.

Common names could be:
- ZSHORTCUT (for the key you type)
- ZPHRASE (for the replacement string)

If these or similar fields appear, you can then proceed to export them. For example:

SELECT ZSHORTCUT, ZPHRASE FROM ZTEXTREPLACEMENTENTRY;

If no ZSHORTCUT or ZPHRASE fields exist, identify the most appropriate column names from the PRAGMA output and adjust the SELECT query accordingly.

Let me know if you need help interpreting the column names!

User: Results in
0|Z_PK|INTEGER|0||1
1|Z_ENT|INTEGER|0||0
2|Z_OPT|INTEGER|0||0
3|ZNEEDSSAVETOCLOUD|INTEGER|0||0
4|ZWASDELETED|INTEGER|0||0
5|ZTIMESTAMP|TIMESTAMP|0||0
6|ZPHRASE|VARCHAR|0||0
7|ZSHORTCUT|VARCHAR|0||0
8|ZUNIQUENAME|VARCHAR|0||0
9|ZREMOTERECORDINFO|BLOB|0||0
Assistant: Great! The relevant fields for exporting the text replacement data are:

- ZSHORTCUT: This would be the "key" or the text you type that triggers the replacement.
- ZPHRASE: This is the replacement string that gets substituted when the shortcut is typed.

Now that we know the column names, you can extract the data as a CSV or query it directly:

To export as CSV:

1. Open the database as before:

   sqlite3 ~/Library/KeyboardServices/TextReplacements.db

2. Run the following commands to output the ZSHORTCUT and ZPHRASE to a CSV file:

   .headers on
   .mode csv
   .output text_replacements.csv
   SELECT ZSHORTCUT, ZPHRASE FROM ZTEXTREPLACEMENTENTRY;
   .exit

This will create a text_replacements.csv file with the shortcuts and their corresponding replacement phrases.

Optional: Convert CSV to JSON

If you want to convert the CSV to JSON, you can either use a tool like jq or convert it with a Python one-liner as described earlier.

For Python:

python3 -c "
import csv, json
with open('text_replacements.csv') as f:
    reader = csv.DictReader(f)
    print(json.dumps(list(reader), indent=4))
" > text_replacements.json

This will create a text_replacements.json file containing the shortcut-replacement pairs.