CSV dashboard: query an uploaded CSV file with SQL

How a CSV becomes something you can query

arcodash does not query the file in place. At the first query it reads the whole CSV and builds a SQLite table from it, then runs your SQL against that table. So the dialect is SQLite, and the table is named after the file without its extension: orders.csv becomes orders.

The first row must be the header. An empty file is rejected, and so is a file where the header names collide, because two columns with one name cannot be created. Rows must have the same field count as the header. The upload accepts up to 25 MB, and the form previews the first 20 rows so you can see the parse before saving. The delimiter field defaults to a comma and takes any single ASCII character. Field setup is covered in Connectors overview.

A file whose name has dashes or spaces gives a table name that needs quotes. orders-2026-10.csv is queried as "orders-2026-10". Name the file with underscores and you avoid it.

Types are guessed per value

The columns have no declared type. Each cell is typed on its own as it is loaded:

• Empty becomes NULL.
• A whole number becomes an integer.
• A decimal becomes a real. Trailing zeros are fine, so 10.50 is read as 10.5.
• Everything else stays text. That includes 01234, +5, 1e3, NaN and $1,200.

The rule is that a value is a number only if writing it back gives the same text. That protects zip codes and IDs with leading zeros, and it means one bad cell turns up as text instead of a silent change. Dates are always text. ISO dates (2026-10-12) sort and compare correctly as strings and work with date() and julianday(). A date such as 10/12/2026 does not parse.

The one gotcha that matters

SQLite sorts every text value above every number. In a column of amounts where one cell says $1,200, this filter counts that cell as bigger than 6:

SELECT count(*) AS big FROM orders WHERE amount > 6;

On a five-row sample with two real amounts above 6 and one $1,200, it returned 3, not 2. Aggregates behave the other way: sum(amount) skips the text and under-counts. A dashboard built on that column looks plausible and is wrong, and that is the first check to write.

Checks that suit a static export

A CSV never updates by itself, so checks answer one question: is this export fit to use? Each returns one number, and the monitor compares it with a threshold, as described in Alerts overview.

-- values that are not numbers in a numeric column
SELECT count(*) AS amount_not_numeric
FROM orders
WHERE amount IS NOT NULL AND typeof(amount) NOT IN ('integer', 'real');

-- dates that do not parse
SELECT count(*) AS bad_dates FROM orders WHERE date(placed_on) IS NULL;

-- repeated keys
SELECT count(*) AS dup_ids
FROM (SELECT id FROM orders GROUP BY id HAVING count(*) > 1);

Alert on each with > and 0. For volume, SELECT count(*) AS row_count FROM orders with a < floor catches a truncated export, and a null count such as WHERE email IS NULL with a small tolerance catches a dropped column. The patterns behind these are in Data observability monitors.

Freshness, for an export

arcodash does not expose when a file was uploaded, so freshness is the age of the newest row in the data:

SELECT CAST(julianday('now') - julianday(max(date(placed_on))) AS INTEGER) AS days_old
FROM orders;

It rises by one each day until you upload a newer file, so it works as a reminder: alert at > 7 on a weekly export and the alert tells you the export was missed. An empty file returns NULL and the monitor stays unknown, so keep the row count check alongside it. If you replace the file in the connector’s settings, queries, dashboards and monitors stay attached to the same connector; the next run reads the new data.

Reconcile it against your database

The strongest use of a CSV is comparing it with something you trust. Save one query on the CSV connector and one on your database, run both, then compare them in Query Results, which joins the cached results of saved queries in SQLite. Say query_42 is the database’s totals and query_17 is the CSV’s:

SELECT CASE
         WHEN a.order_count <> b.order_count OR abs(a.revenue - b.revenue) > 1
         THEN 1 ELSE 0
       END AS mismatch
FROM query_42 a, query_17 b;

Monitor mismatch with == and 1. This is the finance export against the ledger, or a partner’s file against your orders. Query Results reads each query’s last cached result, so run both queries first; the rules are in Query Results.

Dashboard tiles

-- rows per day
SELECT date(placed_on) AS day, count(*) AS orders FROM orders GROUP BY 1 ORDER BY 1;

-- top 10 customers by amount, numbers only
SELECT customer, round(sum(amount), 2) AS total
FROM orders
WHERE typeof(amount) IN ('integer', 'real')
GROUP BY customer
ORDER BY total DESC
LIMIT 10;

Filtering on typeof() in the second tile keeps one stray cell out of the total. Put the bad-value count from the checks above next to it on the dashboard.

When a CSV is the wrong tool

If the file is a recurring export from a database you control, connect the database and skip the file. A CSV suits one-off datasets, vendor files and exports from tools with no connector. There is no scheduled re-download, no URL fetch and no appending: each new version is a new upload.

If the file lives behind an HTTP endpoint instead, see the JSON API connector.

docs

CSV setup in the docs

Connection fields, an example configuration and caveats.