Paste in a table

Status

Variants

Paste from the clipboard

Parse the system clipboard's contents as a table.

Read a table from in-memory objects

Take a table from data a program already holds, such as an Arrow table, a data frame or a dictionary of arrays.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

In OpenRefine, pasting is useful for pre-selecting a specific number of rows, or for pasting together rows from different places, rather than deleting unwanted rows later.^[2] In the Polars library, read_clipboard is useful for reading data copied from Excel or other similar spreadsheet software.^[4] In Polars, read_clipboard splits columns on a tab by default, while pandas.read_clipboard splits on one or more whitespace characters by default.^[4][5] When the copied cells hold values with spaces in them, name a tab separator rather than relying on a whitespace default. (Meridian's view) A pasted table has no address to read again, so save it to a file before building anything that will be rerun on it. (Meridian's view)

In DuckDB's Python client, only variables visible to Python code where sql() or execute() is called can be queried by name; setting python_scan_all_frames to true also looks in the functions that called it.^[6] In DuckDB's Python client, an object given a SQL name with register() takes precedence over a native DuckDB table or view of the same name, and both over a Python variable found by name.^[6] In DuckDB's Python client, an Apache Arrow RecordBatchReader can be read only once, so its data must be read into a table, for example with read_all(), to be scanned again.^[6] DuckDB scans Apache Arrow tables and Polars DataFrames on all threads, so a query that depends on row order without an ORDER BY, such as one using first(), can return different rows between runs.^[6] For a pandas DataFrame column of the object dtype, DuckDB picks the target type from a sample of rows, 1000 by default, set by the pandas_analyze_sample option.^[6] In DuckDB, an object column whose type is picked wrongly from its sample fails with Invalid Input Error: Failed to cast value, and the fix is a larger sample.^[6] Name the ordering whenever a result from a table held in memory depends on which row comes first. (Meridian's view)

The method

Applications communicate through the clipboard by providing either serialised representations of an object or a promise, and the sending and receiving applications negotiate the formats that can be transferred.^[3] The operating system and graphical user interface toolkit may convert between clipboard formats, for example from rich text to plain text and back.^[3] In pandas, read_clipboard reads text from the clipboard and passes it to read_csv, parsing it much as a comma-separated values (CSV) file is parsed.^[5] In Polars, read_clipboard likewise reads text from the clipboard and passes it to read_csv.^[4] In OpenRefine, pasted text is assumed to be tab-separated or comma-separated, and if a tab character is found the data is taken as tab-separated.^[2] In VisiData, the open-syspaste command, bound to gShift+P, loads a new table from the system clipboard, parsed as a filetype the user names, tab-separated values (tsv) by default.^[7]

DuckDB's replacement scan API registers a callback that is called when a query reads a table that does not exist in the catalogue, and the callback can put a table function in its place.^[8] DuckDB's Python client uses replacement scans to replace a query's read of a DataFrame's variable name with a table function that reads the DataFrame.^[1][6] Apache Arrow is designed to be a universal in-memory format for tabular, columnar data.^[9] The Arrow C data interface is a small, stable set of C definitions meant to allow zero-copy sharing of Arrow data between independent runtimes and components running in the same process.^[9] The Arrow PyCapsule interface defines standard methods, such as __arrow_c_stream__, so a Python library accepting Arrow data can call them instead of hardcoding support for specific Arrow producers.^[10] DuckDB can consume any object that implements the Arrow PyCapsule interface through an __arrow_c_stream__ method.^[6] In Polars, from_arrow builds a DataFrame from Arrow data with zero copy for the most part, and types Polars does not support may be cast to the closest supported type.^[11]

Compared with the alternatives

The Arrow C data interface is zero-copy by design, while the Arrow inter-process communication (IPC) format works across processes and machines.^[9] Sharing data between distinct processes, and persisting it to storage, are among the Arrow C data interface's stated non-goals.^[9] Reading a table a program already holds suits work inside one process; once the table must outlive the process or cross to another, write it to a file. (Meridian's view) In DuckDB's Python client, register() gives a DataFrame or Arrow object a SQL name, while CREATE TABLE AS SELECT makes a DuckDB table from the DataFrame's contents.^[6] DuckDB's VALUES clause specifies a fixed number of rows, and can stand alone, sit in a FROM clause or feed an INSERT INTO statement.^[12] For a handful of rows that belong with the query itself, a VALUES clause keeps the table in the SQL text, where pasting would leave no record of it. (Meridian's view)

Sources

1. [1]SQL on Pandas – DuckDBdocumentation
2. [2]Starting a project | OpenRefinedocumentation
3. [3]Clipboard (computing) - Wikipediasecondary source
4. [4]polars.read_clipboard — Polars documentationdocumentation
5. [5]pandas.read_clipboard — pandas 3.1.1 documentationdocumentation
6. [6]Data Ingestion – DuckDBdocumentation
7. [7]visidata/visidata/features/open_syspaste.py at 7609212ec2febafa9caba7183cbebfaf49627909 · saulpw/visidata · GitHubdocumentation
8. [8]Replacement Scans – DuckDBdocumentation
9. [9]The Arrow C data interface — Apache Arrow v26.0.1documentation
10. [10]The Arrow PyCapsule Interface — Apache Arrow v26.0.1documentation
11. [11]polars.from_arrow — Polars documentationdocumentation
12. [12]VALUES Clause – DuckDBdocumentation