Lesson 1 of 10 · Lab

Build your first data flow from a CSV

A data flow is a chain of blocks, and each block hands a table to the next. In Strata, Oplema's browser app for data prep, you place blocks on a canvas and connect them with wires. Every flow starts with a block that loads data, and Input loads a file.

Input can read a file straight from a web address into a table.

This course follows fictional Kramerica Bikes, which hires out bikes from three shops in one coastal town. You've just started helping the owner, Marit Holm, who wants to know which shop is short of bikes. Then she adds: "Start with Harbour. Tomas won't stop." Tomas manages Harbour and is sure it's the shop that's short. His till's export for September 1 to 20 is on this site.

The preview will show 100 rows of the file. Is that all of it?

Not necessarily. A preview starts as a sample of 100 rows from each source. The last step shows how much is missing.

Load Harbour's export onto a blank canvas

  1. Right-click kramerica-harbour-to-sep-20.csv and choose Copy link address, or Copy Link in Firefox and Safari.
  2. The lab below is a live Strata canvas. In the palette above it, select IO and drag Input onto the canvas.
  3. Select the block. Open the dropdown that says Local File and choose File from URL. Red text is normal until the block has a file.
  4. Select the URL field, paste the address and select Load. The button now reads Reload, and the preview reads 100 rows × 12 columns.
  5. In the palette, select Prep and drag Select to the right of Input.
  6. Drag from Input's right dot to Select's left dot. Drop on the dot, not the block.
  7. Select Select. It passes every column on until you set it, so its preview shows the same table.
  8. Open the dropdown that says Preview: 100 rows and choose Preview: All rows.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the preview should show

321 rows × 12 columns, from booking_id to till_note. Each row is one booking at Harbour, and 221 of them were missing from the sample.

A preview shows only a sample

The preview starts as a sample so that large files stay quick, and any count you read from it is the sample's count. Switch to Preview: All rows before you count or total anything.

A URL works only when it's the file itself and the server allows it, as this site's course files do. The Input reference lists the other sources, and the next lesson loads the file from your computer.

For later: How tables change shows what each kind of step does to a table.

Common first-flow mistakes

  • Pasting a page's address. Input needs the address of the file itself, ending in .csv, and says so when it gets a web page.
  • Dropping a wire beside the dot. A wire connects only when released on a dot. If no line stays, drag again.
  • Totaling the sample. A sum over the first 100 rows looks like a real number, whatever the file's length.

LIMIT and COUNT in SQL and Excel

SELECT * FROM harbour LIMIT 100;  -- a look at the data
SELECT count(*) FROM harbour;    -- every row

A preview is the first query: fine for a look, wrong for a count. In Excel, the status bar's Count covers only the selected cells, so select the whole column first.