Spreadsheets

Import and export of posts of any type, users and the shop's products and orders as CSV, Excel or OpenDocument spreadsheets.

kitloom/spreadsheets · admins: kitloom/spreadsheets-filament, kitloom/spreadsheets-nova

Video lessons

Recorded in the Filament admin; the Nova admin works the same way.

• Export a spreadsheet
• Import a spreadsheet

History of imports and exports

Import and export of your site's records as spreadsheets — CSV, Excel (XLSX) or OpenDocument (ODS). A supplier's price list goes into the catalogue, the catalogue goes out to a marketplace or an accountant, users come over from another system, and posts of any type are edited in a spreadsheet and put back.

What you can import and export — a sheet — depends on the modules you have:

Sheet	What a row is
Posts, Pages, and every other post type of the site	one record of the type
Users	one user
Products, Orders, Order lines	with the shop: products in WooCommerce's own CSV columns, so a WooCommerce export imports as it is; orders and their lines are export only

XLS files from old Excel versions are not read: save the file as XLSX or CSV first.

Importing a file, in two steps

Import a spreadsheet

1. Upload. Choose the sheet and the file. For a CSV you may also choose its encoding (a file saved by Excel on a Windows computer in Eastern Europe is often Windows-1251) and its delimiter — left empty, the delimiter is found from the first line. The first row of the file must name the columns; a name that is empty or appears twice is refused right away.

2. Map the columns and run. Every column of your file is shown with the first rows of the file and the column of the sheet it goes into. The choice is filled in for you: Regular price, regular_price and Regular Price? all match the sheet's Regular price, and the names WordPress and WooCommerce use (post_title, Short description) match too. Change what is wrong, or choose nothing for a column you do not want. A column the sheet requires (marked as such) must be chosen before the run can start.

Then three choices:

• Update existing records. Each row is looked up first — by its ID, then by what else names it (the slug of a post, the SKU of a product, the email of a user). With this off, a row whose record is already on the site is skipped and the log says so: Row 7: product 12 exists; turn on "Update existing" to change it. With it on, the record is changed.
• Empty cells clear values. Off by default: an empty cell changes nothing, and a record keeps its value. With it on, an empty cell in a column you chose empties the record's value — the sale price of a sale that has ended, the tags of a product, an excerpt. Choose only the columns you mean to change, since every empty cell in them counts. Some values cannot be empty: an existing record's status, author, date or comments, a product's type, visibility, tax and stock status, a variation's product, an existing user's email, username, display name or role, and the title of a new post or the name of a new product. A row that empties one of them fails, and the log names the column. A new record takes its usual defaults for the rest (a draft, the default role, a username made from the email). The ID and a post's slug only find the record: empty, they mean the row names it otherwise, or is new. A product's Attribute N columns are read together, and empty ones leave its attributes as they are, as in WooCommerce.
• Dry run. Everything is read and checked, nothing is written. The counts tell you how many rows would be created, updated, skipped or fail — run it first on a big file.

The import runs in the background; its page in History fills in as it goes.

What a column takes

• Text as it is. An empty cell changes nothing: a record's value stays as it was — unless the run has Empty cells clear values on, when it empties the value.
• Numbers with a point or a comma: 12.50, 12,50.
• Yes/no: 1/0, yes/no, true/false.
• Dates in any common form: 2024-05-07, 2024-05-07 10:20.
• Categories, tags and other taxonomies: names separated by |; a child category under its parent as Travel > Rivers. A name that is not on the site yet is added.
• Status of a post: publish, draft, pending, private or future. Without a status column a new post is a draft — publish the lot when you have looked at it, or add the column.
• Author: the user's login or email address. A name not found leaves the post with you (and the log says so).
• Featured image: the address of an image, or its ID in the media library. An address is fetched into the media library (it must be a public address of an image file).
• Custom fields — field:isbn — when custom fields are installed: checked as the edit form checks them. A list or a group of values is written as JSON, as the export writes it.
• Other meta — meta:shelf — any key that does not start with _. Keys starting with _ belong to WordPress and the modules and are never read from a file.
• Users never have a password in a sheet, in either direction. A new user gets an email inviting them to choose one. Their address is theirs: an import never changes an existing user's email.

The easiest way to get the columns right: export a few records first, and use that file as the template.

When a row is wrong

A row that cannot go in — a number that is not a number, a status that does not exist, a role you may not give, a field the form would refuse — fails on its own: nothing of it is written, the log says why (Row 14: The column "Stock" takes a whole number.), and the other rows go in. Fix the rows in your file and import it again with Update existing records on: the rows that went in the first time are found and left as they are, the fixed ones go in.

A row never deletes or trashes anything.

What you may import is what you may do in the admin: an import runs as you. An author's rows can only make and change their own posts; a user who may not give the administrator role cannot give it from a file either.

Exporting

Export a spreadsheet

Choose the sheet, narrow it with its filters (a status, a date range, a category, a role), choose CSV, XLSX or ODS, and the file downloads. A CSV is UTF-8 with the mark Excel looks for, so its letters open right. The first row has the column names an import reads back, so an exported file can be edited and imported again unchanged.

Every export is written down in History too.

With an AI agent

With kitloom/ai, an agent working on your site can do the same in words — "load this price list", "export last month's orders":

• sheet_preview takes a file at a public address, matches its columns and does a dry run: what would be created, updated, skipped and why;
• sheet_import runs it, with the agent's choice of columns if the guess was wrong, and the same three choices (update_existing, clear_empty, dry_run) — ask it to clear empty cells in words, it does not by itself;
• sheet_runs_list and sheet_run_get read the history and the logs;
• sheet_export writes a file and gives back a download link that works for an hour.

The agent acts as the user it is connected for, with the same permissions. Fetching a file from an address needs the media library.

For developers

• A module adds a sheet with a Kitloom\Spreadsheets\Contracts\Sheet tagged Sheet::TAG — its key, label, permissions, columns, filters, how it finds and writes a row (through the writers, as the actor). A tagged sheet with the key of a built-in one (post:product) replaces it. A sheet taking columns beyond its list (meta:<key>) also implements Contracts\OpenColumns; a note for the log about a cell left out goes through Import\RowNotes; a row that is not for this site at all (a grouped product where the shop sells none) throws Import\RowSkipped and counts as skipped, not failed.
• Runs is the service both admins, the console and the agents call: upload(), start(), preview(), export(), stream(), downloadUrl().
• Commands: kitloom:sheets [sheet], kitloom:sheet-import {sheet} {file} --map="File column=key" --update --clear-empty --dry-run --encoding= --delimiter= --as=user, kitloom:sheet-export {sheet} {file} --filter=key=value --as=user. Without --as a command is the system and may do anything.
• Events\SheetRunFinished follows every run.
• Configuration: config/kitloom-spreadsheets.php — the disk, the upload limit, how long export files are kept (exports.keep_hours, 24), the encodings offered, the agent tools and the admin switches.