# CSV, Excel and JSONL Ingestion Stop writing custom import and validation scripts. CsvPath Framework automates CSV and Excel ingestion and data quality checks. Open source. Python.
Logo for the CsvPath Framework

Automate CSV, Excel, and JSONL/NDJSON

End Manual Data Validation

#### CsvPath Framework data preboarding registers, versions, validates, upgrades, and stages CSV, Excel, JSONL files and data frames from data partners before they break your pipelines. [CsvPath Framework](https://github.com/csvpath/csvpath) is the open source way to shift-left data quality. Enforce data contracts as data enters the organization with **less manual effort, fewer ingestion failures, and more agile development** using consistent data preboarding tools [you can try in minutes](/getting-started/quickstart).

Your data lake deserves a data publisher it can trust!

### Get FlightPath Data, the cross-platform app frontend
[**FlightPath Data** is a powerful new frontend to CsvPath Framework](https://www.flightpathdata.com/). Go beyond CsvPath Framework's built-in CLI. Get up and running faster with a purpose-built preboarding development and operations console. FlightPath Data gives you all the help and examples you need move quickly. FlightPath Data is bundled with **FlightPath Server**, the no-code/low-code automation REST API connecting your existing infrastructure to data preboarding. Available as a *free* download from the [Microsoft Store](https://apps.microsoft.com/detail/9p9pbpkz4jdf?hl=en-US\&gl=US) and the [Apple MacOS Store](https://apps.apple.com/us/app/flightpath-data/id6745823097). ### The Architecture For Efficient Data File Feed Ingestion **CsvPath Framework** implements the [**Collect, Store, Validate Publish architectural pattern.** ](https://static1.squarespace.com/static/66df9d47982d0d40e1574327/t/6771fe6f63bbf5361725ad05/1735523953587/The+Collect+Store+Validate+Pattern+-+Atesta+Analytics.pdf) Ingestion goes faster, is more cost-efficient, and more effective with a preboarding stage. CsvPath Framework was built to fill the blindspot between MFT (managed file transfer) and the data lake with a simple path to provably correct data. This data preboarding blindspot is a big deal. Think about it. If even 1 in 30 companies depends heavily on CSV or Excel data, the *lack of **delimited file preboarding is a trillion-dollar problem***.
A data flow diagram showing how CSV, Excel and other tabular data come into the organization through a preboarding process that acts as a Trusted Publisher to the data lake and applications.
Why roll your own preboarding? CsvPath Framework is a purpose-built off-the-shelf solution you can rollout now. ### Powerful CSV and Excel Validation CSV, Excel, NDJSON, and JSONL validation is core to the Framework. **CsvPath Validation Language** is simple, easy to integrate, and flexible enough to handle the unexpected. Inspired by Schematron, XPath, and SQL, CsvPath Validation Language brings powerful data validation to tabular and JSON structured data. [Start here](/topics/higher-level-topics/validation/schemas-or-rules). Together CsvPath Framework and FlightPath Data can help you build leadership's confidence that your data governance doesn't turn a blind eye to your most unruly data. ### Integrated With Your Existing Tools
Logos of the many popular DataOps tools that are integrated with CsvPath Framework: aws s3, azure, slack, Excel, opentelemetry, sftp, ckan, pandas, openlineage, and more

CsvPath has a bunch of built-in integrations. Suggest more!

[Parquet](/topics/how-tos/parquet) / [Sqlite](/topics/how-tos/sqlite) / [S3](/topics/how-tos/storage-backends/aws-s3) / [OpenLineage](/getting-started/dataops-integrations/openlineage) / [OpenTelemetry](/getting-started/dataops-integrations/opentelemetry) / [Slack](/topics/how-tos/slack) / [Postgres](/topics/how-tos/postgres-and-mysql) / [MySQL](/topics/how-tos/postgres-and-mysql) / [Azure](/topics/how-tos/storage-backends/azure) / [Google Cloud Storage](/topics/how-tos/storage-backends/google-cloud-storage) / [Webhooks](/topics/how-tos/webhooks) / [JSONPath](https://github.com/csvpath/csvpath/blob/main/docs/func_gen/jsonpath.md) / [XPath](https://github.com/csvpath/csvpath/blob/main/docs/func_gen/xpath.md) / [Airflow](/topics/how-tos/airflow) and more. ### Give CsvPath Framework a Try * [Getting Started](/getting-started/quickstart) * [How-tos](/topics/how-tos) * [DataOps Integrations](/getting-started/dataops-integrations) * [The FlightPath Examples](/getting-started/the-flightpath-data-examples) {% hint style="success" %} For more background on preboarding and the CsvPath and FlightPath architecture [check out the CsvPath blog](https://blog.csvpath.org/). {% endhint %} {% file src="/files/mT8DDvqSrnXqfODHeaRF" %} {% embed url="" %} # CSV Validation Automation CSV File Validation Automation for Data Engineers ### CSV File Validation Automation for Data Engineers Does your company have data import scripts that nobody owns? Are there data file folder trees with duplicate files and naming nobody understands? Are you tired of spending hours every week debugging CSV files from vendors and writing custom validation scripts that break when partners change their formats? **You are not alone!** Instead of writing custom Python scripts for every data vendor, what if you could easily create new push-button data partner projects that automate every data feed exactly the same way, but with business rules validation to reduce manual effort and errors? CsvPath Framework reduces CSV processing time up to 80% and catches data quality issues before they reach your database. Here's how it works: ### Stop Writing Custom Validation Scripts **Before CsvPath (the old way):** ```python # 47 lines of custom validation code for each vendor import pandas as pd import re from datetime import datetime def validate_vendor_a_csv(file_path): df = pd.read_csv(file_path) errors = [] # Check required columns exist required_cols = ['customer_id', 'amount', 'date'] missing_cols = [col for col in required_cols if col not in df.columns] if missing_cols: errors.append(f"Missing columns: {missing_cols}") # Validate customer_id format invalid_ids = df[~df['customer_id'].str.match(r'^[A-Z]{2}\d{6}$', na=False)] if not invalid_ids.empty: errors.append(f"Invalid customer IDs on rows: {invalid_ids.index.tolist()}") # Validate amounts are positive numbers invalid_amounts = df[df['amount'] <= 0] if not invalid_amounts.empty: errors.append(f"Invalid amounts on rows: {invalid_amounts.index.tolist()}") # Validate date format try: pd.to_datetime(df['saledate']) except: errors.append("Invalid date format") # ... 30+ more lines for edge cases, encoding, duplicates, etc. if errors: raise ValueError("\n".join(errors)) return df ``` **With CsvPath (the new way):** ```python # 3 lines that work for any vendor from csvpath import CsvPath csvpath = CsvPath() results = csvpath.collect_paths(pathsname="vendor_rules", filename="customers") ``` **Your validation rules file (vendor\_rules):** ``` $[*][ regex(#customer_id, /^[A-Z]{2}\d{6}$/) and #amount > 0 date(#saledate, "MM/dd/yyyy") ] ``` ### Stop Manually Handling These Common Vendor CSV Problems * **Files with extra spaces, wrong date formats, or missing required fields** - CsvPath automatically trims whitespace and validates data types * **Partners who change column order without warning** - Rules work regardless of column position, reorder columns easily * **Encoding issues that crash your pandas scripts** - Capture and handle issues without pipeline failure * **Data that looks fine but fails downstream validation** - Catch schema mismatches before they hit your database * **Inconsistent header names** - Map vendor variations to your standard field names * **Files that are sometimes empty or malformed** - Graceful error handling with detailed reporting * **Set up CsvPath projects for new data vendors in seconds** using the [FlightPath Data](/getting-started/get-the-flightpath-data-app) app ### Every Week You Delay Costs More Manual Hours Many data engineers spend 3-5 hours per week on CSV issues that CsvPath automates in minutes. That's 150+ hours per year, per engineer. **Get your first automated CSV validator running in under 10 minutes.** ### Already Trusted by Data Teams CsvPath Framework is trusted by data teams from startups to regulated organizations. * **Distributed via** [**PyPI**](https://pypi.org/project/csvpath/)**, the** [**Microsoft Store**](https://apps.microsoft.com/detail/9P9PBPKZ4JDF)**, and the** [**Apple MacOS App Store**](https://apps.apple.com/us/app/flightpath-data/id6745823097) - enterprise-ready * **150+ built-in validation functions** for common data quality checks * **Open source** - dig below the surface, it's all there * **Community and paid support** - get help when you need it ### Ready to Automate Your CSV Chaos? Try CsvPath Framework as a Python library or in the [FlightPath Data](/getting-started/get-the-flightpath-data-app) app for MacOS or Windows. **→ Try the 5-Minute** [**Quickstart**](/getting-started/quickstart) **→ See More Real Examples** *(Complete solutions for common vendor scenarios)* **→** [**View Full Documentation**](https://github.com/csvpath/csvpath) *(Technical reference and advanced features)* *** **Questions?** Check out our FAQ or join the discussion on [GitHub](https://github.com/csvpath/csvpath/discussions). **Need enterprise support?** [Contact us about consulting services for complex data integration projects.](https://www.atestaanalytics.com/contact-us) # DATA PREBOARDING Why we all need to care about data preboarding and the trusted publisher model CsvPath is the leading tool for automated data preboarding. It is a purpose-built open source Python framework integrated with a wide variety of popular DataOps tools that acts as a trusted publisher between MFT and the data lake and applications. ## What is Data Preboarding? Data preboarding is the receiving process for external batch data. It is the first part of a robust data onboarding process. Preboarding assigns a durable identity, validates that the data meets expectations, upgrades it for productivity, and stages it in an immuable known-good archive for downstream consumers. **Your data lake deserves a data publisher it can trust!** Once data is preboarded it is no longer considered external.
A checklist of the capabilities of a preboarding architecture like CsvPath Framework: durable identification, validation, data upgrading, canonicalization, consistent immutable staging as a trusted publisher to downstream data users.
Data preboarding may be a new term to you, or not; either way it is not a new concept. All data is preboarded on its way into the organization. The question is, how well does your onboarding process work? The experience of most companies is that the process is less reliable, holds more risk, and is much more expensive than is comfortable. **Manual and error prone preboarding commonly diverts 2% to 10% of a revenue stream to overhead**. That's north of $20,000 per million or more than $20 million per billion in revenue. That adds up!
The CsvPath Framework logo
## How does the CsvPath Framework help? CsvPath is a drop-in replacement for rickety data landing zones. It is laser-focused on automated data preboarding. The Framework focuses on making the overall onboarding process efficient, fast, and safe by generating trustworthy data — and doing it in a way that scales operationally to any number of data partners. A company with one data partner needs effective preboarding. A company with a thousand data partners needs efficient preboarding that never fails. CsvPath Framework can help! CsvPath brings many capabilities to the table: * An opinionated framework for collecting, identifying, validating and publishing data that enables you to spin up a new data partner project literally in seconds * Powerful schema and rules-based validation that has never before been available for delimited data * Explainability-focused metadata production that gives you the power to know exactly what happened as your data evolved * Out-of-the-box integrations for lineage tracking, observability, MFT (managed file transfer), and more With CsvPath Framework you are signing up for a well-known pattern that settles the architecture and design questions up-front, leaving your team focused on data quality and accountability. And with CsvPath's the automation-forward approach, you can scale-down manual data quality efforts and scale up data throughput.
A super high-level data flow diagram showing how data files and validation/upgrading files are combined to create known-good data for downstream data consumers.

CsvPath is a pre-packaged automation-focused preboarding process that ends garbage-in-garbage-out.

## How to get started If you are a developer, take a look at the [Quickstart ](/getting-started/quickstart)and the [Your First Validation](/getting-started/more-csv-and-excel-validation/your-first-validation-the-lazy-way) exercises. They will get you up and running and introduce the CLI. Or try [FlightPath Data](https://www.flightpathdata.com/), the open source Windows and MacOS frontend to CsvPath Framework — available free in the Windows Store and Apple Mac Store. FlightPath is the fastest way to get started.
Reading about [schemas vs. rules-based validation](/topics/higher-level-topics/validation/schemas-or-rules) would be useful. Take a look the [How-tos](/topics/how-tos) and [DataOps integrations](/getting-started/dataops-integrations) sections. There is a [cheatsheet](/topics/practical-stuff/csvpath-framework-cheatsheet) and [validation language basics](/topics/practical-stuff/language). And there is more information on the[ GitHub site](https://github.com/csvpath/csvpath). For a higher-level view on the topics of edge governance and data preboarding, try the [atesta analytics whitepapers](https://www.atestaanalytics.com/downloads). They are CsvPath focused, but speak to the overarching operational and organizational needs. Data pre-boarding is everywhere. And yet it is dramatically undertooled. We're on a mission to upgrade preboarding and make CsvPath Framework the world's trusted publisher. Welcome aboard! # Quickstart Get started managing CSV/Excel file validation and ingestion with less manual effort, lower risk, and greater predictability. This page gives you all the information you need to get started validating your CSVs with CsvPath Framework. It is super high-level and quick. You will want to go deeper on other pages later. We're going to do a trivial validation of a CSV file. Validating an Excel file would be essentially the same. {% hint style="warning" %} For an **even quicker start**, download the free **FlightPath Data** app from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath). FlightPath is the open source frontend to CsvPath Framework. It is your CSV/Excel file feed development and operations cockpit. When you open FlightPath you will see [these examples that you can immediately run](/getting-started/the-flightpath-data-examples). {% endhint %} {% hint style="info" %} If you need help getting started with Python, try [Python.org's intros](https://www.python.org/about/gettingstarted/). Starting with a project tool like [Poetry](https://python-poetry.org/docs/basic-usage/) or [Jupyter Notebooks](https://jupyter-notebook-beginner-guide.readthedocs.io/en/latest/) can also help. {% endhint %} {% hint style="success" %} Feel like skipping the Python? [Watch these videos](https://www.youtube.com/@atesta-analytics) 🎥 or [try this Python-free CLI example](/getting-started/more-csv-and-excel-validation/your-first-validation-the-lazy-way)**.** {% endhint %} ### PyPI and Github [The open source CsvPath Framework](https://github.com/csvpath/csvpath) is available through [PyPI](https://pypi.org/project/csvpath/) as `csvpath`. The project is quite active. You should pin the version you use but update it regularly. We use Poetry for our own development. If you choose Poetry, all you need to do is: ``` poetry new <> cd <> poetry add csvpath ``` If you prefer Pip, install CsvPath Framework with: ``` pip install csvpath ``` Have a look at the [Github project](https://github.com/dk107dk/csvpath) for more details. You can read that site in parallel to this one.
Screenshot of the open source CsvPath Framework's GitHub page
### Let's run something! The worker class in CsvPath is unsurprisingly called `CsvPath`. For simple validation, it is all you need. {% hint style="warning" %} For more complex situations and DataOps automation we use the manager class `CsvPaths`. But we'll come back to that in later pages. For now just know that it exists, has essentially the same API, and is equally lightweight to use. {% endhint %} The quickest way to bootstrap a CsvPath Framework project is the command line interface (CLI). The CLI is a super simple tool that is great for fast no-code development. To try the CLI, skip over to [Your First Validation, The Lazy Way](/getting-started/more-csv-and-excel-validation/your-first-validation-the-lazy-way). To continue with the simplest possible Python, let's do a *hello world*. Create a script file and import CsvPath:

Add CsvPath to your requirements.txt or dependency manger first, of course!

```python from csvpath import CsvPath ``` Create a test CSV file. Save it as trivial.csv or whatever name you like.
A trivial CSV file to do a simple intro validation using CsvPath Language
{% file src="/files/5tc23tVYmWUjAgKd01nx" %} Make a csvpath. Also a trivial one, just to keep it simple.
A trivial CsvPath Language validation statement. This statement can validate a CSV or Excel file.
```python csvpath = """$trivial.csv[*][yes()]""" ``` This path says: * Open `trivial.csv` * Scan all the lines * Match every one of them
A screenshot of the Python to run the validation file. It is the same as the code below.
Here's everything: {% code lineNumbers="true" %} ```python from csvpath import CsvPath path = """$trivial.csv[*][yes()]""" cp = CsvPath() cp.fast_forward(path) if cp.is_valid: print("Totally valid!") else: print("Not valid.") ``` {% endcode %} What does this script do? * **Line 1:** imports CsvPath so we can use it * **Line 3:** is our csvpath that we'll use to validate our test file, `trivial.csv` * **Line 6:** fast-forwards though the CSV file's lines. We could also step through them one by one, if we wanted to. * **Line 8:** checks if we consider the file valid. If the file didn't meet expectations our csvpath would have declared the file invalid using the `fail()` function. When you run your script you should see something like:
A screenshot of what you should see when you run your validation.
Hello-world examples are never super impressive on their own. But you are now ready to dig in and see what CsvPath can really do. Next, try [Your First Validation, the Lazy Way](/getting-started/more-csv-and-excel-validation/your-first-validation-the-lazy-way). Also check out the [How-tos section](/topics/how-tos) for more use cases and examples. If you'd like a helping hand, [contact us](/getting-started/a-helping-hand)! # Get the FlightPath Data app FlightPath is a purpose-built CSV and Excel preboarding frontend to CsvPath Framework
[FlightPath Data](https://www.flightpathdata.com/) is a development and operations frontend to CsvPath Framework. * Makes CsvPath Language more agile and iterative. Wrangle CSV and Excel data better! * Gives you data file finding and run results metadata tracing tools * Enables quick, error-free forms-based configuration for dev and prod envs * Supports you with a ton of in-context help and examples * Is a window into your immutable data staging and publishing areas FlightPath is a **free download** from the [Apple MacOS Store](https://apps.apple.com/us/app/flightpath-data/id6745823097) and the [Microsoft Store](https://apps.microsoft.com/detail/9p9pbpkz4jdf?hl=en-US\&gl=US). You can also get the code from [Github](https://github.com/dk107dk/flightpath/tree/main). {% hint style="success" %} [FlightPath Server](https://www.flightpathdata.com/server.html) is bundled with FlightPath Data, giving you a complete automation architecture in an open and easy package. {% endhint %}
Take FlightPath out for a trial run and let us know what you think! # The FlightPath Data Examples The built-in examples that ship with FlightPath Data to help you start processing your CSV/Excel files fast.
[FlightPath Data](/getting-started/get-the-flightpath-data-app) comes with a set of CsvPath examples that can help you get started validating and upgrading your CSV and Excel file feeds quickly. Every new FlightPath Data project is created with an examples directory, so you always have ideas close at hand. The examples cover some common validation and upgrading activities. When you're done learning, feel free to delete the examples directory. {% hint style="success" %} **Download FlightPath Data** from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath). FlightPath is the free and open source development and operations console to the CsvPath Framework. {% endhint %} These pages present some of the READMEs and CsvPath Language files from the FlightPath examples. To see the full examples, with syntax highlighting, documentation, and sample CSV and Excel data, all in a working environment, just [install FlightPath](https://www.flightpathdata.com/).
# Counting CsvPath Validation Language examples of counting CSV data {% hint style="warning" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath) {% endhint %} These examples show different ways to craft CSV and Excel validation rules for counting lines and header values. There are a number of ways to do similar counts that have separate purposes but which overlap. For example: * `increment()` adds 1 each N times a match component evaluates to True * `counter()` adds N each time a match component evaluates to True * `every()` adds 1 every N-times a value is seen, matching or not Likewise, summing offers options: * `sum()` keeps a running count of values in a header * `count()` keeps a count of matches and, like `every()`, can also count match components it contains * `subtotal()` tracks the running sum of a header for each value in another header * `tally()` tracks counts of the values seen in headers or combinations of headers
* [Track values](/getting-started/the-flightpath-data-examples/counting/track-values) * [Counters](/getting-started/the-flightpath-data-examples/counting/counters) * [Tallying](/getting-started/the-flightpath-data-examples/counting/tallying) * [Summing up](/getting-started/the-flightpath-data-examples/counting/summing-up) * [Thresholds](/getting-started/the-flightpath-data-examples/counting/thresholds) # Track values {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath tracks the number of electricians. See the Variables tab for the results. Using increment() and mod() we can see that there are five electricians, and that there are two pairs with one electrician remaining. The mod() function will trigger errors until it has the values it needs for its calculation. But for this example we'll use validation-mode to make sure we ignore the complaints. From count() we can see that as well as those five electricians there are another 12 trades persons who are not electricians. id: electricians test-data: examples/counting/projects.csv validation-mode: no-raise, no-print, no-stop ~ $[*][ #trade == "Electrician" count.electricians(#trade == "Electrician") increment.nocontrib.half(#trade == "Electrician", 2) @half_remainder = mod(@half, 2) last.nocontrib() -> print("See the Variables tab") ] ``` # Counters {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath uses a decimal counter value to create a score for a developer based on how many projects they have. It also uses increment() to create a scaled impact metric based on a count of developer projects. id: developers test-data: examples/counting/projects.csv ~ $[1*][ #developer == "The Druker Company LTD" -> counter.influence(6.35) increment.nocontrib.impact_scale(#developer == "The Druker Company LTD", 3) gte.nocontrib(@influence, 50) -> @msg = "Druker has influence: " lt.nocontrib(@influence, 50) -> @msg = "Druker has little influence: " last.nocontrib() -> @influence = round(@influence, 2) last.nocontrib() -> print("$.variables.msg $.variables.influence") ] ``` # Tallying {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ tally(), every(), and count() create the same counts in variables. However, they each have their own different capabilities. - count(): Counts line matches (the union of all match components) and individual match component matches. count() produces values but doesn't contribute to determining matches. - every(): Counts values, creates a scaled count variable, and matches if its modulus is 0 - tally(): counts as a side-effect. tally() can count multiple header values as a join by passing multiple header arguments. tally() has no impact on matching and produces only the default value -- it is a complete side-effect. count() has no impact on matching, but produces its current count as its value. every() both produces a value, the scaled count, and votes on matching based on the remainder. You can remove every() as a factor in matching by adding the .nocontrib qualifier. id: counts test-data: examples/counting/projects.csv ~ $[1*][ #general_contractor_name == "Suffolk Construction Company" ~ to limit the matched lines to every 3rd Suffolk project remove the .nocontrib qualifier ~ every.nocontrib.e(#general_contractor_name, 3) tally.t(#general_contractor_name) count.c(#general_contractor_name) last.nocontrib() -> print("See variables tab for summaries") ] ``` # Summing up {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath sums up two header values in a similar way to this SQL. SELECT SUM (CASE WHEN neighborhood IN ('Roxbury','South Boston') THEN worker_hours_this_period ELSE 0 END) AS roxbury_south_boston_hours, SUM (CASE WHEN trade IN ('Iron Worker','Pipefitter') THEN worker_hours_this_period ELSE 0 END) AS iron_worker_pipefitter_hours FROM projects id: hello world test-data: examples/counting/projects.csv ~ $[1*][ subtotal.neighborhood(#neighborhood, #worker_hours_this_period) subtotal.trade(#trade, #worker_hours_this_period) @trade_hours = add( get("neighborhood", "Roxbury"), get("neighborhood", "South Boston") ) @neighborhood_hours = add( get("trade", "Iron Worker"), get("trade", "Pipefitter") ) last() -> replace( #neighborhood, @neighborhood_hours ) last() -> replace( #trade, @trade_hours ) last() -> collect( "neighborhood", "trade" ) last() -> print("Check out the matches tab") ] ``` # Thresholds {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath finds all the new high-value thresholds in a CSV file. It gives the same output as this SQL: SELECT id, worker_hours_this_period FROM( SELECT id, worker_hours_this_period, MAX(worker_hours_this_period) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_prev_value FROM projects ) AS subquery WHERE worker_hours_this_period > max_prev_value OR max_prev_value IS NULL; id: New high value test-data: examples/headers/projects_with_reset.csv ~ $[1*][ @hours.onchange.increase = float(#worker_hours_this_period) print.onmatch("line: $.csvpath.line_number: $.variables.hours") ] ``` # Duplicates CsvPath Validation Language examples of handling duplicate CSV/Excel data {% hint style="warning" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath) {% endhint %} CsvPath Validation Language makes it easy to find duplicate data in CSV and Excel files. These csvpaths show how you can create validation rules quickly and easily.
* [Unique lines by headers](/getting-started/the-flightpath-data-examples/duplicates/unique-lines-by-headers) * [Percent overlapping](/getting-started/the-flightpath-data-examples/duplicates/percent-overlapping) * [Duplicate lines](/getting-started/the-flightpath-data-examples/duplicates/duplicate-lines) # Unique lines by headers {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ Find all the lines that don't have at least one other year/location match out of a 5000 line sample. FlightPath will ask if you want to create a smaller sample file. This example runs quickly as-is, but in general smaller samples are better during iterative development. In particular, doing many printouts can slow runs down. To create a sample first click on the data file in the left-hand tree, Alzheimers_Disease_and_Healthy_Aging_Data_sample.csv. Then look at the top of FlightPath for the data toolbar and click the "Save sample as" button. After creating the sample you need to change the "test-data" path, below, so it points to your new sample file. Or, if you remove "test-data", you will be prompted to pick a file each time you run this csvpath. id: dup lines test-data: examples/duplicates/Alzheimers_Disease_and_Healthy_Aging_Data_sample.csv ~ $[*][ not( dup_lines( #YearStart, #LocationAbbr ) ) last.nocontrib() -> print("See the matches tab for the unique lines") ``` # Last duplicate seen {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} 
~
   This example uses some very artifical SAP CSV data to show a duplicate check
   on the transaction ID header. If a duplicate is found we print the error  
   with the most recent time the ID was seen.

   id: sales
   test-data: duplicates/sales.csv
   validation-mode:raise, print
~
$[*][ 
	@d = has_dups(#transactionId) 	

	~ if we have a duplicate, find the last time we saw it ~
	@d.asbool -> @last = get(@track, #transactionId)

	~ again, if we have a duplicate, this time print the error ~
	@d.asbool -> print("On line $.csvpath.line_number ID '$.headers.transactionId' is a duplicate ID last seen at $.variables.last")

	~ update our map of ID->time pairs so we can look back, if neeeded ~	
	track(#transactionId, #transactionCreatedDateTime)	
]
# Percent overlapping {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ Find the % of overlapping records for three headers. name: test-data: examples/duplicates/Alzheimers_Disease_and_Healthy_Aging_Data_sample.csv ~ $[1*][ dup_lines(#Stratification1, #Stratification2, #LocationID) @p = percent("match") @p = multiply(@p, 100) @p = int(@p) last.nocontrib() -> print("Out of $.csvpath.line_number lines with $.csvpath.count_matches matches, $.variables.p% overlap on age, race, and location") ] ``` # Duplicate lines {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath checks for duplicate lines. If it finds any it immediately stops and fails the file. Note that we're doing the fail and stop at the end of the printout. This is just a concise way of avoiding another when/do. id: 4-way dup check test-data: examples/duplicates/Alzheimers_Disease_and_Healthy_Aging_Data_sample.csv ~ $[1*][ @d = has_dups(#Stratification1, #Stratification2, #LocationID, #Topic) @d.asbool -> print(" Duplicate found on line: $.csvpath.line_number: Category 1: $.headers.StratificationCategory1: $.headers.Stratification1 Category 2: $.headers.StratificationCategory2: $.headers.Stratification2 Location: $.headers.4 Topic: $.headers.Topic ", fail_and_stop()) ] ``` # Headers CsvPath Validation Language examples for managing CSV/Excel file headers {% hint style="warning" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath)[Github](https://github.com/dk107dk/flightpath) {% endhint %} ## Using headers Headers are the names of values in a CSV or Excel file. In a delimited file, any given line may not have values for the headers. And headers can change or be found at any point in a delimited file. CsvPath Framework has many functions to help you work with headers. Most of the functions are oriented towards creating validation rules. Some of the header functions are quite unique. For example, in CsvPath Framework, you can reset the headers to the values of the current line at any time. These examples will help you start validating and upgrading your CSV and Excel files using the header functions to create rules.
* [Collect matching lines](/getting-started/the-flightpath-data-examples/headers/collect-matching-lines) * [Replace values](/getting-started/the-flightpath-data-examples/headers/replace-values) * [Insert and append](/getting-started/the-flightpath-data-examples/headers/insert-and-append) * [No blanks](/getting-started/the-flightpath-data-examples/headers/no-blanks) * [Reset headers](/getting-started/the-flightpath-data-examples/headers/reset-headers) * [Name check](/getting-started/the-flightpath-data-examples/headers/name-check) * [Position check](/getting-started/the-flightpath-data-examples/headers/position-check) * [More header checks](/getting-started/the-flightpath-data-examples/headers/more-header-checks) * [End](/getting-started/the-flightpath-data-examples/headers/end) # Collect matching lines {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ The collect() function selects headers to collect values from when lines match. In this csvpath we are collecting three header values from two lines. Data from the matched lines is visible in the Matches tab. We'll also print to the Printouts tab just to make the results extra visible. test-data:examples/headers/projects.csv id: collect ~ $[*][ #2 == "60 Kilmarnock Street" collect(#agency, #project_address, #period_ending) print_line.onmatch() ``` # Replace values {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath updates the "compliance_project_name" header when it sees a certain value. Because we used the nocontrib qualifier the csvpath still returns all lines even though we are selecting a subset. id: replacing test-data:examples/headers/projects.csv ~ $[*][ #compliance_project_name.nocontrib == "350 Boylston Street" -> replace(#compliance_project_name, "New Co. Build") last() -> print("See Matches tab for replacements") ] ``` # Insert and append {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath updates the test file data to have a processing date at the 0th header and a "modified by" header at the end of the line. id: insert and append test-data:examples/headers/projects.csv ~ $[*][ insert( 0, "processing date", now() ) append( "modified by", "CsvPath", yes() ) last.nocontrib() -> print("See the Matches tab for the new column at the end of every line") ] ``` # No blanks {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ When used without arguments, all() returns True (it matches) when all headers have values. all()'s opposite is the missing() function. In this example we are looking for any lines where a header has no value. We could use missing(), but to make a more interesting example, we are using return-mode to return only lines that do not match. On the last line we print a tally of lines and matches. Notice that we add a nocontrib qualifier to last(). nocontrib means that the match component with that qualifier does not contribute to matching. If we didn't add nocontrib the last() would only match with the last line. Since we are interested in all(), not last(), we use nocontrib to neutralize last(). return-mode:no-matches id: missing example test-data:examples/headers/projects.csv ~ $[*][ all() last.nocontrib() -> print("Lines: $.csvpath.line_number, Matches: $.csvpath.count_matches") ] ``` # Reset headers {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ In some cases you may want to reset headers. This often happens when there are prolog lines at the top of a file or if multiple datasets are joined in one file. When that happens you may do a reset and then need to check that the new headers are what you expect. This csvpath shows how to do the check on the new set of headers. id: reset headers test-data:examples/headers/projects_with_reset.csv ~ $[1*][ line_number() == 18 -> reset_headers(skip()) header_names_mismatch.u("agency|neighborhood|project|outcome") print( "Line: $.csvpath.line_number: present: $.variables.u_present misordered: $.variables.u_misordered unmatched: $.variables.u_unmatched duplicated: $.variables.u_duplicated ") ``` # Name check {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ One way to check a header's name id: header name test-data: examples/headers/projects.csv ~ $[1*][ @name = header_name(1) @name == "compliance_project_name" -> print("Name is correct!") ] ``` # Position check {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ Checking a header's position id: header name test-data: examples/headers/projects.csv ~ $[*][ header_name("compliance_project_name") == 1 -> print("Position is correct!") ] ``` # More header checks {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ We can check if a header is what we expect using the header_name() function by passing the expected name as a second argument. The result is true or false. To add to the example, if we wanted to trigger an action based on the result of header_name() we could use a when/do expression based on one of two things: - A variable holding the value of header_name() - using header_name() in the when/do expression directly While you wouldn't typically use a variable in such a simple case, if you did you would need to remember that a variable standing by itself is an existance test. That means a variable with the value False is still True in the sense that it exists. To make the boolean value of the variable be used as its match vote you have to add the asbool qualifier. test-data: examples/headers/projects.csv ~ $[*][ print(" Line: $.csvpath.line_number") @name = header_name(1, "compliance_project_name") @name.asbool -> print(" Name variable is correct: $.variables.name") @notname = header_name(1, "Compliance Project Name") not( @notname.asbool ) -> print(" Not name variable is also correct: $.variables.notname") header_name(1, "compliance_project_name") -> print(" Correct, no variable involved") not( header_name(1, "Compliance Project Name") ) -> print(" Also correct, still no variable involved") ] ``` # End {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ The end() function returns the value of the last header. If you pass it an int N it will return the value of the header at the index: count_headers_in_line() - 1 - N. (The minus 1 is because a count of the headers is 1-based; whereas, the index of a header is 0-based). To make the example a bit more interesting, we add the onchange qualifier to the variable assignment. That makes this variable assignment contribute a vote to matching lines. The onchange qualifier's main function is to limit an action to when it sees new information. Because we put onchange on the variable the Matches tab shows 9 lines. We could alternatively have put onchange on the print() function so we print only when the data changes, but still match every line. id: last column test-data: examples/headers/projects.csv ~ $[1*][ @last_header_value.onchange = end(6) print.onmatch("$.variables.last_header_value") ] ``` # Lookups {% hint style="success" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath)[ub](https://github.com/dk107dk/flightpath) {% endhint %} These example csvpaths create an index of zipcodes by city and perform lookups against it. The data comes from [data.gov](https://data.gov): `catalog.data.gov/dataset/boundaries-us-zip-codes`. The extensive geo-coding data was removed for brevity.
To run this example: 1. Stage `Boundaries_US_Zip_Codes.csv` as a named-file called `zips` 2. Load `index_zipcodes.csvpaths` as a named-paths group called `zip_index` 3. Load `zipcoode_looksups.csvpaths` as a named-paths group called `zip_lookups` 4. Run `zip_index` against the named-file `zips` 5. Run `zip_lookups` against the named-file `zips` Staging named-files and loading named-paths groups are options on the context menu when you right-click a filename in the project directory tree on the left. To run, right click on either the named-file folder or the named-paths folder in the windows on the right-hand side and select `New run`. The result is a variable `"a"` with the value of Boston's zipcode
# index\_zipcodes.csvpaths {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath creates an index of zipcodes by city from a CSV file with a CITY header and a ZIPCODE header. The index will be used to perform a lookup of a city's zipcode by another csvpath in a different named-paths group. To run this example: 1. Load Boundaries_US_Zip_Codes.csv as a named-file called zips 2. Load this csvpath as a named-paths group called zip_index 3. Run zip_index against the named-file zips See README.md for the full example steps sequence. source: data.gov, catalog.data.gov/dataset/boundaries-us-zip-codes test-data: lookups/Boundaries__US_Zip_Codes.csv name: create index ~ $[1*][ track.zipcodes(#CITY,#ZIPCODE) ~ we want to index, but not to capture data, so we reject all lines ~ no() ] ``` # zipcode\_lookup.csvpaths {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath iterates a CSV or Excel file and looks up the zipcode for Boston for every line. To run this example, first follow the steps in index_zipcodes.csvpaths to create the index. Then do these steps: 1. Load this csvpaths file as a named-paths group call zip_lookup 2. Run the zips named-file against zip_lookup id: zip lookup validation-mode:raise,print ~ $[1*][ @a = $bigzips.variables.zipcodes.Boston ~ we again want the variables but not the data, so we reject all lines ~ no() ] ``` # Math CsvPath Validation Language examples of using math functions in processing CSV/Excel files {% hint style="warning" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath) {% endhint %}
These examples focus on functions to find percents, averages, and sums in CSV and Excel files. Most of the math functions are easy enough to understand from their names. These csvpaths may give you ideas for your own validations and upgrading scripts. * [Averaging across groups](/getting-started/the-flightpath-data-examples/math/averaging-across-groups) * [Sum](/getting-started/the-flightpath-data-examples/math/sum) * [Subtract](/getting-started/the-flightpath-data-examples/math/subtract) * [Min and max](/getting-started/the-flightpath-data-examples/math/min-and-max) * [Running median and average](/getting-started/the-flightpath-data-examples/math/running-median-and-average) * [Percents](/getting-started/the-flightpath-data-examples/math/percents) * [Above average](/getting-started/the-flightpath-data-examples/math/above-average) # Averaging across groups {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath finds the average temp for all days sampled in each month. The result is a "month_ave" dictionary with an average temp keyed by each observed month number. I.e. for the first three months of the year you might see: {'01': 27.25, '02': 34.25, '03': 39.65} While this kind of pivot may be useful, it's worth pointing out that CsvPath Framework's goal is to assess the validity of data and upgrade it, if needed, to ideal-form raw data. Analysis and reporting are valuable, but ancillary. test-data: temps.txt ~ $[1*][ ~ find the month number ~ @month = regex(/[0-9]{4}-([0-9]{1,2})/, #0, 1) ~ count the number of samples we have for the month ~ tally.month_obs(@month) ~ get a subtotal of the month's temps ~ @sub = subtotal.month_temp(@month, #temp) ~ get the count of observations in month ~ @obs = get("month_obs_month",@month) ~ get the average for the month ~ @ave = divide(@sub, @obs) ~ store the average ~ put("month_ave", @month, @ave) print(" temp: $.headers.temp obs: $.variables.obs average: $.variables.ave month_temp: $.variables.month_temp month_ave: $.variables.month_ave ") ] ``` # Sum {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ sum() produces a value and also maintains a variable. You can add a name qualifier to enable the use of multiple sum()s or just for clarity. id: hello world test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1*][ @r = random(1, 5) @d = divide( sum.autos_moved(#2), @r ) @i = int( @d ) @rr = round( @d, 2 ) print("Line $.csvpath.line_number: divide $.variables.autos_moved by $.variables.r to get $.variables.d and cast to integer $.variables.i; alternatively rounded value $.variables.rr") ] ``` # Subtract {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ Subtract is similar to add, multiply, and divide, but it has the additional ability to negate a value. (This example uses the alias minus()) id: minus and subtract validation-mode: raise, print, stop test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1-2][ @i = random(1,3) @minus_i = minus(@i) @r = subtract(@i, @minus_i) print("$.variables.i subtract $.variables.minus_i = $.variables.r") ] ``` # Min and max {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath shows the min() and max() functions' potential to mirror the increase and decrease qualfiers. Look in the Variables tab to see the results. Notice that increase and decrease will do a lexical comparison if you don't pass them numbers. min() and max() convert to numbers, but plain headers are always strings until you do something with them. id: min-max test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1*][ @m1 = max(#"Automobile Volume") @m2.increase = int(#"Automobile Volume") @m3 = min(#"Automobile Volume") @m4.decrease = int(#"Automobile Volume") last() -> print(" m1: $.variables.m1 m2: $.variables.m2 m3: $.variables.m3 m4: $.variables.m4 ") ] ``` # Running median and average {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ Here we have the running median and the running average. The end result in variables @a and @m are for the full list of numbers. However, @a and @m are available at each line as the average and median of the lines scanned to that point. id: ave and median validation-mode: print, stop test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1*][ @a = average(#"Automobile Volume") @m = median(#"Automobile Volume") last() -> print("Final average: $.variables.a Final median: $.variables.m See variables for running values. ") ] ``` # Percents {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath checks if the % unique values is below 90% after at least 20% of years have been checked. id: percent unique validation-mode: raise, print, stop test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1*][ @reviewed = percent("scan") @uniques = percent_unique.units(#"Automobile Volume") below( @reviewed, .20 ) -> skip() below( @uniques, 90 ) -> print("Line $.csvpath.line_number looks wrong: $.variables.uniques") ] ``` # Above average {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ These two csvpaths, when loaded into one named-paths group called "above-ave", are roughly equal to this SQL: SELECT year, type, volume FROM autos WHERE volume > ( SELECT AVG(volume) FROM autos ) id: find average test-data: examples/math/Automobiles_Annual_Imports_and_Exports_Port_Authority_of_NY.csv ~ $[1*][ @ave = average(#2) ] ---- CSVPATH ---- ~ id: use average ~ $[1*][ below.nocontrib(#2, $above-ave.variables.ave) -> skip() print("$.headers.2 is above $above-ave.variables.ave") ] ``` # Schemas Examples of using CsvPath Validation Language schemas to validate CSV and Excel data {% hint style="warning" %} **Try these examples yourself.** They are built into FlightPath Data. You can download FlightPath Data free from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097) or [Github](https://github.com/dk107dk/flightpath)[ub](https://github.com/dk107dk/flightpath) {% endhint %}
## Making schemas for CSV and Excel CsvPath Framework supports data validation using schemas and rules. Both approaches have their own strengths. This example shows how to create a simple schema that functions much like a SQL DDL schema. The DDL example is in the file person.sql.txt. It has a simplistic 3-table model of a person with address and contact information. The examples in the file schemas.csvpath show four variations on an analogous CsvPath schema that can be used with a CSV or Excel file. Run any of the csvpaths by putting your cursor in the csvpath you want to run and typing **control-r** or right-clicking and selecting **Run**. * [Intersection](/getting-started/the-flightpath-data-examples/schemas/intersection) * [A three entity schema](/getting-started/the-flightpath-data-examples/schemas/three-entity-schema) * [Three entity schema v2](/getting-started/the-flightpath-data-examples/schemas/three-entity-schema-v2) * [Three entity schema v3](/getting-started/the-flightpath-data-examples/schemas/three-entity-schema-v3) * [Three entity schema v4](/getting-started/the-flightpath-data-examples/schemas/three-entity-schema-v4) # Intersection {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This csvpath is roughly equal to this SQL: SELECT street, city FROM shipping_address INTERSECT SELECT street, city FROM billing_address We set up the schemas for the shipping address and billing address, just for clarity; whereas, the SQL query only tests for street and city being equal. To be more equivalent, the csvpath would just be: #3 == #9 #4 == #10 collect(#street, #city) This is not a very realistic case, but it is interesting for the comparison to SQL. CSV and SQL schemas have many commonalities. In the second line() definition we use the header indexes rather than the names. That is because we have conflicting header names. Header names are found by searching left to right, so we're fine with the first line() definition. But the second line()'s headers would not be found if we used the header names. This is exactly the kind of thing index references are for. Because we can do this, our csvpath finds a number of schema violations for the second line(). Notice that we are naming the schema fields, rather than just leaving them to be understood from the header names. This is partly because of the index header references. But it is more valuable because we will have errors on the second line() in line 4 because of the not-nones. The error messages will be easier to understand if we add the names to the string()s. It is optional, but definitely helps in some cases, like this one. Note that for this to be a useful example we need validation-mode to be set to no-raise, so the full run happens despite errors. And we gain easier debugging errors messages and make the schemas self documenting by adding the names shipping and billing to the line() functions. id: addresses test-data: examples/schemas/shipping.csv validation-mode:print, no-raise ~ $[1*][ ~ we don't really need the two line() schemas, but for comparison to SQL we'll go ahead and create them ~ line.shipping( blank(#0), ~ this blank() is a placeholder for the ID header ~ string.notnone.addressee(#1), string.unit(#unit), string.notnone.street(#street), string.notnone.city(#city), string.notnone.state(#state, 2, 2), integer.notnone.postcode(#zip), wildcard() ) line.billing( wildcard(7), string.notnone.payee(#payee), string.unit(#unit), string.notnone.street(#9), string.notnone.city(#10), string.notnone.state(#11, 2, 2), integer.notnone.postcode(#12, 99999, 0) ) #3 == #9 #4 == #10 collect(#street, #city) ] ``` # Three entity schema {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ This schema shows a three-entity line structured as a person, their address, and their contact info, all side-by-side. The first entity, the person, starts at the left-most header. The other two entities start at well defined locations in the line. line() functions can overlap. For example, you could define a person to have an age, height, and weight, while also defining a second line() entity with health demographics including those same headers. line()s can be distinct. Adding a distinct qualifier requires all the header values combined from one line to be unique within the data file. You may use multiple line() functions to create the set of distinct constraints you need. For example, a line.person.distinct() might contain the first, middle, and last name plus the last 4 of a person's social security number. Another line.drivers_id() within the same CsvPath schema might require the last name and drivers license number pair to also be unique. id: example one description: Exactly the right headers and wildcards test-data: examples/schemas/people.csv ~ $[1*][ ~ this is the first entity in a line ~ line.person( integer.id.notnone(#0), string.notnone(#given_name), string.notnone(#family_name), wildcard() ) ~ this is a second entity ~ line.address( wildcard(3), string(#city), string(#state, 2), string(#zipcode), wildcard(3) ) ~ and this is the last ~ line.contact( wildcard(6), email(#email), url(#linkedin), string.cell(#phone) ) ~ Just to see something in the printouts let's say hello. ~ @name = concat(caps(#1), " ", caps(#2)) print("Hello $.variables.name") ] ``` # Three entity schema v2 {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ id: example two description: 2 wildcards in address test-data: examples/schemas/people.csv ~ $[1*][ line.person( integer.id.notnone(#0), string.notnone(#given_name), string.notnone(#family_name), wildcard() ) line.address( wildcard(3), string(#city), string(#state, 2), string(#zipcode), wildcard() ) line.contact( wildcard(6), email(#email), url(#linkedin), string.cell(#phone) ) last.nocontrib() -> print("See the Matches tab for valid lines") ] ``` # Three entity schema v3 {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ---- CSVPATH ---- ~ id: example three description: Extra wildcard at end of contact test-data: examples/schemas/people.csv ~ $[1*][ line.person( integer.id.notnone(#0), string.notnone(#given_name), string.notnone(#family_name), wildcard() ) line.address( wildcard(3), string(#city), string(#state, 2), string(#zipcode), wildcard(3) ) line.contact( wildcard(6), email(#email), url(#linkedin), string.cell(#phone), wildcard() ) last.nocontrib() -> print("See the Matches tab for valid lines") ] ``` # Three entity schema v4 {% hint style="success" %} Run this example using its test data from within [FlightPath Data](https://www.flightpathdata.com/). {% endhint %} ``` ~ id: example four description: Unnecessary wildcard at end of contact and a distinct. test-data: examples/schemas/people.csv ~ $[1*][ line.person( integer.id.notnone(#0), string.notnone(#given_name), string.notnone(#family_name), wildcard() ) line.address( wildcard(3), string(#city), string(#state, 2), string(#zipcode), wildcard() ) ~ because each entity ends with a wildcard() with no specified length this line can continue as far to the right as needed ~ line.distinct.contact( wildcard(6), email(#email), url(#linkedin), string.cell(#phone), wildcard() ) last.nocontrib() -> print("See the Matches tab for valid lines") ] ``` # More CSV and Excel Validation Preboarding without validation is like pizza without cheese CsvPath Framework includes a robust delimited data validation framework that makes validating CSV, Excel, JSONL, data frames, and other tabular formats a snap. The CsvPath Validation Language: * Is declarative * Provides powerful rules-based validation * Provides schema-based validation * Has over 200 validation functions and allows user-defined custom functions * Supports data upgrading and canonicalization * Streams data to handle large files * Provides metadata, variables, reporting, and error handling options * Comes with a wealth of features enabling all kinds of use cases Best of all, you can start small, do simple things incredibly quickly, and not worry that you'll hit a wall when the requirements get harder. Read on for a small example. Then look through the site for lots more examples, large and small. If you need help or have questions, [just ask](/getting-started/a-helping-hand)! # Your First Validation, The Lazy Way Start here if you want a code-free introduction! {% hint style="info" %} This example uses the built-in CLI. The CLI is so 2025. For a more powerful, yet even easier, experience, try the [**FlightPath Data** app](https://www.flightpathdata.com/). FlightPath Data is a free and open cross-platform operations and development tool that makes getting started with CsvPath Framework easy. It is bundled with **FlightPath Server**, a no-code/low-code integration and automation API. You can read more about [FlightPath Data here](https://www.flightpathdata.com/flightpath.html) and [here](/getting-started/the-flightpath-data-examples). Or just download it from [Microsoft](https://apps.microsoft.com/detail/9P9PBPKZ4JDF) or [Apple](https://apps.apple.com/us/app/flightpath-data/id6745823097?mt=12) and read the in-context help and examples. {% endhint %} This page gives one way to do the **Your First Validation** exercise. In the other approaches to our [super-simple first validation exercise](/getting-started/more-csv-and-excel-validation/your-first-validation-the-easy-way) the focus is on the CsvPath Language, but there was also a little Python to drive it. We can do it without the Python by using the CLI that comes with the CsvPath Library. Here's how. We're going to use Poetry for our example project. You can [learn how to set Poetry up here](https://python-poetry.org/docs/). Open the command line and type this: ```bash poetry new first_example ``` Change `first_example` to any project name you like. You should see this:
`cd` into your new project. Next, add csvpaths to your project with: ```bash poetry add csvpaths ``` You should see this:
We can now run the CsvPath Library's CLI with: ```bash poetry run cli ``` If you're not a Poetry user, what we're doing is running a script defined in the pyproject.toml.
You can do the same with: ```python from csvpath.cli import Cli Cli().loop() ``` The CsvPath CLI is bare-bones. Despite that, it is a useful way to do simple stuff fast. It is great for learning and basic CsvPath Language dev work. You should see this:
You can select `quit`, for now. In your project dir, create a subdirectory called `assets`, or whatever name you like. We'll drop an example CSV file and your csvpath file there. Create a file called `first.csvpath`—or again, whatever name you like. Into it, paste the simplified version of the [First Validation Example](/getting-started/more-csv-and-excel-validation/your-first-validation-the-easy-way) csvpath statement: ```xquery ~ id: First Validation, Simplified! description: Check if a file is valid validation-mode: print, no-raise, fail ~ $[*][ line( string.notnone("firstname"), string.notnone("lastname", 30), string("say") ) ] ``` Add the example delimited data in example.csv to the assets dir. Use a trivial data set: ```csv firstname,lastname,say Sam,Cat,Meow... Fred,Dog,Woof woof Blue,Bird,Tweet! ``` Now we're ready to run the validation. Fire up the CLI again with `poetry run cli`. Select `named-files`. You should see this:
Hit return on `add named-file`. We're going to import your file into the `FileManager`'s files area. The file manager is used whenever one of your `CsvPaths` instances needs to run a validation. When you hit return the CLI should ask you for a name for the file you are going to import:
Any name works. `example` would be a good choice. You should then see a selection of `dir`, `file`, or `json`.
Pick `file`. Next you will select your file by drilling down into your `assets` directory. Select your file and hit return.
Once your file is added you go back to the top menu. This time select `named-paths`. And in the next submenu pick add named-paths.
Again, you enter a name for the csvpaths you are adding. `first` would be a fine name. You next drill down to your `first.csvpaths` file in the assets dir. Select it and hit return. You'll be taken back to the top menu. Now you're ready to run your example. Select `run` and hit return. You will be asked for the name of a file. Select your file's name, `example`.
Next you'll be asked for the name of your csvpaths:
You have just one named-paths name, `first`, so select that and hit return. Now you get the question of what method you want to use to run your paths against your file. The options are **fast forward** or **collect**. As you may already know, fast-forward runs your validation, but doesn't collect the matching lines. Instead, it only collects variables, printouts, and errors. The collect method does collect the matching lines, in addition to variables, printouts, and errors. As an aside, the library also allows you to step through a CSV path as its being validated, line-by-line. However, the CLI does not offer that option. You can easily do it programmatically using a `CsvPaths` instance's `next_paths()` method in a `for line in csvpath.next_paths()` loop. For our purposes, either method works. Pick `collect`.
The CLI briefly tells you it is running. Then you're back at the top menu. You have successfully completed your first validation run. Congrats! Now let's take a look at what resulted from our validation run. Select `named-results`.
The CLI is so simple it can only open our results in your operating system's file browser. But that will do for learning and developing. Select `open named-result` and select `first`. A new window opens to your runs of the `first` named-paths group runs. So far you have just one run. It should be timestamped for a minute ago. Inside your `first` run you should see these files:
`data.csv` has all the lines from your `example.csv` file with no changes made. Our validation matched all the lines and we used the collect method (technically, `CsvPaths.collect_paths()`) so everything in the original file came through unchanged. `errors.json` is empty because there were no errors. We didn't set any variables, so `vars.json` is empty. And we didn't print anything as the run happened, so `printouts.txt` is also empty. Not a lot to see, here, but we were expecting that, so it is a good thing. There is a good amount of metadata in meta.json. If you open that file you should see something like this:
On line 16 you can see what file we used. It is the one you imported earlier. You can [learn more about how the CsvPath Library manages files here](/topics/higher-level-topics/data-validation-files-and-storage/file-management). And read this page for more information about [named-paths group validation results](/topics/higher-level-topics/data-validation-files-and-storage/where-do-i-find-results). And that's it. Your first validation. Simplified and no Python code involved. Not bad! # Your First Validation, The Easy Way CsvPath is a very flexible language. There is often a simpler way than you first thought. CsvPath Language offers both rules-based validation and schemas. Validation rules are powerful. A rule can do things that a schema can't do. But sometimes just clarifying the shape of the data is enough. Both approaches are important tools. CsvPath's claim to fame is the power of rules-based validation — no other validation language offers the same capabilities for delimited, tabular data. But CsvPath is also really good at structural validation. You can see more examples and [read about the difference between these approaches here](/topics/higher-level-topics/validation/schemas-or-rules). Here's our First Validation example again, this time using a simple schema. We'll use the `line()` function to specify what each line of data looks like in a valid CSV or Excel file. This snippet is the whole validation: ```xquery line( string.notnone("firstname"), string.notnone("lastname", 30), string("say") ) ``` We expect exactly three headers and require two of them to always have values. We could run this using the CLI, but let's see how to do it with Python. ```python from csvpath import CsvPath csvpath = """ ~ id: First Validation, Simplified! description: Check if a file is valid validation-mode: print, no-raise, fail ~ $trivial.csv[*][ line( string.notnone("firstname"), string.notnone("lastname", 30), string("say") ) ]""" path = CsvPath().parse(csvpath) path.fast_forward() if not path.is_valid: print(f"The file is invalid") ``` Two lines to run the validation. Two lines to print a warning if the CSV file is invalid. Simple! We also added some optional metadata and a mode configuration in a comment. The comment is the part between the `~` characters. In the metadata we are saying that the `id` field will become the csvpath's `identity`. The identity is available on the `CsvPath` instance's `identity` property. It will be used in printing out any built-in validation errors. The `description` is ours to use as we wish—it is a user-defined field. And the `validation-mode` is a setting that tells the `CsvPath` instance what to do when there is a validation error. In this case we want to print errors, but not raise exceptions, and we want the file to be marked as invalid if there are errors. We sometimes call this failing the file. There's a lot more you could do, of course. This is barely the tip of the iceberg. Keep reading and experimenting! # Your First Validation, The Hard Way Super simple rules to demonstrate how validation works As you saw in the Quickstart, CsvPath is easy to set up. And structural validation is also easy. But what greater value can CsvPath add? CsvPath's rules-based validation of CSV and Excel files is a unique capability. In this version of the **Your First Validation** example we're going to first take a rules-based approach that will give you a sense of the CsvPath Language's power. ## Our goal Our mission is to write a csvpath that can keep some bad data from getting loaded into our data management systems. Our goals for the data are: 1. There cannot be blanks; every header must have data 2. The files' `lastname` header cannot have values greater than 30 characters 3. The first header must be `firstname` Our strategy is to collect any lines that break these rules. The validity of the file as a whole will depend on there being no failing lines.
## To raise or call By default, the CsvPath library config raises errors when it runs into problems. This can halt a run for a simple built-in validation error. For e.g. `add("five", 3)` does not equal `8` because "five" is not a number. Finding that error is good, but in some cases you might not want the validation run to stop with a big noisy error. To have your `CsvPath` instance neatly print errors, but not raise exceptions, you can make a quick change to the `config.ini` file. By default the config file is in `./config/config.ini`. Make the change to this line:
As you can see, in my `config.ini` the `CsvPath` instance error policy is on line 8. Just remove the keyword `raise` from that line, save, and you're good to go. Now, for that incorrect addition above, you get a concise error message like this, rather than a big exception stack dump: `Line 3: Wrong value in match component 0: mismatch in add` ## Continue your Quickstart script. Paste this rule into the csvpath string. It goes in the matching part, after the scanning part. The scanning part of a csvpath is at the beginning. It looks like this: `[1*][` `~ Apply three rules to check if a CSV file meets expectations ~` Your file should now look something like:
Csvpaths can include line breaks, so you can format your csvpath any way you like. What did we just do? We added a comment saying what our csvpath does. That's all. Comments are completely optional, but they are useful. Here is the file so far. {% file src="/files/7BgYjRjLFRwE9KwuoVgc" %} Let's continue. ## Rule one: no blanks Our csvpath's first rule is that valid files have data under every header. CsvPath looks at the 0th line to determine the file's headers. Of course, some files don't include headers. We'll see how to handle that later. Add a test for blanks below the comment. Make it replace the `yes()` function: ```clike ~ Apply three rules to check if a CSV file meets expectations ~ not(all(headers())) ``` What does this statement do? As we iterate through the CSV file line by line, the `all()` looks at values and returns `True` if all of them have data. We pass `all()` a `header()` function to direct it to look in all the headers. In our last script we used the `fast_forward()` method to run though the CSV file without interacting with it. This time, let's collect the offending lines. To do that, we need to use `not()` to match any line that has blanks. We also have to switch from `fast_forward()` to `collect()`. And one more change. Rather than using is\_valid, let's say that the count of invalid lines determines if our file is valid. `collect()` returns the lines. Let's just print out how many. When you've made these changes, your script should look like: ```python from csvpath import CsvPath csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file meets expectations ~ missing(headers()) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() print(f"Found {len(lines)} invalid lines") ``` Try it out. You should see that our file is still valid. Here's our CSV file again. {% file src="/files/5tc23tVYmWUjAgKd01nx" %} To see that the script works, edit the CSV file to make a blank. Take Sam Cat's lastname out.
This time your script should tell you that there is one invalid line.
## Rule two: no long lastnames Next let's create our rule about long lastnames. Our requirements said that lastnames cannot be longer than 30 characters. Add this line to your csvpath below the line about blanks. ```clike above(length(#lastname), 30) ``` This line uses functions to check a particular header's length. We're saying that values under the `lastname` header must be less than 30 characters long. Csvpaths use `#` to indicate a header. Your csvpath should now look like:
```python csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file meets expectations ~ missing(headers()) above(length(#lastname), 30) ]""" ``` Pretty straightforward, right? But wait! We can do better. Swap out the line you just added for: ``` too_long(#lastname, 30) ``` That's much better! There are more than 75 built-in functions so there is often a simpler way to do things. The [list of functions is on the Github repo](https://github.com/dk107dk/csvpath/blob/main/docs/functions.md). Here's what your file should be like now. ```python from csvpath import CsvPath csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file is invalid ~ missing(headers()) too_long(#lastname, 30) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() print(f"Found {len(lines)} invalid lines") ``` Now, we have a problem. The problem is that we are ANDing these two rules together. What's the problem? When we are ANDing, by default, we match a line if all the rules are true. In this case, though, we are expecting lines to match if only one of the two rules is true. What we need is an OR operation. That's easy! Just add this line right below where you created your CsvPath instance. ```python path.OR = True ``` This change will make CsvPath match lines where any one or more rules are true. This way, we can call out the possible problems with the file and collect lines that match those problems without the rules interfering with each other. Your script should now look like: ```python from csvpath import CsvPath csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file is invalid ~ missing(headers()) too_long(#lastname, 30) ]""" path = CsvPath() path.OR = True path.parse(csvpath) lines = path.collect() print(f"Found {len(lines)} invalid lines") ``` Try running it. You should still see that there is one invalid line. After you confirm that, try creating a very long lastname for Sam the Cat and no lastname for Fred the Dog. CsvPath should collect the two invalid lines.
## Rule three: first header Our validation csvpath wants the first column to be called `firstname`. That seems like an easy rule to satisify, right? Just type it that way. We will of course do that. But consider if your csvpath is going to be validating files from a nightly batch job. Since we don't want to rock the keyboard at 3 a.m., we need a rule to check for us, even if it is just a simple rule. Add this below the max length rule: ```python not( header_name(0, "firstname") ) ``` This rule says that the 0th header must be the `firstname` header. Headers can be accessed by their numeric position — their index. The index is 0-based, like a Python list. That means the first header is `#0`. In our rule, the function looks up the name of the header indicated by `0`. If it doesn't equal `"firstname"` we collect the invalid line. Let's pause on an interesting point. ## Validation rule strategies CsvPath applies a csvpath to a CSV file line by line. It can collect each line that matches its rules. CsvPath is all about matching lines. In our rule, the `column()` function will be called on every line, not just the header line. That means that if the first header is not `firstname`, our rule would collect every line in the file. For our purposes, that would indicate that all the lines were bad. In reality, in that case, we would consider the file as a whole invalid, not the individual lines. What to do? Easy! Change the line you just added so that the rule looks like: ``` not.nocontrib( header_name(0, "firstname") ) -> fail() ``` Now the rule doesn't determine if individual lines match match the header name requirement. Instead it marks the file as invalid. This allows you to collect the lines with problems and also know if the file has problems that cut across the lines. ## And you're done! Your script should now look something like: ```python from csvpath import CsvPath csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file is invalid ~ missing(headers()) too_long(#lastname, 30) not.nocontrib( header_name(0, "firstname") ) -> fail() ]""" path = CsvPath() path.OR = True path.parse(csvpath) lines = path.collect() print(f"Found {len(lines)} invalid lines") ``` There are a couple of new things here. First there is a qualifier, `nocontrib`, on the `not()` that wraps the `header_name()` function. Qualifiers modify the behavior of functions and other match components. You can [read all about qualifiers here](https://github.com/dk107dk/csvpath/blob/main/docs/qualifiers.md). The `nocontrib` qualifier says that the function it is on should not determine if the line matches. It does not contribute. Again, we need this because every line will match on the header name problem. The second new thing is the when/do. That's the `->` operator. When/do says that when the left hand side is `True` the right hand side happens. In this case, when there is a header name mismatch we fail the file using the `fail()`. If you are concerned that this when/do could be executed many times, you are right. But in this case that doesn't matter because calling `fail()` is cheap in performance terms and always has the same effect. Now when you run your script you will see... nothing new. Let's add a print statement to let us know if the file overall is valid. Put this at the bottom of your script. ```python if not path.is_valid: print(f"The file as a whole is invalid. Check the headers.") ``` Now you should see something like:
You might wonder if we should really be saying that the file as a whole is invalid only if we find a problem with the headers. Isn't it a problem that two lines are invalid, as well? That is a very reasonable opinion. CsvPath cannot make assumption about if you are collecting invalid lines or valid ones. That is a decision about [your validation strategy](#validation-rule-strategies)—as we have said, there are always multiple ways to do things. However, we can easily mark the file invalid when we have lines failing. Just add this to the bottom of your csvpath string: ```xquery has_matches.nocontrib() -> fail() ``` And for consistency's sake, modify your error message: ```python print(f"The file is invalid") ``` Now our validation script is giving us complete and actionable information about our file. There's much more we could do, of course—we're just scratching the surface! We'll explore some of those options and improvements in the next example. Here is the final state of the script to compare against your own. ```python from csvpath import CsvPath csvpath = """$trivial.csv[*][ ~ Apply three rules to check if a CSV file is invalid ~ missing(headers()) too_long(#lastname, 30) not.nocontrib( header_name(0, "firstname") ) -> fail() has_matches.nocontrib() -> fail() ]""" path = CsvPath() path.OR = True path.parse(csvpath) lines = path.collect() print(f"Found {len(lines)} invalid lines") if not path.is_valid: print(f"The file is invalid") ``` # DataOps Integrations CsvPath has built-in integrations for storage, observability, formats, and more These pages and [the How-tos section](/topics/how-tos) are all about integrating CsvPath FileOps into your wider DataOps environment. And there are lots more use cases and how-tos in [the docs on https://www.flightpathdata.com](https://www.flightpathdata.com/flightpath.html).
CsvPath is growing its list of pre-integrated tools week-by-week. If you don't see the tool you need, [just ask](/getting-started/a-helping-hand). | Category | Tools | | -------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **File Transfer** |

| | **Lineage** |

| | **Observability** |

(80+ monitoring and alerting venders support OpenTelemetry)

| | **Data Formats** |

  • CSV and any XSV
  • Excel
  • JSONL
  • Pandas dataframes
  • Embedded JSON and XML

| | **Notifications** |

| | **Data Publishing** |

| | **Metadata Databases** |

| | **Object and Data Lake Storage** |

| | **Shell scripts** |

| | **Data Processing** |

| # OpenTelemetry Observability for the masses

CsvPath in Grafana

DataOps success demands a good understanding of what is happening on multiple levels moment-to-moment. You can't agile what you don't observe. OpenTelemetry is the standard way to collect indicators from systems into an observability platform. Its OTLP protocol is supported by [more than 80 observability venders](https://opentelemetry.io/ecosystem/vendors/) and open source platforms, including Grafana, Splunk, Elastic, and more. And now CsvPath, too. Getting started with CsvPath + OpenTelemetry is drop-dead easy. Easier even than the other CsvPaths integrations, which are all pretty lightweight themselves. Here's what you do:
1. Update to the latest point release 2. Open `config/config.ini` and add otlp to the `[listeners] groups` list 3. Set your OTLP platform environment variables If your `config/config.ini` isn't generated new so it doesn't already have the OTLP class imports you see in the screenshot above, just paste these in: ```ini [listeners] otlp.result = from csvpath.managers.integrations.otlp.otlp_result_listener import OpenTelemetryResultListener otlp.results = from csvpath.managers.integrations.otlp.otlp_results_listener import OpenTelemetryResultsListener ``` The env vars values are vender specific. Here's a screenshot of my Grafana Cloud setup. Yours will be similar, but different values. Check your vender's docs.

OTPL env vars. Don't use these, though! Your observability tool vender will provide your values for your own use

Once you've done those three things you're done. Run some named-paths groups and see your metrics flowing into your observability tool. Simple! There's more than just a couple of pretty graphs going on here. We'll talk about how adding observability to CsvPath is much more than the sum of its parts. It can change your DataOps mode of operations entirely. Give OpenTelemetry a try and let us know what you'd like to see. There's much more we can do on OTLP. # OpenLineage Get started with Edge Data Governance the easy way. The instructions on this page should take you 15 to 45 minutes, depending on network speeds, docker startup times, etc.
First a bit on what we're aiming to do and why. Lineage is about tracking the changes to data sets and their usage over time with the goal of explaining how every state in the data lifecycle happened. Clear lineage data makes finding, explaining, and fixing problems easier. To get a clear view of the lineage of a data set you need metadata — lots of it — and a way to analyze the information to tell the story of how things happened. [OpenLineage](https://openlineage.io/) is an open standard for event-based lineage capture. [Marquez](https://peppy-sprite-186812.netlify.app/) is the server and webapp providing the reference API to collect and display OpenLineage events. CsvPath is an OpenLineage event source that provides copious metadata describing how your data moves through a consistent onboarding lifecycle. Together these open source tools **fill the gap between MFT (managed file transfer) and the typical data lake architecture**. They provide an unprecedented level of visibility into your data onboarding operation. With workflow, transformation, and processing tools like **dbt**, **Airflow**, and **Spark** also throwing off OpenLineage events, you now have a straightforward way to collect end-to-end lineage. From data partner, to data lake, to analytics and applications, and back out to the World as a data product or service.

An end-to-end lineage schematic

## How to start To start, create a new CsvPath project. As usual we'll use Poetry, but of course you can use Pip or any Python project tool. Call your `project lineage_example`. ``` poetry new lineage_example ```
That sets up your CsvPath library. For this example we only need the CLI so we're almost done. We'll create a dummy CsvPath language file to run and some dummy data in a moment. Install [Docker desktop](https://www.docker.com/products/docker-desktop/), if you don't already have it. You'll need to create a Dockerhub account. It should be painless. Next install Marquez. [Read this page](https://peppy-sprite-186812.netlify.app/docs/quickstart) because it's interesting and tells you much more about Marquez than our page does. Clone the Marquez Github: ```url git clone https://github.com/MarquezProject/marquez && cd marquez ``` In the `marquez` directory do: ```bash ./docker/up.sh ``` After the images download and the server starts you should be done setting up Marquez. Back to CsvPath. Create a file in your project directory called `test.csv`. Paste in our usual test data. ```csv firstname,lastname,say David,Kermit,hi! Fish,Bat,blurgh... Frog,Bat,ribbit... Bug,Bat,sniffle sniffle... Bird,Bat,flap flap... Ants,Bat,skriffle... Slug,Bat,oozeeee... Frog,Bat,growl ``` Create another file called `lineage_example.csvpath`. Paste in this: ```xquery ~ id: first lineage example ~ $[*][ yes()] ``` Before we can run your files we need to stage them in the CsvPath framework's `inputs` directory. We also need to tell the CsvPath library that it should send events to Marquez. We'll add the files first because that will give CsvPath the opportunity to create directories and config files for us. Fire up the CsvPath CLI. Do: ``` poetry run cli ``` If you are not using Poetry have a look at `pyproject.toml` to see the plain command to use to start the CLI. The CLI will look like this
Select `named-files` and then `add named-file`. You'll be asked for a name. Give the name `test`. Then you will see options for an individual file, a JSON list of files, or adding a directory of files:
Select `file`. You will see a listing of your directory. Pick `test.csv`:
After CsvPath adds your input data file you go back to the top menu. This time select `named-paths` and then `add named-paths`. You should see:
You'll be asked for a name. Give the name `lineage_example`. You will again be asked if you are picking a file of csvpaths, a directory, or a JSON file. Again pick file. You will be presented with your directory:
Pick your `lineage_example.csvpath` file. And you're done with that part of the setup. Next let's modify the `config.ini` slightly. Open `./config/config.ini`. We want to make two changes. First we'll change the archive name. You don't really need to do this, but since your example isn't real work, why not separate it? We also need to uncomment the `[listeners]` and `[marquez]` settings. When you've made those changes your config file should look like:
Notice we made the archive name `Sunshine_Inc`. Do the same. Marquez doesn't like spaces so be sure to use the `_`. Now we can run our csvpath! Restart your CLI so it has your config changes. At the top level select run:
You will be asked to pick the file to run from a list. There is one option, so pick that.
Next you will be asked for the named-paths group. Again you'll have a list of one, so pick the one.
And finally you'll be asked to pick a run strategy by method name. If you've been doing other examples you'll know that `collect` keeps the matching rows and `fast-forward` does not. For our purposes it doesn't matter which we choose, but pick `collect`.
You should get a message indicating that your run completed:
We're good. We should see our run in Marquez. Open . Remember, you'll start out looking at the `default` namespace. `default` is empty. We pushed our events to `Sunshine_Inc`. Switch to the Jobs vertical tab on the left-hand side.
Then look at the top right for the namespaces dropdown. Select `Sunshine_Inc`. If you don't see our namespace right away, refresh the page.
You should now see your job events!
Click on [Group:lineage\_example.Instance:first lin...](http://localhost:3000/lineage/job/Sunshine_Inc/Group%3Alineage_example.Instance%3Afirst%20lineage%20example) to open your core job. There will be other jobs that were for staging assets. You can learn about everything you are seeing on other pages of this site.
And there you have it. A local install of Marquez integrated with a CsvPath project. Clarity and consistency! Not bad for a few minutes work. And a good start on the journey to stronger edge governance and operational efficiency. # A Helping Hand We're here to help CsvPath is an open source project. As individuals we're happy to help. And we have sponsors and partners we can hand off to, if more is needed. Reach out and let's talk about what you're trying to do. {% embed url="" %} # How-tos Quick examples to help you get started using CsvPath Framework These quick how-to articles will jump-start specific use cases and configurations. They don't cover the full range of CsvPath Framework functionality. If you don't see a how-to you're looking for[ please ask here](/getting-started/a-helping-hand). And if you think your scenario needs more support from the Framework [please let us know](/getting-started/a-helping-hand). There are more how-tos in the [FlightPath Data docs](https://www.flightpathdata.com/docs.html) covering the app and how you use it develop for and manage the Framework in production. FlightPath also has [a bunch of built-in examples](/getting-started/the-flightpath-data-examples). Checkout [https://www.flightpathdata.com](https://www.flightpathdata.com/) for more. # Storage Backends [As described here](/topics/higher-level-topics/organizing-inbound-data/the-three-data-spaces), CsvPath Framework stores files in three protected spaces: * Source file staging (a.k.a. the named-files area) * The csvpaths area (a.k.a. the named-paths area) * The archive Each of these areas can live in any of the five supported backends: * File system * [SFTP](/topics/how-tos/storage-backends/s3-sftp-google-cloud-or-azure) * [AWS S3](/topics/how-tos/storage-backends/aws-s3) * [Azure](/topics/how-tos/storage-backends/azure) * [Google Cloud Storage](/topics/how-tos/storage-backends/google-cloud-storage) CsvPath Framework generates events during staging named-files, loading named-paths, and running named-paths groups against named-files to populate the archive. Events are stored in the file backends as `manifest.json` files. In addition to your main file backend options, you can also choose to store events in a [SQL database](/topics/how-tos/postgres-and-mysql) and/or send them to an observability system that supports [OpenTelemetry](/getting-started/dataops-integrations/opentelemetry) or [OpenLineage](/getting-started/dataops-integrations/openlineage). # AWS S3 CsvPath's three file stores can be local or on S3. Mix and match!
{% hint style="info" %} All five backends work exactly the same. The main difference is in the protocol part of the URI: * azure:// * sftp\:// * s3:// * gs\:// * / or c:\\ *(HTTP is read-only, so doesn't count as a storage backend. It is only for loading files.)* Each backend has its own way of storing credentials, of course. In the case of SFTP there is a form in the config panel's integrations tab. The three cloud stores use env vars that you can set in the OS, in FlightPath Data, or in a FlightPath Server project. {% endhint %} There are three places CsvPath keeps files: * [The archive](/topics/practical-stuff/namespacing-with-the-archive) — the place where results live * [Inputs](/topics/higher-level-topics/data-validation-files-and-storage/named_files_and_paths): * Named files — all the source Excel and CSV data files * Named paths — your named groups of csvpaths Each of these can be independently placed. By default Archive is the folder at `./archive`. Named-files and named-paths default to: * `./inputs/named_files` * `./inputs/named_paths` All of these locations and names can be changed. Keep in mind that the Archive takes its name from the last part of its path. That means that if you put your archive at `./this/is/my/stuff` your archive will be named `stuff`. In most cases that doesn't matter, but when we're tying into other systems, such as the CKAN or Marquez integrations, the archive name is meaningful. How do you set up the three file storage locations? Easy, just change [your settings](/topics/how-tos/config.ini). There are three settings in `config.ini`. By default config.ini is in `./config/config.ini`. The settings you need are in the `[results]` and `[inputs]` sections. Archive is set under `[results]` with the archive key. Named-files and named-paths are set under `[inputs]` using the `files` and `csvpaths` keys, respectively.
If you'd like your results to go to an archive in S3 all you need to do is set the `archive` key to an S3 URI like: ```ini archive = s3://csvpath-example-1/archive ``` Using this setting would send all results to the `csvpath-example-1` bucket with paths beginning with `archive`. (S3 doesn't truly have directories, but in effect, everything goes into the `archive` directory). Likewise, to store your source data in S3 you would set a key like: ```ini files = s3://csvpath-example-1/inputs/named_files ``` As usual with AWS you will need to authenticate at the command line. This can be as simple as exporting your SK and AK as env vars. See AWS's docs. Bear in mind that as soon as you separate your data and compute you incur a network latency cost, as well as actual dollars and cents costs. There are ways to mitigate the latency and moving your compute to AWS along with your data is likely to be a big help. All-in-all, using S3 is sweet, but as with any work you do using CsvPath, try, test, automate, and iterate. # Azure Trusted publishing using Azure blobs is really quite straightforward. This page is really just here to point to [the S3 instructions](/topics/how-tos/storage-backends/aws-s3) because everything is basically the same. You need a `AZURE_STORAGE_CONNECTION_STRING` environment variable holding your storage account connection string. Other than that, there's no additional setup — just use of `azure://` instead of `sftp://` or `s3://` in `config/config.ini` or when you add content with `FileManager` or `PathsManager`. So easy!
A screenshot of the Azure Storage Explorer app showing CsvPath Framework CSV and JSON files
# Google Cloud Storage Trusted publishing using Azure blobs is really quite straightforward. This page is really just here to point to [the S3 instructions](/topics/how-tos/storage-backends/aws-s3) because everything is basically the same. You need a `GCS_CREDENTIALS_PATH` environment variable. It points to the location of your service account JSON credentials file. Other than that, there's no additional setup — just use of gs`://` instead of `sftp://` or `s3://` in `config/config.ini` or when you add content with `FileManager` or `PathsManager`. Super easy!
A screenshot of the Azure Storage Explorer app showing CsvPath Framework CSV and JSON files
# HTTPS You can register named-file content using HTTP or HTTPS in the same way that you would using S3, SFTP, or the local filesystem. While adding files by HTTP is a snap, the remote file name in the URL may not be helpful. CSV and Excel files on the web sometimes come out of applications. When they do they may lack a regular file name. Luckily there's an easy way to update the registered content with a name. ## Happy path First, let's create a simple harness. Our goal is to register a file from the web in CsvPath Framework. We are importing it, or staging it, as a named-file. For context we'll run a csvpath against our new content and access the results. {% code lineNumbers="true" %} ```python from csvpath import CsvPaths class Main: def load_http_content(self): paths = CsvPaths() paths.file_manager.add_named_file( name="orders", path="https://drive.google.com/uc?id=1zO8ekHWx9U7mrbx_0Hoxxu6od7uxJqWw&export=download", ) paths.paths_manager.add_named_paths(name="http_demo", paths=["$[*][yes()]"]) paths.collect_paths(pathsname="http_demo", filename="orders") results = paths.results_manager.get_named_results("http_demo") ``` {% endcode %} The main event is the method call starting on line 6 that adds a named-file called `orders`. The new version of `orders` is coming from a Google Drive account with a long opaque HTTPS URL. So far so good. ## Let's fix that name However, when we look at the registered file's manifest there is a gotcha. Our manifest is at `./inputs/named_files/orders/manifest.json`. (If you aren't using the default location for named-files your path will be different). When we open it we see:
The named-file manifest for a new item of CSV content.
Lots of things went right. Our time, uuid, from URL, and fingerprint are fine. But the file type should be `csv` and the file name and file home are garbled because the HTTP URL didn't point to a physical file so much as identify an item of content held by the Google Drive application. Since we know the data we're downloading is CSV data and we know what it is about, we can easily update the named-file to add clarity. We'll use the `patch_named_file` method. The `patch_named_file` method is on `FileRegistrar`. The `FileManager` that you use to add a named-file has a registrar to keep track of file metadata. It can help us easily compensate for HTTP's deficiencies. {% code lineNumbers="true" %} ```python from csvpath import CsvPaths class Main: def load_http_content(self): paths = CsvPaths() paths.file_manager.add_named_file( name="orders", path="https://drive.google.com/uc?id=1zO8ekHWx9U7mrbx_0Hoxxu6od7uxJqWw&export=download", ) paths.file_manager.registrar.patch_named_file( name="orders", patch={"type": "csv", "file_name": "download.csv"} ) paths.paths_manager.add_named_paths(name="http_demo", paths=["$[*][yes()]"]) paths.collect_paths(pathsname="http_demo", filename="orders") results = paths.results_manager.get_named_results("http_demo") ``` {% endcode %} The fix is line 10. We're passing a "patch" that will change the type of the file to `cvs` and the name of the file to `download.csv`. The `FileRegistar` updates the `manifest.json` so everything tics and ties. This is what you should see:
And you're good. The `orders` named-file is ready to work. Of course using HTTP to load content into a named-file doesn't always require the extra step to patch it. If you have a URL like `https://csvpath.org/my-data-file.csv` you won't need to help CsvPath know what the file name and file type are because it's obviously CSV data in a file called `my-data-file.csv`. But if you do need to make an adjustment, that's how you do it. # S3, SFTP, Google Cloud, or Azure Say you want to add a file currently sitting in S3 to CsvPath Framework as a named-file. That is to say, you want to give an easy name to a physical file and move it into CsvPath's inputs location from the bucket where it currently is in S3. CsvPath Framework stores incoming files in the named-files directory tree. This is your data file registry. The location of the named-files directory is set in config/config.ini's `[inputs]` section under the `files` key. The named-files directories can live in any of the CsvPath storage backends. At this time those are: * The local filesystem * S3 * SFTP * Azure Blob Storage * Google Cloud Storage The file you want to register is also in one of these storage systems. How can you get it loaded into named-files? Easy, just use: * A relative or absolute path to the file in the local system, or * A URL like `s3://bucket/name`, or * An `sftp://server:port/path/to/my/file` URL, or * A URL like `azure://container/key` or `gs://bucket/key` In the case of S3 and SFTP you also need to add credentials to CsvPath Framework and/or FlightPath. That will require one of these options: * For AWS, add an `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY` pair to the environment * For SFTP, credentials are set in the `[sftp]` section's `username` and `password` keys. Use all caps to reference environment variables.
Moving csv or excel files into CsvPath Framework from S3 to SFTP

This is just an example. You can mix and match storage backends as you like.

That takes care of the left-hand side of this picture. As you may already know, the right-hand side is setup simply using the `[inputs]` section and the `flies` and `csvpaths` keys. You can [read more about that here](/topics/how-tos/storage-backends/aws-s3). # Parquet How to use CsvPath Framework to output Parquet files
CsvPath Framework can create Parquet files for schema entities in tabular data files. *(I.e., CSV, Excel, JSONL, and data frames).* Parquet files are created in addition to the usual CsvPaths run output files. To output to Parquet you create a schema entity. This is similar to defining a table in SQL. Usually when we create CsvPath Language schemas we use the `line()` function. (Read [more about `line()` entities here](/topics/higher-level-topics/validation/schemas-or-rules) and also [see these examples](/getting-started/the-flightpath-data-examples/schemas)). Instead of `line()` we're going to use `parquet()` for the same entity creation, but this time with `.parquet` file output. For example, let's start with a `person` entity like this: ```json line.person( string.firstname(#0), string.lastname.notnone(#1) ) ``` This says that a `person` has a `firstname` and a `lastname`. `firstname` is populated from the first header, `#0`, and `lastname` from the 2nd header, `#1`. When we apply this schema to a data file the lines that fit this model match and are collected. We want to output the matching data to a `.parquet` file. We do this by creating our `person` entity using the `parquet()` function, instead of the `line()` function: ```json parquet.person( string.firstname(#0), string.lastname.notnone(#1) ) ``` When we apply this version of our schema to our data file, any lines that match the `person` entity are captured to a `person.parquet` file in the run dir. More specifically, the parquet file lands in the folder containing the outputs for the specific csvpath statement that includes our entity. So, for instance, our csvpath might look like this: ``` ~ id: person ~ $[*][ parquet.person( string.firstname(#0), string.lastname.notnone(#1) )] ``` That would result in output like this:
You can see that the `data.csv` is still created. `data.csv` captures all matching lines. Our `parquet()` entity is a schema entity that determines if a line matches. In that it is very much like using `line()`. However, the `person.parquet` file we are creating only captures the entity data, not the whole line. Using a Parquet tool we can query this file using ``` SELECT * FROM parquet_table ```
You can use as many `parquet()` entities as you like in a csvpath statement. And you can move Parquet files using [`transfer-mode`](/topics/practical-stuff/the-modes) or the [SFTP integration](/topics/how-tos/sftp/sftp-export), just like any run-generated file. As said above, the data captured is more specific than what is collected into data.csv. If you want to capture matching data irrespective of if a line matches, you can use the `nocontrib` qualifier. In this case, `nocontrib` is building a wall between a `parquet()` entity and the contributions of other match components, so that you capture matching data to Parquet, even if it comes from a line that won't get captured to `data.csv`. # Sqlite Capturing run events to Sqlite can make searching for answers faster
You can easily send your runs metadata to Sqlite as well as to `manifest.json` files. Why might you want to do that? Sqlite gives you the ability to quickly search for runs that match certain characteristics. It contains most of the data in the results and result events so you can more easily query at multiple levels across runs. Turning on the Sqlite backend is simple. It is pre-configured in your generated `config/config.ini`, when you create a new project. To add Sqlite to an existing project, pickup the latest version of CsvPath Framework from Pypi and add these to the `[listeners]` section of your `config.ini`: ```ini [listeners] sqlite.result = from csvpath.managers.integrations.sqlite.sqlite_result_listener import SqliteResultListener sqlite.results = from csvpath.managers.integrations.sqlite.sqlite_results_listener import SqliteResultsListener ``` Also in `[listeners]`, you need to add the `sqlite` group to the `groups` key. If for some reason you want to, you can run the `SqliteResultsListener` without the `SqliteResultListener`, but the reverse is not true. I can't think of a reason to just one of the listeners, though. Finally, you need a \[sqlite] section with a single key pointing to the file that will contain the database. This section and key will also be generated in any new `config.ini` created by the Framework. Or, you can add it like this, using any location you like: ```ini [sqlite] db = archive/csvpath.db ``` That's all there is to it. Your events will now be captured to two tables in the database: * `named_paths_group_run` * `instance_run` Recall that we use the terms `instance`, `identity`, and `csvpath` to refer to csvpaths that are managed by a `CsvPaths` object. The term `instance` stems from the object or instance of the `CsvPath` class. Identity refers to the optional but highly recommended `ID` or `name` metadata field you can add to your csvpath's external comments for better identification in error messages and logging. (E.g. to name a csvpath `my csvpath!` do something like: `~ id: my csvpath! ~ $[*][yes()]`) As you would guess, `named_paths_group_run` is the main table describing a run. It is created from the `results` event by the `SqliteResultsListener`. `instance_run` is the description of a csvpath's results within the name-paths group. `instance_run` is populated from the `result` event handled by `SqliteResultListener`.
Just for reference, the schema for the tables is here. You don't have to do anything with it, though. The tables are set up automatically when you enable the integration or anytime you delete the database. ```sql CREATE TABLE IF NOT EXISTS named_paths_group_run ( uuid varchar(40) PRIMARY KEY not null, at datetime not null, archive_name varchar(100), time_completed datetime, all_completed varchar(1) default 'N', all_valid varchar(1) default 'N', all_expected_files varchar(1) default 'N', error_count int, status varchar(20), by_line_run varchar(1) default 'Y', run_home varchar(250), named_results_name varchar(45), named_paths_uuid varchar(40) not null, named_paths_name varchar(45) not null, named_paths_home varchar(250) not null, named_file_uuid varchar(40) not null, named_file_name varchar(45) not null, named_file_home varchar(500) not null, named_file_path varchar(500) not null, named_file_size int default -1, named_file_last_change, named_file_fingerprint varchar(70), hostname varchar(45), username varchar(45), ip_address varchar(40), manifest_path varchar(250) ); CREATE TABLE IF NOT EXISTS instance_run( uuid varchar(40) PRIMARY KEY not null, at datetime not null, group_run_uuid varchar(40) not null, instance_identity varchar(100), instance_index int default -1, instance_home varchar(250) not null, source_mode_preceding varchar(1) default 'N', preceding_instance_identity varchar(100), actual_data_file varchar(500), number_of_files_expected int default -1, number_of_files_generated int default -1, files_expected varchar(1) default 'Y', valid varchar(1) default 'N', completed varchar(1) default 'N', lines_scanned int default 0, lines_total int default 0, lines_matched int default 0, error_count int default -1, manifest_path varchar(250) not null, FOREIGN KEY(group_run_uuid) REFERENCES named_paths_group_run(uuid) ); ``` Expanding the model to cover named-paths csvpath group loads and staging named-files is on the roadmap. # Postgres and MySQL Some multi-user archives may benefit from capturing events to the database
the logos of mysql, postgres, and sqlite
CsvPath Framework is great for both individual developer users and large groups of DataOps pros taking care of many data partnerships. The difference between these scenarios is important, however. You can read more about it here. ## Why go with SQL? There are three obvious reasons to consider setting up a database for CsvPath Framework: * Querying SQL is much more productive for some purposes, compared to going through the `manifest.json` files. [Sqlite also solves for this locally.](/topics/how-tos/sqlite) * A SQL database is an immutable record of changes; whereas, `manifest.json` files can be overwritten or deleted if the archive and staging areas are not protected * In some cases other applications can more easily use SQL to get CsvPath Framework data ## Filesystem overwrites and races In general, CsvPath Framework assumes it has privileged access to protected file system space for its archive, the source file staging area, and the named-paths area. That is absolutely the right way to go in production. In dev environments, though, things can certainly be a bit looser. If you are working with your peers in a shared archive or a shared source file staging or named-paths area you should consider using a SQL database to capture events. When you work on the same files you run the risk over overwrites. That is no different from any shared data system — for e.g. you can have the same problem in JIRA, Git, or Sharepoint. However, CsvPath's filesystem data writes are necessarily not all atomic. In certain cases, there is a race condition around file system writes. That is typically not a consideration for automated runs. When CsvPath is automated typically there is a single Python process working on a particular run, not two Python processes competing over one run. Moreover, most CsvPath Framework automations will have their own protected area namespaced by named-paths name or archive name or both. Still, in development environments and for some corner cases you might want your audit trail to have more immutability. For this the SQL databases have you covered. All metadata writes, whether to the `manifest.json` files in the file system, to a local Sqlite, or to a SQL database server, are handled by event listeners. You've probably seen the many optional listeners in your `config/config.ini`. When you write to the file system you overwrite any existing file in the location. But when your events go to SQL you are creating an even more immutable record that can disambiguate any cross-talk in the manifests due to a non-ideal setup of your automation jobs or a shared dev env without namespacing. ## Setting up SQL databases Setting up SQL events is not hard at all. Still, as you can tell, we would only suggest it if the benefit is worth running a server, managing the four tables, and distributing credentials. If you're just in need of better layout of your archive and/or named-paths, obviously that's the even easier and more correct thing to focus on. For most folks, that's all you need. And, if you're just in need of a way to query your results, [a local Sqlite may be a more lightweight solution](/topics/how-tos/sqlite). As you can tell, we're all about a low-friction [DX](https://en.wikipedia.org/wiki/User_experience#Developer_experience). Regardless, to configure for SQL what you need is: * The listeners are enabled * Your database URL is available in `config/config.ini` ### Configure the listeners Open `config/config.ini` and look for the `[listeners]` section. It should have the SQL listeners already. But if your config file is not newly generated, it might not. Copy the keys below into your file, if needed.
what your config file should look like. same as the text below.
```python sql.file = from csvpath.managers.integrations.sql.sql_file_listener import SqlFileListener sql.paths = from csvpath.managers.integrations.sql.sql_paths_listener import SqlPathsListener sql.result = from csvpath.managers.integrations.sql.sql_result_listener import SqlResultListener sql.results = from csvpath.managers.integrations.sql.sql_results_listener import SqlResultsListener ``` Then look for the `groups` key in `[listeners]` and add `sql` to it, separating with a comma, if needed. ### Configure the database URI Check for a `[sql]` section. If your `config.ini` isn't newly generated copy the `[sql]` section from below into your file.
screenshot of a config file, same as the text below

You may already have this SQL section if you generated your config recently

```ini [sql] dialect = mysql connection_string = mysql://csvpath:password1@192.168.1.1/csvpath ``` The `dialect` key value must be one of: * `mysql` * `postgres` * `sqlite` Under the hood we're using [SQLAlchemy](https://www.sqlalchemy.org/), so you can [look here for the connection string formats](https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls). As usual, you can use an ALL CAPS value to point to the environment var of that name. For e.g., `connection_string = CONNECTION_URI` would result in `connection_string` equaling the value of the `CONNECTION_URI` environment variable, if found. That's all the configuration needed. ## The database structure Your data is now ready to flow into the database. CsvPath Framework will create the database for you in the background. The database is `csvpath` and the tables are: * `instance_run` * `named_file` * `named_paths` * `named_paths_group_run`
screenshot of the mysql command line client
You are capturing: * `NAMED_FILE`: The same information as in the `manifest.json` created when you add a named-file * `NAMED_PATHS`: The same for named-paths * `NAMED_PATHS_GROUP_RUN`: The run manifest for the named-paths group run found in its `run_dir` (a.k.a. the run's home) * `INSTANCE_RUN`: The run manifest for each individual csvpath in the named-paths group being run The database is quite simple. It is nothing more than an immutable record (unless you mutate it outside of CsvPath) of the assets and runs your CsvPath Framework performs. That's all it needs to be.
an ERD generated in Mysql Workbench showing the four tables.
And there you have it. All your events captured to your favorite database. # Transfers Sending result data where you need it. CsvPath Framework attempts to fit seamlessly into the systems and workflows you already use. There are three main tools to achieve a level of transparencies to other, pre-existing systems: * Templates — the way to structure your staged and results files areas * Activations — linking file registrations to named-paths group runs * Transfers — putting result files exactly where collaborating systems expect them Transfers are instructions for CsvPath Framework to move results files to other locations in any of the backends supported, including arbitrary SFTP servers that are not configured as Framework backends. There are three types of files that can be moved: * Any standard result file * Any printouts or Parquet files, regardless of the name * The registered source data file that was used in the run There are two ways to set up transfers: * Transfer mode (a `transfer-mode:` directive in a csvpath's leading comment) * In the named-paths group's `definition.json` file Transfer mode is more limited, but it can be configured by the csvpath writer on a csvpath-by-csvpath basis. That is a powerful feature, since a csvpath writer may not control when their csvpaths are deployed to production. It may also be helpful when composing small csvpaths into larger rule sets, as we advocate you do for better testing. Defining transfers at the named-paths group gives you a bit more. There, you can set transfers to run only when a status is met. We use the same statuses with transfers that we use elsewhere: * All runs * Valid runs * Invalid runs * Runs with errors As usual, remember that a valid run can have errors and an invalid run may have no errors. Errors and validity are separate concepts in CsvPath Framework for good reasons covered elsewhere. #### The Configuration Is Simple, Really! While the `definition.json` file has increasingly many capabilities, and despite transfers being a flexible feature, the configuration is relatively simple. The overall look of the file follows this example: ```json { "maths": [], "_config": { "groups": { "maths": { "transfers": { "path_transfers": { "hello world": { "on_complete_all": [ { "file": "data", "transfer_to": "foo" } ], "on_complete_invalid": [], "on_complete_valid": [], "on_complete_error": [] }, "minus and subtract": { "on_complete_all": [ { "file": "printouts", "transfer_to": "bar" } ], "on_complete_invalid": [], "on_complete_valid": [], "on_complete_error": [] } } } } } } } ``` Here you can see that we're sending the standard `data.csv` and `printouts.txt` files to the value of variables generated in the csvpaths, `foo` and `bar`. The equivalent transfer mode would be: ``` ~ transfer-mode: data > foo ~ ``` However, with transfer mode we don't have the run state option. We also don't have a consolidated and consistent transfers definition, as we do in `definition.json`. So, how is that easy? The answer is, use FlightPath Data to create your transfers. There is a transfers setup form that truly simplifies things. To find it, first load a named-paths group using the context menu in the project tree on the left. Right-click your `.csvpaths` file or a directory containing csvpaths and click the `Load csvpaths` item. Then, once you created the named-paths group, right-click on it in the right-hand middle window and select `Set transfers`.
The transfers dialog opens showing the four run end-states on the left and the transfers for each on the right. Below the list of transfers is a form providing the csvpaths available, the files available (which you can edit to input arbitrarily named files), and csvpath variable that contains the transfer path.
When you click the Set button your data will be transfered to the definition.json file, eliminating the need to write deeply nested JSON. Transfer paths are the same as the paths you use to configure backends. I.e. you can have: * Filesystem paths * `s3://` for AWS S3 * `azure://` for Azure Blob Storage * `gs://` for Google Cloud Storage * `sftp://` for SFTP S3, Azure, and GCS rely on your backend-configured accounts. SFTP is more interesting. As usual, we match on the server address and port configured in the Config > Integrations > SFTP form in FlightPath Data or `[sftp]` in `config.ini`. But if we don't have a match there, we look in the named-paths group's definition.json for any SFTP servers that are configured there specifically for transfers. To set those up, right-click your named-paths group and select `Set SFTP sources`.
As with `transfer-mode:`, we use variables, rather than hard-coded paths. This lets the csvpath writer, or the data source, determine where the files land. And that brings us back to the top. Why do we transfer, when we have a versioned, immutable results repository with a REST API access? The main reason to transfer is because most companies have a pre-existing file handling solution. It may not be the ideal solution — else why would you be looking at CsvPath Framework? — but the components of the solution are looking for files in a certain place, so it would be nice if they found them there. Moreover, pushing result files to a mutable working directory location helps further separate CsvPath Framework's Archive from day-to-day file wrangling. # File System Transfers Exporting files from CsvPath Framework runs to other filesystem locations {% hint style="info" %} As of 0.0.598, transfer-mode can export any of the run results files. Previously it only handled `data.csv` and `unmatched.csv`. Now you can transfer *(i.e. copy)* your `printouts.txt`, `errors.json`, `.parquet` files, and any of the other data and metadata created. {% endhint %} Files in the Archive are intended to stay put until they age out or are otherwise administratively handled. A csvpath writer or DataOps engineer shouldn't be expecting to move files from the Archive to somewhere else — the Archive is immutable. Nevertheless, the need is common. There are four ways to get your run outputs to a certain location: * Copy files from the Archive using some automation, leaving the originals in place — this might mean configuring your named-paths group to run scripts or call external webhooks * Use CsvPath's transfer function, `transfer-mode`, to direct a copy of a data file to an external location * Change your archive backend and/or use templates to land the data in the right place in the right system * Use the SFTP integration to export *(again, meaning copy)* the files by SFTP Any of these approaches is fine, of course. This how-to is about the second one: `transfer-mode`. A transfer copies a `data.csv` or `unmatched.csv` or another run-generated artifact to a dynamically chosen location. When you transfer a file you are copying the contents to another location, not moving it. The original file always stays where it is created in the run. The way you do a transfer involves [setting a mode](/topics/practical-stuff/the-modes): `transfer-mode`. That's right, you set up transfers on a csvpath-by-csvpath basis. Transfer mode is like any of the modes. It is set in an external comment. An external comment is a comment that is above or below the csvpath, not within the match part of the path. Setting transfer mode looks like: ```xquery ~ id: transfer test transfer-mode: data > my-file-var ~ $[*][yes()] ``` What that `transfer-mode` value means is that you are going to copy the `data.csv` file from you csvpath instance's home directory to the path that is the value of the `my-file-var` variable. You can set `my-file-var` (or whatever you name the variable) statically or dynamically. Either way works fine. Here are two examples: ``` ~ id: transfer test transfer-mode: data > my-file-var ~ $[*][ @my-file-var = "march/orders/update.csv" ] ``` or: ``` ~ id: transfer test transfer-mode: data > my-file-var ~ $[*][ @my-file-var = concat( now("%M"), "/orders/update.csv") ] ``` Transfers always go to a location below a transfer directory. You set the root transfer directory in `config/config.ini` in the `[results]` section with a `transfers` key. For example, ```ini [results] archive= TinPenny_toys transfers= /Users/tptuser/cvspath/projects/transfers ``` In our first variables example above, the full transfer path the `data.csv` file would be copied to would be: ``` /Users/tptuser/cvspath/projects/transfers/march/orders/update.csv ``` Files that are transferred can append an existing file. This can be useful if you are assembling data from a series of csvpaths. The first csvpath in the series might transfer its data.csv to an output.csv file in the transfer dir as a new file. A second csvpath, either in the same named-paths group or another named-paths group, could then append its data.csv or unmatched.csv to the same output.csv file. The way you do that is by appending a `+` to the variable name. Let me sum up a few things to remember about `tranfer-mode`: * It is a mode set in an external comment as `transfer-mode:` * Transfer mode copies content to another file, it doesn't move the original * The copy goes to a file under the transfer directory pointed to by the `transfers` key in `config/config.ini` * Transfer mode supports all the storage backends * The file path within the transfer directory is set in the variable you named in your `transfer-mode` metadata * You must populate the variable for transfer mode to work. You can do this statically in your csvpath. We expect that in many cases the value will be constructed dynamically. * A `+` appended to the variable name means that the transfer should append to the file indicated, if it exists. # AWS Lambda Setup a Lambda to automatically process files arriving to an S3 bucket
AWS's parts bin may be the ultimate for DIY MFT. The components include: * Transfer Family for secure transfer * IAM and Identity Center for authentication * S3 for storage, aging, retention, etc. * Lambda for processing Of course there are many other services that might also come into play. AWS has more than everything you need. That said, some assembly is required and batteries are not included. We can make setting up data onboarding a bit easier by showing how an automatically triggered lambda can enable CsvPath to pick up a newly arrived file for registration, validation, canonicalization, and publishing. When you have finished this page you will have a fully automated a file onboarding process. What we do on this page builds on the [description of CsvPath's S3 capabilities here](/topics/how-tos/storage-backends/aws-s3). ## The steps we are about to take The steps are straightforward: * Create two S3 buckets * Create the lambda * Create a CsvPath lambda layer * Load your named-paths group * Drop a data file to test Before we start, caveats abound. Primarily, most of these steps are deep topics and this isn't a complete tutorial on setting up infrastructure in AWS. We can get you close to the beach, but you are definitely going to have to get your feet wet. If that is not what you're looking for, try one of CsvPath's other integrations that might be quicker. SFTP and SFTPPlus are worth a look. Another caveat, we're focused on the simplest automated thing that could possibly work. This is not typically how you would go about setting up a production environment. If you haven't already, check out [Terraform](https://www.terraform.io/) and/or CloudFormation. ## Create the buckets We will use two buckets. One for inbound files and one for CsvPath assets. Our assets bucket will hold the results archive and the two inputs directories for files and csvpaths. 1. Log into AWS and navigate to S3 2. Create two buckets with default settings. Make one have a name that tells you the bucket is for inbound data. The second bucket's name should indicate that it holds a CsvPath assets. Our requirements are basic. For all S3's innumerable options, the buckets should be a snap. ## Create the lambda
1. Open the Lambda service and click the `Create function` button 2. Use a name that tells you this lambda is for inbound data processing 3. Allow AWS to create a new role for the lambda 4. Pick Python as the platform. Either AMD or ARM works. 5. Link the inbound bucket to the lambda. Click `Add a trigger`. In the `Trigger Configuration` page select S3 as your source. Pick your inbound bucket. All object create events should be selected. Acknowledge the warning about using one bucket for inputs and outputs. A write-event-driven lambda-bucket pair that reads and writes to the same bucket cause endless loops. There are some safeguards, but it is a problem you definitely want to avoid. We have two buckets, one for arrivals and one for CsvPath's own use. The arrivals bucket triggers the lambda and the lambda writes to the CsvPath framework's bucket. No loops. 6. In the `Configuration` tab, select `General configuration` and click `Edit`. Change the `Timeout` field from 3 seconds to 3 minutes. The way we are setting up CsvPaths for this example our runs will need more than 3 seconds. We're done with the core of creating the lambda. Before we configure our lambda, let's create the lambda layer that provides the dependencies we need. ## Create a lambda layer Lambda layers make it easy to setup your dependencies and share them across lambdas. You can [read about lambda layers here](https://docs.aws.amazon.com/lambda/latest/dg/chapter-layers.html). Creating a lambda layer is pretty straightforward. While our way works, we're no experts and cannot guarantee there isn't a better way to do it. As elsewhere on this site we'll use Poetry for our Python projects. If you prefer another way, by all means do that. We'll create a Poetry project to create the lambda layer. Typically the project would be a home for our lambda, but in this case we'll just use the Lambda service's `Code Source` tab to write the actual lambda. Instead, this project will give us an empty wheel that defines all its dependencies. We'll then install the wheel, along with its dependencies, and zip up the directory as our layer. 1. Create a Poetry project: `poetry new tin_penny_toys_orders` 2. `cd` into your new project 3. Add the `csvpath` library: `poetry add csvpath` 4. Do a Poetry build with: `poetry build` 5. `cd` into the `dist` directory and run these commands: ```bash mkdir layer poetry run pip install --upgrade -t layer/python ./*.whl cd layer ; zip -r ../lambda_layer.zip python -x '*.pyc' ``` You should see a `lambda_layer.zip` containing your dependencies. This is the artifact we upload to make the lambda layer. 1. Back in the Lambda service, in the left column nav click on `Layers` 2. At the top right, click on `Create layer` 3. Give your layer a name that you'll remember and upload your `lambda_layer.zip` file 4. Click the `Create` button at the bottom right 5. When the create succeeds you will be in the new layer's page. Look at the `Versions` table. There is one version. Copy its ARN. 6. In the left-hand nav, click on `Functions` and open the inbound function you created a few moments ago. 7. At the bottom of the `Code` tab is a table of layers. Click the `Add layer` button at the bottom right. 8. In the `Add layer` form's `Choose a layer` box, select `Specify an ARN`. Paste your new layer's ARN in the field below. We're doing well. Just a few more steps! Some code and then some permissions. ## Code the lambda The lambda code and configuration is pretty minimal. Here's the Python part: {% code lineNumbers="true" %} ```python import json import os import traceback import logging import boto3 from csvpath import CsvPaths from csvpath.util.box import Box client = boto3.client('s3') def lambda_handler(event, context): with Box() as b: b.add("boto_client", client) logger = logging.getLogger() logger.setLevel(logging.INFO) paths = CsvPaths() namedfile = os.environ['file'] namedpaths = os.environ['paths'] newfile = f"s3://{event['Records'][0]['s3']['bucket']['name']}/{event['Records'][0]['s3']['object']['key']}" paths.file_manager.add_named_file(name=namedfile, path=newfile) paths.collect_paths(filename=namedfile, pathsname=namedpaths) return { 'statusCode': 200, 'body': json.dumps('Success! Check the archive.') } ``` {% endcode %} Here's what's going on: * Line 9: we get a `client` that we'll use to interact with S3. We get it up here and share it below through the `Box` to provide access to all the components that need to use it. * Line 17: `paths` is the core of our Lambda. It coordinates all the configuration and runs the named-paths group. * Line 18, 19: `namedfile` and `namedpaths` name our assets in S3. We're getting the values from the environment. We haven't set the environment up yet. * Line 20: `newfile` is the S3 URL of our arriving file. It is coming from the event that S3 called our lambda with. * Line 22: we have a new file so we need to register it under the name we use for Tin Penny Toys's orders. We'll configure the name when we setup env vars. * Line 23: this is where the run happens. We apply our named-paths to this new orders file. ## Config, Config There are two sets of configuration to do. We need to add our `config.ini` and we need to set up env vars. Jump back to the Code source tab. 1. Right click in the explorer and add a file. Call it `config.ini` 2. Paste in the configuration below, changing it to match your names and URLs. (This is pretty stock, but if you want to generate your own default just run the CLI to give CsvPath a chance to create one. We do this below in a moment). {% code lineNumbers="true" %} ```ini [csvpath_files] extensions = txt, csvpath, csvpaths [csv_files] extensions = txt, csv, tsv, dat, tab, psv, ssv [errors] csvpath = collect, fail, print csvpaths = raise, collect [logging] csvpath = debug csvpaths = debug log_file = /tmp/csvpath.log log_files_to_keep = 100 log_file_size = 52428800 [config] path = config.ini [cache] path = . use_cache = no [functions] imports = functions.imports [listeners] groups = #slack, marquez, ckan, sftp, sftpplus # add slack to the list of groups above for alerts to slack webhooks slack.file = from csvpath.managers.integrations.slack.sender import SlackSender slack.result = from csvpath.managers.integrations.slack.sender import SlackSender slack.results = from csvpath.managers.integrations.slack.sender import SlackSender [slack] # add your main webhook here. to set webhooks on a csvpath-by-csvpath basis add # on-valid-slack: webhook-minus-'https://' and/or # on-invalid-slack: webhook-minus-'https://' webhook_url = [results] archive = s3://csvpath-order-management-archive/archive [inputs] files = s3://csvpath-order-management-archive/inputs/named_files csvpaths = s3://csvpath-order-management-archive/inputs/named_paths on_unmatched_file_fingerprints = halt ``` {% endcode %} Here are the important things: * **Lines 44, 47, 48**: make these point to your CsvPath S3 buckets. Your arriving files come into the other bucket. When you stage a newly arrived file it will be copied into the inputs/named\_files directory. * Lambda has a read-only file system. The only locally writable space is `/tmp`. We need to make changes to adapt to that. * **Line 23**: turn off caching. We could point the cache to `/tmp/cache`, but in this case we're setting up a completely automated production system where we don't anticipate starting runs iteratively and watching them run, as we would do in development. Given that, the cache doesn't help us. * **Line 19**: make the config file live at the root of the lambda, just where it is. * **Line 14**: point the log to `/tmp` * **Lines 8, 9**: remember to change your error handling setting to match what you're looking for. In production we often don't `raise` errors because we don't want to halt processing. Likewise, we may not want to `stop` on error. And that's it. Your `config.ini` should be all set. Now let's do the env vars. Click on the `Configuration` tab, Select `Environment variables` in the left-hand nav, and click `Edit`. We need to add three vars: * `CSVPATH_CONFIG_PATH` = `config.ini` * `file` = `orders` * `paths` = `orders` The first, `CSVPATH_CONFIG_PATH`, is needed to tell the `CsvPaths` instance where to find config.ini. If we didn't provide it, `CsvPaths` would look at the default location, `config/config.ini`, which doesn't work for a lambda. The other two vars are giving the logical name of our inbound files and the named-paths group name that identifies the set of csvpaths we want to run on each new orders file.
That's it. Our lambda configuration is done. But we still need to setup the right permissions for the Lambda service to access S3. ## Setup the permissions Still in Lambda, click into the `Configuration` tab, if you're not already there. Select `Permissions` in the left-hand nav. You can see the role your lambda is using on the right-hand side. In the `Resource Summary` you can see it has permission to work with CloudWatch already. We need it to also have permission to work with S3. Click the role name to open the role in IAM. In the `Permissions policies` box you can see a narrowly defined permission for the role to work with logs. We're going to allow the role to also work with S3. Because this is a quick example, we're not going to be careful to provide least privledge. Instead we're just going to make it work. You should of course be more careful in general, and definitely more thoughtful in production. * Click Add permissions, Attach policies * Type `s3f` in the `Other permissions policies` search and you should see `AmazonS3FullAccess`. Check that policy and click `Add permissions`. You should get a message that the policy was attached to the role and you should see the two policies the role has listed.
Now let's set up our named-paths. ## Create and load the named-paths Back in your local Python project let's setup our named-paths. If you have named-paths already loaded you can use those. But supposing we don't have any already loaded? Let's see what to do. First fire up the CLI to make sure we have `config.ini` and other assets created. Do: ``` poetry run CLI ```
Select `quit`. We just needed to give CsvPath the chance to generate a config file. Open `config/config.ini` and make one change. Set `[inputs] csvpaths` equal to your named-paths location in your CsvPath assets bucket. It will be the same path as you used in `[inputs] csvpaths` in your lambda's `config.ini`. It might look like: ```ini [inputs] csvpaths = s3://csvpath-order-management-archive/inputs/named_paths ``` Everything else is fine as-is. Remember to make sure you have your secret key and access key environment variables setup. AWS looks for `AWS_ACCESS_KEY_ID` and `AWS_SECRET_ACCESS_KEY`. Now create a simple csvpath called `orders.csvpath`. Put it in a `named_paths` directory at the root of your project. Since the point here is to wire up AWS let's just go super trivial on the CsvPath Language. Create the file with: ```xquery ~ id: hello-world ~ $[*][ print("hello world!") ] ``` This path will be named `hello-world` and it will print `hello world!` once for every line of every file it sees. Now fire up the CLI again and let's push the named-paths to S3. Do `poetry run cli` again. 1. Select `named-paths` 2. Select `add named-paths` 3. Type in `orders` as the name of the named-paths group 4. Pick `file` 5. Drill down to select `./named_paths/orders.csvpath`
There should be a brief pause as your file uploads. Then you should be able to see it in S3 under the `named_paths` directory. Now we're ready to test an inbound file arrival. ## Try it out! Testing this setup is easy. Just navigate to your S3 bucket and upload a file. You should see a log begin spooling in the lambda's `Monitor` tab. And you can watch your results bucket to see that you successfully staged your named-file and created the named-paths group results. There are many ways to optimize this setup. What you have is the quick-get-something-working version. Now that you can see CsvPath basically working, you can think about how to iteratively improve your solution. # Webhooks Calling a webhook at the end of a named-paths group run is a straightforward way to integrate systems Your named-paths groups can be configured to call up to four webhooks at the end of a run. You can use this capability to easily trigger workflows that send emails or load webapps or move files, and much more using webhook calls to any of the dozens of automation platforms like Zapier.
The four types determine if a request is made to a particular hook. The types are: * All — a webhook call is made at the end of every run * On invalid — a call is made if there are any invalid csvpaths in the named-path group run * On valid — a call is made if all csvpaths in the named-paths group are were found to be valid * Errors — if there are errors, the concatenated contents of all the `errors.json` files, as well as any webhook params, are sent to the hook The configuration you need to make is in the named-paths `definition.json`. The `definition.json` file is where you can define named-paths groups as dictionaries like: ```json { "numbers": [ "tests/test_resources/named_paths/zips.csvpaths", "tests/test_resources/named_paths/select.csvpaths" ], "needs split": [ "tests/test_resources/named_paths/zips.csvpaths" ], "_config": { } } ``` You can name your JSON file whatever you like. CsvPath Framework will copy it into a `definitions.json` file when you load your named-paths groups using the file. Each of the named-paths groups defined in the JSON file will get its own copy of the JSON file. You can add your webhooks configuration to your JSON file by hand or you can add a dict to the loaded JSON file (i.e. CsvPath Framework's definition.json, not your original file) using a method on PathsManager. Either way, everytime you update your named-paths group with its JSON file you will rewrite the config, so you can work in your own file and load it into CsvPath with the PathsManager as often as needed. ## The Python configuration method The PathsManager method is: ```python from csvpath import CsvPaths cfg = { "on_complete_all_webhook":"type > all, me > var|me, name > meta|name, time > var|now" "all_webhook_url":"http://localhost:8000/json-hook" } CsvPaths().paths_manager.store_config_for_paths("hooks_test", cfg) ``` store\_config\_for\_paths overwrites the whole config for that named-paths group. If you want to keep your existing config, while modifying it, use: ```python from csvpath import CsvPaths paths = CsvPaths() paths.paths_manager.get_config_for_paths("hooks_test") # config update goes here paths.paths_manager.store_config_for_paths("hooks_test", cfg) ``` ## The JSON configuration method Alternatively, you can just edit your named-paths group JSON definition file to have the webhook config values. Then you just load your named-paths group as normal using: ```python from csvpath import CsvPaths CsvPaths().paths_manager.add_named_paths_from_json(file_path="./my_named_paths.json") ``` The webhooks configuration is goes in the `_config` *key.* `_config` is a dictionary that has keys for each named-paths group that has config information. The keys have a dict of config values. Your webhook config might look like this: ```json "_config": { "hooks": { "on_complete_all_webhook":"type > all, me > var|me, name > meta|name, time > var|now", "on_complete_valid_webhook":"type > valid, me > var|me, name > meta|name, time > var|now", "on_complete_invalid_webhook":"type > invalid, me > var|me, name > meta|name, time > var|now", "on_complete_errors_webhook":"type > errors, me > var|me, name > meta|name, time > var|now", "all_webhook_url":"http://localhost:8000/json-hook", "valid_webhook_url":"http://localhost:8000/json-hook", "invalid_webhook_url":"http://localhost:8000/json-hook", "errors_webhook_url":"http://localhost:8000/json-hook" } } ``` If you use the `store_config_for_paths()` method of adding the webhooks config and don't add csvpath files to your named-paths group in a JSON definition, then this would be all your `definitions.json` file would contain. In this example, the `hooks` key is a reference to a named-paths group named `hooks`. All the four types of webhooks are shown here. They are: * `on_complete_all_webhook` * `on_complete_invalid_webhook` * `on_complete_valid_webhook` * `on_complete_errors_webhook` Each one has a corresponding webhook URL which is what gets called. The values under the main hooks keys (`on_complete_all_webhook`, etc) are value pairs. We use this same format for some of the modes. The data structure is a comma-separated list of name-value pairs, with some token substitution options. Each pair is like: ``` name > value ``` You can read this as: *name points to value*. Each value can be in the form `meta|name` or `var|name`. A `meta|` value will come from the external comment metadata fields of a csvpath. The `var|` value will come from the csvpath variables. Since we're executing webhooks at the end of a run, not in the context of a particular csvpath, you have to keep in mind that all the metadata is bundled together with the later csvpaths in the group potentially overwriting metadata values set by earlier csvpaths. Likewise with variables, you're working from a superset of variables where the last variable setter wins. These are all valid examples: ```json name > meta|name, date > var|today, content-type > text/plain, token > MY_KEY ``` The `content-type` key-value is static. The token key's `MY_KEY` is converted, if possible, to the value of the `MY_KEY` environment variable, if one exists. This attempted conversion to an env var value happens whenever a value is in all caps. These key-value pairs are added to the JSON dictionary that forms the payload of the webhook post. At this time the webhook feature only supports `POST` requests. In the case you send to an errors webhook, your dict will additionally have an `errors` key with a list of all the errors from the run. When your webhook is configured with: ```html "on_complete_all_webhook":"type > all, me > var|me, name > meta|name, time > var|now" ``` You will see a payload like this: ```json { "type": "all", "me": "one", "name": "my csvpaths", "time": "2025-04-03 14:11:23.353189+00:00" } ``` # Airflow An Easy Way To Add FlightPath Server API To Your Airflow Project
The [FlightPath Server](https://www.flightpathdata.com/) [Airflow provider lives here](https://github.com/flightpathdata/airflow-provider-flightpath). Watch this space for more how-to info. In the meantime, start with the repo's README.md. # Slack Send yourself notifications about CsvPath runs via Slack webhooks
CsvPath can send alerts to Slack as run events happen. It looks basically like this:
## Event Types To recap, there are five event types. Each event goes to listeners. CsvPath has its own listener that creates new or updated manifests. The types are: * **Named-file staging**: a `file` event is fired at the time you add a file to the file inputs directory using the file manager. * **Named-paths loading**: a `paths` event is fired when you load a named-paths group into the inputs directory using the paths manager. * **Run start**: when a run starts a `run` notification indicates at the highest-level the inputs and start time. It tells you that an activity is happening in the archive. * **Results available**: `results` events are fired when at the beginning and end of a named-paths group run. It is summary-level information about the group's progress. * **Result available**: a result `event` is fired when an instance of a csvpath in a named-paths group starts or completes. This is the most detailed event. ## Configuring Listeners All of these events are received by all listeners configured in `config/config.ini` to listen for events of a type. For example, the Marquez OpenLineage listeners are configured like this: ```ini marquez.file = from csvpath.managers.files.file_listener_ol import OpenLineageFileListener marquez.paths = from csvpath.managers.paths.paths_listener_ol import OpenLineagePathsListener marquez.result = from csvpath.managers.results.result_listener_ol import OpenLineageResultListener marquez.results = from csvpath.managers.results.results_listener_ol import OpenLineageResultsListener ``` There is a Slack listener that can receive any of those same four events. (Neither Marquez or Slack support the very general `run` event). To configure Slack you simple add any or all of these lines to your `config.ini` file: ```ini slack.file = from csvpath.managers.integrations.slack.sender import SlackSender slack.paths = from csvpath.managers.integrations.slack.sender import SlackSender slack.result = from csvpath.managers.integrations.slack.sender import SlackSender slack.results = from csvpath.managers.integrations.slack.sender import SlackSender ``` You will also need to add or update a `[slack]` section to say what webhook you want the events to go to. That looks like this: ```ini [slack] # add your main webhook here. to set webhooks on a csvpath-by-csvpath basis add # on-valid-slack: webhook-minus-'https://' and/or # on-invalid-slack: webhook-minus-'https://' webhook_url = ``` Obviously you need to add your webhook URL. And finally you need to tell CsvPath that you want the `slack` group of event listeners to receive events. Do that by adding slack to the `groups` key in the `[listeners]` section of `config.ini`. ```ini [listeners] groups = slack, marquez ``` ## Using Csvpath-by-csvpath Webhooks As the comments in your config file say, you can also configure the result Slack events on a csvpath-by-csvpath basis. Only the `result` events can be configured by a csvpath. That is because a `result` event is tied to a single csvpath; whereas, the other events apply to named-paths groups of csvpaths or to input files. Within your csvpath you need an external comment. An external comment is one that is above or below the csvpath, not within the match part of the csvpath. In the external comment you may use one or both of the custom metadata fields that the Slack integration knows to look for: * `on-valid-slack:` * `on-invalid-slack:` The value of the field is a webhook URL. When you add these fields remember to only give the URL starting with the subdomain and domain. So rather than: ```url https://hooks.slack.com/services/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 ``` You just use: ```xquery ~ id: Slack example on-valid-slack: hooks.slack.com/services/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 ~ $[*][print("hello world!")] ``` The reason to use the shorter form is because a full URL has a protocol signifier that includes a colon. Since CsvPath metadata fields are defined as names followed by a colon, `https://...` looks to CsvPath like a metadata field named `https`. If neither of these metadata fields is present, your event will go to the default URL in `config/config.ini`. As you would guess, if your csvpath is valid — per the `valid` field in the metadata collected during the run — the `on-valid-slack` webhook is called. If not `valid` the `on-invalid-slack` webhook gets the call. ## A Reminder About Validity And in case you don't remember, you set the `valid` value using the `fail()` function. A csvpath is considered valid by default. Under certain circumstances it may have indications that something is wrong (e.g. the expected files not generated and stopped early indicators) which generally you see in the metadata and/or as errors in `errors.json`. But unless you explicitly say a file is invalid, it is valid. That said, bear in mind that built-in validations, when tripped, can mark a file as invalid. For example, if you try to `add("five", none())` you will raise an error and depending on your [mode settings](/topics/practical-stuff/the-modes), your file may be marked invalid without you having to do anything. In that case, if you had `on-invalid-slack` configured with a webhook, you would get an alert. # Scripts

A script and its output captured to a timestamped file after a run is complete

Running a script at the end of a named-paths group run is a common need. Setting it up is a straightforward configuration file change plus one `PathsManager` method call. ### Configuration In `config/config.ini` you need a couple of things: * The listener enabled * The `[scripts]` section with two keys First, make sure you have the `[scripts]` section. If your `config.ini` file is newly generated from the most recent point release, it will be there. Otherwise, add it like this:
The `run_scripts` key is enables or blocks all script running. By default script running is blocked. To run scripts you must have the value `yes`. The `shell` key is optional. CsvPath Framework uses it to create a shebang in the first line of your script if it doesn't see one. You can set `shell` to blank or remove the key, if you don't think it would be helpful. You also need the listener import line at `[listeners] scripts.results`. Again, if your project is new you may have it. Otherwise, copy and paste from the code below. Next, let's make sure the listener is active.
The listener group is `scripts`. There is only one event type that executes scripts: `results`. You need the `scripts.results` key under `[listeners]` to import the class. And you need the `groups` key to include `scripts`. If you have multiple listener groups enabled just remember to comma-separate them. Here's everything: ```ini [listeners] groups = scripts scripts.results = from csvpath.managers.integrations.scripts.scripts_results_listener import ScriptsResultsListener ``` ### Adding a script To add a script you do one of: * Add the script to the named-paths groups `definition.json` in a text editor by hand * Call the `PathsManager`'s `store_script_for_paths()` method The second option is the better and easier choice. Using the `PathsManager` method you have less chance for error. If the definitions file doesn't yet exist it is generated for you. However, should you want to add the script by hand, you can. ### First, let's see the hard way * Open or create definitions.json wherever you keep your csvpaths prior to loading them. Your file doesn't need to be called `definitions.json`, but when you load it that is the name CsvPath Framework will use * Create a `_config` key with a dict * In the `_config` dict add a key with the named-paths group name that holds a dict * In the dict add a script type key that holds a script name The script type is one of: * `on_complete_all_script` — executed on every run * `on_complete_valid_script` — executed when all csvpaths in the run are fully valid * `on_complete_invalid_script` — executed when any csvpath in the run is invaid * `on_complete_errors_script` — executed if there were any errors What you get should look something like: ```json { "many": [ "tests/test_resources/named_paths/many.csvpaths" ], "numbers": [ "tests/test_resources/named_paths/zips.csvpaths", "tests/test_resources/named_paths/select.csvpaths" ], "needs split": [ "tests/test_resources/named_paths/zips.csvpaths" ], "_config": { "many": { "template": ":1/:run_dir/:2", "on_complete_all_script": "complete_script.sh" } } } ``` That's not super hard, but it's harder than doing it the easy way. You would then put your script file, in this example `complete_script.sh`, in the named-paths home, the same directory as the definition.json ends up. ### The easy way is better! The easy way to add a script is to call a `PathManager` method: ```python from csvpath import CsvPaths CsvPaths().paths_manager.store_script_for_paths( name="many", script_name="complete_script.sh", text="echo 'hello world'" ) ``` Here the named-paths group name is `many`. If we didn't want to run the script every time using `on_complete_all_script`, the default, we would add a parameter like `script_type="on_complete_errors_script"` or or one of the other script types. The outcome of the method call is the same as the example of doing it by hand — just much easier to set up. ### What happens? When your named-paths group runs and the script type's condition is met, CsvPath Framework copies the script file into the run home directory and runs it. It captures the standard out and error out to a text file that has the same name as the script plus a timestamp. And that's it. The only caveat is that you cannot run scripts after named-paths group runs unless you are using the local filesystem backend — the default. If you are storing your archive in the cloud or on an SFTP server you will need to use another method to trigger actions. [A cloud function](/topics/how-tos/aws-lambda) would be one option. You can [use Zapier, FTTT, or another webhook savvy tool with a named-paths webhook call](/topics/how-tos/webhooks) to trigger actions and workflows in the cloud. # File Arrival Activation Automatically run a named-paths group when a new file arrives You can have a named-paths group run automatically when a new file arrives and is registered as named-file version. This functionality is essentially the same as: ```python csvpaths = CsvPaths() csvpaths.file_manager.add_named_file(name="shipping", path="/my/path/to/my/file.csv") run_ref = csvpaths.collect_paths(filename="shipping", pathsname="my_shipping_group") ``` It is also essentially the same as calling FlightPath Server's `/`[`register_and_run`](https://www.flightpathdata.com/swagger/dist/#/register/register_and_run_csvpath_register_and_run_post) endpoint. Even though the functionality is not hard to replicate using plain Python or a JSON REST API endpoint, it still has value. Activating a named-paths group on file arrival is an automated, no-code operation. It is based on configuration that lives with the named-file. And it acts as a default behaviour that can consistently augment a workflow, even when it is not the main event. Setting up the file arrival activation is equally straightforward. You add it to the named-file's definition.json file. It looks like: ```json { "on_arrival": { "named_paths_group": "shipping", "run_method": "collect_paths" } } ``` It's that simple. Now when a. new CSV, Excel, or JSONL file is registered — in Python, using the API, or in FlightPath Data — our `shipping` named-paths group will run. # Rewind / Replay # Replay Using References Options for when you need to rerun a named-path group A named-paths group is a set of csvpaths that are run as a unit. Named-paths groups are powerful tools for assembling and managing validation and data adjustments. They offer: * The ability to break down validations into bite-sized pieces that are easy to test and reuse * A simple way to run a group of csvpaths * A way to make results available across csvpaths and groups of csvpaths * Options for serial csvpath execution or line-by-line breadth-first execution * The ability to pipe results from one csvpath to be the input to the next * The ability to rewind and replay The last option is super valuable from an operational efficiency point of view. It is important to remember that csvpaths don't just return matched or unmatched lines—captured as their data.csv output—they also have the ability to create new headers, rename and reset headers, and change data values. That potential for change increases the value of being able to pick up from a particular processing point in the named-paths group and (re)run the data forward. What do *rewind* and *replay* actually mean? Let's look at the what and how. ### Rewind vs. Replay The concepts of rewind and replay are really close. It is more important that you know the capabilities than that you use the right word for an activity. This isn't about language, buzzwords, or product comparisons, it's about getting stuff done. CsvPath can replay a single csvpath within a named-paths group. It can do that using the same data from a previous run or new data that hasn't been seen before. CsvPath can also rewind to a point in a named-paths group and play forward to any point in the series of csvpaths in that group. This can be combined with setting the `run-mode` for any given csvpath in the group to `no-run`. And, for even more control, you can also modify the named-paths group itself in JSON, object, or single-file form. You can think of the rewind and replay capabilities as being analogous to a music player. Replay is like setting Spotify to loop on a song infinitely. Rewind is like restarting a playlist from the first song or any song. Be aware that replay is most powerful for named-paths groups where data is piped from csvpath to csvpath. That means using the `source-mode` setting `preceding`. The source mode tells the `CsvPath` instance to pull the input data for its csvpath from the `data.csv` stored with the results of the preceding csvpath. Being able to replay from a particular csvpath in the named-paths group is particularly valuable when the data is large or the number of processing steps, represented as intermediate csvpaths, makes development and/or operations complex. Also keep in mind that rewind and replay are for the serial run methods `collect_paths()`, `fast_forward_paths()`, and `next_paths()`. The by-line breadth-first methods do not explicitly support rewind and replay, though there are undoubtedly corner-cases to explore. The reason is that the breadth-first runs do not generate independent intermediate data sets in the same discrete way that serial runs do. ### References are key To rewind or replay you need to: * Use a `CsvPaths` instance, not a `CsvPath`, instance * Create a named-path group, ideally with an identify for each csvpath in the group * Pass references to one or both of the file manager and paths manager when you do your run A named-file name is a pointer that maps to a physical filesystem path. The pointer is in the form of a string name or a reference. A named-paths name is the same, except that it points to one or more csvpaths. You have seen these before and we're not going to add or change anything here except using a reference rather than a simple name string. ### Named-file Names First named-file names. They come in two flavors: * name-of-named-file * $named-paths-name.results.instance.csvpath-identity The top bullet is just the basic name->path setup that you've seen many times in these pages. The second bullet is a reference. Like all references it starts with $. Next it identifies the named-paths result you want to use as your input. The token `results` is the reference datatype that indicates we're talking about the results of a past run. Next, the instance name is a date-time string. And last, the identity of the csvpath within the named-paths group that generated the `data.csv` we want to use as our input. Recall that the csvpath's identity is set in an external comment using a `name` or `id` metadata field. For example: ```xquery ~ id: my-identity ~ $[*][yes()] ``` That's a lot! The result is that we pipe the resulting `data.csv` of a particular csvpath from a completed named-paths group run as the source data for our next run. To get it all to work, you need that instance name. Its date-time string identifies the run you want to use as a data source. The format of the instance identifier is like this: `%Y-%m-%d_%H-%M-%S.%f` That means a date-time like: `2024-03-21_08-15-21.1` Or March 21st in the year 2024, at 8:15 AM on the 21st second. The trailing `.1` is technically a number of milliseconds, but we only use that in the relatively rare case that two runs complete within the same second. This format would be a pain to remember if not for a couple of tokens: * `:last` * `:first` You can use these tokens in your date string to simplify what you are looking for. The way that works is that you substitute the smaller end of your date-time identifier with one of the tokens. E.g. `2024-0:last` Means that you want to use the last run, from no later than the last second of September, 2024. `2024-03-21:first` Means that you want to use the first run that happened on or after the start of March 21st, 2024. Without these tokens, all you need to do is look at the run times in the archive directory. But using the last and first tokens makes things much easier. The caveat here—and it's an important one—is that you cannot replay a replay using `:last`. The reason is that `:last` looks for the most recent run, but a replay by definition doesn't have all the assets needed for a run within itself. Therefore, if a second replay is attempted using the same instance name, including `:last`, you will find a run that is not self contained and which cannot be the basis for a new run. Imagine, I run the named-paths name ABC. ABC is composed of A, B, and C csvpaths. A works fine, but I need to modify B and replay. Everything works fine. My new run uses A's `data.csv` and creates B and C with their own `data.csv`s. If I want to modify B again and replay again, the last run is my replay and it doesn't contain its own `A/data.csv`. This may sound hard to follow in the abstract. But luckily, looking at the archive directory when you're setting up a replay it is pretty intuitive. ### So Far... So far we have described the named-files pointer. It is the filename argument used in this line to call a `CsvPaths` instance's `collect_paths` method. This one line is where all the rewind and replay magic happens: ```python paths.collect_paths( filename="$sourcemode.results.202:last.source1", pathsname="$sourcemode.csvpaths.source2:from" ) ``` Rewind and replay can and typically do involve not just the `filename` argument but also the `pathsname` argument. ### The Named-paths Name The file manager takes references that let you find data to use in your next run in the results of a specific csvpath from a past run. The paths manager lets use references to identify what csvpaths to run within the named-paths group in your rewind or replay. Bear in mind that you can achieve the same outcome by manipulating the structure of the named-paths group in its JSON, object, or single-file form, or by using `run-mode: no-run` strategically in your individual csvpath mode settings. But both those options have significant quality and development productivity risks that rewind and replay largely avoid. A rewind or replay reference looks like this: ```xquery $my-named-paths-group-name.csvpaths.my-csv-identity:from ``` The CsvPaths language knows `my-named-paths-group-name` is a named-paths name because of the `csvpaths` datatype. Anytime you are referencing a csvpath within a named-paths group you should be using that datatype. The exception to that rule is that this is also legal in some circumstances: ``` my-named-paths-group-name#my-csv-identity ``` In general, though, unless you know you need to use the hashmark version for some reason, for rewind and replay use the `csvpaths` datatype in a reference. Now, if you used just this: ```xquery $my-named-paths-group-name.csvpaths.my-csv-identity ``` You would be replaying just the `my-csv-identity` csvpath. That is useful, for sure. However, you can do more by using two tokens, `:from` and `:to`. These tokens indicate that the CsvPaths instance should rewind to either: * Run from the first csvpath up to and including the csvpath identified by the reference—`:to` * Run the identified csvpath along with any following csvpaths in the named-paths group—`:from` In both cases you are running as if you used `run-mode: no-run` in each of the csvpaths in the group that you want to avoid rerunning during the rewind run. This ability to say *"pickup the named-group starting from this csvpath and using this data"* is not a revolutionary leap from the normal way of running named-groups. But it does offer great tools for development and operational triaging. Definitely worth experimenting with and getting comfortable using. # Doing rewind / replay, part 1 How to start a run from an intermediate point in a past run In [Replay Using References](/topics/how-tos/rewind-replay/replay-using-references) we looked at how to start a run based off an intermediate state of a past run. Let's take a look at an example. This will be very artificial, but it will give you a more concrete understanding that you can bring to more real-world situations. {% hint style="success" %} There is a [CLI version of this how-to here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-2). No Python required. And there is a video of the CLI at the bottom as a bonus. {% endhint %} Implementing the rewind concept is build up from parts: * Named-paths groups * Csvpath identities * Collected data * `source-mode: preceding` * References In brief: we rewind back csvpath steps in a named-paths group of csvpaths. Each of the csvpaths create an intermediate output by selecting lines and, optionally, changing or adding data. Source-mode preceding pipes intermediate form data from one csvpath to the next. And references give us a way to point to the intermediate data and pull it into a csvpath from the named-paths group to create a new run. We'll start with a simple three csvpath named-paths group. Each csvpath is unimaginatively named: `source1`, `source2`, `source3`. The csvpaths pipe their output from one into the next. We run the csvpaths in the group serially. After the first run we find something to improve in the second csvpath. We want to run the group again after our improvements. But ideally we would skip the first csvpath because that one is time consuming. What to do?
For more background on how results are stored please read: [Where Do I Find Results?](/topics/higher-level-topics/data-validation-files-and-storage/where-do-i-find-results) Our named-paths group, called "sourcemode", looks like this: ```xquery ~ name: source1 ~ $[*][ firstline() -> print("this is $.csvpath.identity at line $.csvpath.line_number working on data from $.csvpath.file_name with headers $.csvpath.headers") #firstname #lastname append("count", count(), yes()) collect("firstname", "lastname", "count") ] ---- CSVPATH ---- ~ id: source2 source-mode: preceding ~ $[*][ firstline() -> print("this is $.csvpath.identity working on data from $.csvpath.file_name with headers: $.csvpath.headers") gt(#count, 4) append("working", random(0,5), yes() ) collect("firstname", "lastname", "working", "count") ] ---- CSVPATH ---- ~ id: source3 source-mode: preceding ~ $[*][ firstline() -> print("this is $.csvpath.identity working on data from $.csvpath.file_name") print("$.headers.firstname, $.headers.lastname, $.headers.working") collect("firstname", "lastname", "working") ] ``` You can run this group using this Python: ```python paths = CsvPaths() paths.file_manager.add_named_file(name="sourcemode", path="test.csv") paths.paths_manager.add_named_paths_from_file( name="sourcemode", file_path="source_mode.csvpaths", ) paths.collect_paths(filename="sourcemode", pathsname="sourcemode") ``` As usual, we create a `CsvPaths` instance and register our delimited file with a simple name. Likewise with our named-paths group. In this case we're putting all three csvpaths in one file, so that's all we have to tell our `CsvPaths` instance about. After running this super simple setup, this is what we get.
The structure is:
The key result file for us for this example is data.csv. Every csvpath that is used to collect lines from a delimited file by a CsvPath instance sends its collected data to a data.csv file. (You can choose to not store the data on disk and of course you can also just not collect it). You can see that these csvpaths each did their modifications to the data in a way that impacts the final results coming out of `source3`. * `source1` added a `count` header and limited the lines collected to only those with both firstname and lastname * `source2` appended a `working` header with a random number from 0 to 5, and limited the lines collected to only those where `count` is greater than 4 * And, finally, `source3` removed the `count` header The result is this:
That's all well and good. But let's make a change in `source2` and set ourselves up to use the output of the first run of `source1` as our input. To keep it simple, just change `working` to `thinking`.
Great! Now, because we want to save bytes and watts we're going to rewind to `source2` using `source1`'s data.csv in our new run. Here's the Python: ```python paths.collect_paths( filename="$sourcemode.results.202:last.source1", pathsname="$sourcemode.csvpaths.source2:from", ) results = paths.results_manager.get_named_results("sourcemode") ``` I can hear you muttering *that's it?!* Yeah, that's it. In fact, the last line is a complete add-on, you may or may not want to inspect the results programmatically. Basically, rewind is a one-liner. The two references are pretty straightforward. The datatypes are important: * `results` indicates that we're looking at the data resulting from running our named-paths * `csvpaths` is the datatype that represents the named-paths group we're working with The only other things to pay attention to are the tokens embedded in the references. There are four tokens you can use in references. They start with a colon: * For the date-stamps of runs: * `:last` * `:first` * For the position of csvpaths in their named-paths group: * `:from` * `:to` As you would guess, they do exactly what you'd expect. The `:first` and `:last` tokens replace the right-hand side of run date-stamps to make them easier to remember and manipulate programmatically. The `:from` and `:to` tokens are appended to csvpath identities to indicate that we don't want just the csvpath identified, but also want its predecessors or successors. The result after `source3` is exactly what we were looking for. Which isn't much, in this trivial example, but still.
How do we know that we successfully did a rewind? Well, a couple of things. The biggest tell is that we don't have a results directory for `source1`.
You can also look at the `source2` `meta.json` from the rewind run to see what the settings and inputs were. In a full automated DataOps Collect, Store, Validate pattern this is where the rubber hits the road. Or at least one place.
You can see on line 3 that we're sourcing data from `source1`. On line 16 you can see that our configuration calls for us to grab the `data.csv` output of the last csvpath. Here is the same `source2` `meta.json` information from the first run. Because we did the runs in one script they ended up in adjacent directories: the first in `2024-11-12_08-03-54` and the second in `2024-11-12_08-03-54.0`. Notice that in the metadata shown above and below both times we pull `source2`'s data from `2024-11-12_08-03-54`, not from the second run `2024-11-12_08-03-54.0`.
You can see that we are pulling data from the named-file `sourcemode`, not a physical file path. And you can see that we captured a `source-mode-source` metadata key to identify how we swapped in `source1`'s `data.csv` instead of using the `sourcemode` file. And that's about it. Rewind and replay are not hard. In fact, pretty darned easy, right? # Doing rewind / replay, part 2 Can we achieve the same goals using only the CLI? Yes! While the Python we used to drive [Rewind / replay, part 1](/topics/how-tos/rewind-replay/doing-rewind-replay-part-1) was not complicated, using the CLI would be even quicker and in a triage situation might be a better option. So let's try that. We'll use the same data and csvpaths for this second example. Only our use of the CLI is different. Because it is the same activity we'll do it quickly and let you refer back to part 1 for the background information. Here are our files. Two sets of three csvpaths and one data file. {% file src="/files/HtzRisx5RsnAZgxb8iuw" %} {% file src="/files/M82QreVQYVHWgMEjzeta" %} {% file src="/files/Rv5bnxhFOIQEQDyiz3IF" %} You stage the data and load the csvpaths like this:
Next, run the original from `sourcemode.csvpaths`. We're using the version where the `source2` csvpath has the word `working`. Here are the steps:
The result is a bit messy, but if you look closely those printouts tell us the csvpaths worked. Anyway, the `print()` statements in the csvpaths can be removed when you want to show off this trick to your friends. Running a rewind/replay in the CLI is easy. But remember to reload your csvpaths when you make changes. We have a second csvpaths file that has our changes, so load it now. Use the same named-paths name as you did the first time. Now we're ready to rewind and replay using the same data generated by the `source1` csvpath with the new csvpaths. The new csvpaths file is only different in the `source2` and `source3` csvpaths, so we're going to start with `source2`:
And there again we did the substitution of `thinking` for `working`. You can see that rewind/replay based on immutable data is straightforward — using the CLI or Python. If you need to redo any part of your process and you don't want to start at the very beginning, you have an easy way to do that. And because the data is immutable and the processing is idempotent, there is little risk in iterating on a solution. Using the CLI, if you prefer that approach, is just icing on the cake. Here's a quick video to show the steps we took. Try it yourself. {% embed url="" %} # Referring to named-file versions It's common to need to rerun an older file version again -- and easy to do. CsvPath stores file versions as named-files. Each name identifies a set of physical files. The set of physical files may have had the same name or different names. One by one they all become the most current named-file. But what about when you want to re-run last week's version or last month's or year's? Happens all the time when we need to compare data or update our CsvPath Language files and rerun. Luckily it is easy to run old versions. The way you do it is with references. ## Types of references File references are not the same as named-paths references or references to results data files. Those are three different capabilities that help you do similar things: re-run CsvPath Language files against data files.
A comparison of how different types of references help you run your data preboarding process on new or older delimited data.

There are lots of options for rerunning or reusing preboarding processes and data

You can read about using results and named-paths references in doing rewind/replays [here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-1), [here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-2) and [here](/topics/how-tos/rewind-replay/replay-using-references). This page is about named-file references. A.k.a. *how do I go back in time to run earlier data?* ## The file reference datatype References have types. You can [read about there here](/topics/practical-stuff/the_reference_data_types). Named-file references look like: ```bash $new-arrivals.files.yesterday:last ``` The datatype for this reference is `files`, indicating that it is a reference into the named-files collection. It points to a named-file named `new-arrivals`. The version of `new-arrivals` it refers to is the last version that was registered yesterday. If no `new-arrivals` file was registered yesterday, this reference will result in an error message saying the file cannot be found. ## Identifing versions In the reference above, `:last` is a token or pointer that modifies the word `yesterday`. There are several of these pointers that help you more easily identify versions of named-files. If you have ever looked at the physical files associated with a named-file name you've seen that they are named using sha256 hashes. The hash comes from the bytes of the file and changes each time a new file is registered with different bytes. One of these file names might look like: ```url inputs/named_files/food/food.csv/74d3787c36bdb2098a3479126d28970618696237d63f9006717d61e86af5a988.csv ``` Obviously a hash filename is a pain to work with so we have several approaches to identifying versions of named-files. And some of the ways have optional pointers like `:last`. The ways are: * Index * Fingerprint * Day * Date ## Index Using an index is pretty much what it sounds like: a zero-based integer that is the count of versions of the named-file. You can see the versions in the manifest file. In the food named-file example above the manifest file is at: ```bash inputs/named_files/food/manifest.json ``` Opening the manifest.json we might see:
The contents of the named-files manifest.json. It shows that four CSV files were registered under the name "food".
You can see that four files have been registered under the name `food`. The first and last were named `food.csv` and the second and third physical files registered as the `food` bytes were actually `people.csv` and `people2.csv`. To refer to the second file by zero-based index we would simply use: ``` $food.files.1 ``` ## Fingerprint A fingerprint is a sha256 hash. It is a representation of the exact bytes in a file. If even one byte changes, the new fingerprint would be complete different and unique. Any two files with exactly the same bytes, regardless of the filename, will have the same fingerprint. To make a reference by fingerprint to the third `food` file in the `food` named-file's `manifest.json` screenshot above we would have to find the fingerprint in the manifest and add it to our reference like this: ```bash $food.files.be9fadda358d434e29c4cbf794bbbe1d505bf17d68598c4131f10c4ece176c67 ``` This is the most exact way to refer to a file, but it isn't the most convienent. ## Day We can create a reference to a file by day using today or yesterday. These do exactly what they sound like. Today picks out the file version from the current day. Yesterday picks out the file version from the preceding day. But wait, what if there are multiple versions from today or more than one from yesterday? You can solve that problem, in some cases, using `:first` and `:last`. These pointers say that you are looking for either the first version registered in the specified day or the last version registered. If you don't provide a pointer the default is `:last`. ```bash $food.files.yesterday:last ``` This could be a helpful reference when you pickup the previous day's work in the morning and want to start working on the bytes you had then. On the other hand, to pick out the 1st version of `food` in the `manifest.json` above you would use the following (assuming it's still the 26th of February 2025): ```bash $food.files.today:first ``` Instead of `:first` and `:last` you can also use the index of a registration. That works the same way we described an index reference above, except that the index is just within the subset of registrations from `today` or `yesterday`. If I have a named-file with 10 registrations the last five of which happened yesterday, I can use a reference like this to pick out the second bytes registered yesterday, a.k.a. the registration at the seventh index of all registrations on all days: ```bash $food.files.yesterday:2 ``` ## Date The final way to refer to a named-file version is by datestamp. A date reference might look like: ```bash $food.files.2025-02-2:after ``` This reference says that we want the food bytes from the first file registered as food after February 20, 2025, midnight. Similarly: ```bash $food.files.2025-02-26_18-50:after ``` This reference would pickout the first registration in `food`'s `manifest.json`, above. The full timestamp pattern you use in your references in strftime notation is: ```python %Y-%m-%d_%H-%M-%S ``` You can provide as much specificity as you like by adding more to the format string. I.e. `2025-01-21_01` is less specific than `2025-01-21_01-30-45`. However, neither goes to the subsecond, so in principle there could be a conflict where you specify the datatime to the second, but there are two registrations that happened in the same second. In practice, that is hugely unlikely, and if you think it could happen you should just use the fingerprint. Since you'd need to dig the to-the-second datetime out of the `manifest.json` anyway, using the fingerprint would be no more effort. As well as `:after` and `:before` you can pass a datetime string without a pointer. A reference like that is an exact match reference. In general, the benefit of using a datetime string without a pointer is clarity. A fingerprint or an index would be equally effective, but less easy to interpret without checking the `manifest.json`. # SFTP SFTP is a bedrock backend provider that also enables seamless operations CsvPath Framework uses SFTP in three ways: * First and foremost, as a backend provider * As a source for file registrations into named-files * As a target for transfers of files from run results #### A Backend Provider [We cover storage backends elsewhere](/topics/how-tos/storage-backends). Basically, you can setup one SFTP storage backend per project. Using that backend configuration, you can locate your named-file staging, named-paths groups validation, and/or results archive in that SFTP server. You can also register data files from any location in that server that your credentials can access. Similarly, you can transfer result files from the archive to any location in that server your credentials have access to. #### A Source Of Files To Register Files can be registered into a named-file from any number of SFTP servers, as long as you configure those servers in your named-file's definition.json. When you attempt to register a file at, say, `sftp://my-sftp-server.acme.com:2022/the/file.jsonl`, but you don't have `my-sftp-server.acme.com` on port `2022` as a backend, your `FileManager` will look in the definition of the named-file you want to register `/the/file.jsonl` into to see if there is a match on `my-sftp-server.acme.com` and `2022`. If there is, the registration happens. #### A Target For Result Files Transfers In a similar way to how named-files can have SFTP server configurations, named-paths also can have them. In the case of a named-paths, [SFTP use is during transfers](/topics/how-tos/transfers). As you probably know, any result file can be transferred to a destination in any of the project's backends. SFTP is a special case. With SFTP you can setup additional server-port configs in addition to or alongside the backends. There are two methods of doing post-run results transfers: transfers defined on the csvpath statement (`transfer-mode:`) and transfers defined in the named-paths group `definition.json`. Both can use the additional SFTP servers, if any are configured. # SFTP Export Forward your results from CsvPath's archive to an SFTP account
{% hint style="danger" %} This page is about sending files to an SFTP after a run completes. It is not about using SFTP as a storage backend for staging and runs to operate on. You can both use the SFTP backend and export files to an SFTP server at the same time. {% endhint %} Sending your named-results files by SFTP easy. This feature is similar to the `transfer-mode` feature. The difference is that `transfer-mode` only copies files on the local disk. To start sending results by SFTP you simply: * Make a trivial change to your `config/config.ini` * Add metadata to the csvpath's external comments The process is the same as for the other integrations. You will, of course, also need to have an SFTP account. {% hint style="success" %} Sending results by SFTP as this page describes is not the same as using the SFTP backend. You have the option to [configure your archive, named-files, or named-paths storage areas to be in an SFTP server](/topics/higher-level-topics/data-validation-files-and-storage/storage-backends). Doing that applies to all work in your CsvPath Framework project; whereas, sending results files the way this page describes is something you set up for an individual csvpath. {% endhint %} Here is the `config.ini` change:
If your project is new and you let `CsvPaths` generate it for you you just need to uncomment the `sftp.results` key and add `sftp` to the `groups` key. If you don't have the `sftp.results` key already, just paste in: ```ini sftp.results = from csvpath.managers.integrations.sftp.sftp_sender import SftpSender ``` If you want a `CsvPaths` instance to generate a new config, move the old one to a temp file and just do either of the following. Bear in mind that this feature is at CsvPath point release `0.0.505` or greater. ``` poetry run cli ``` Or if you're not a fan of Poetry, make a trivial Python file that has these two lines and run it: ```python from csvpath.cli import Cli Cli().loop() ``` Next the metadata directives: * sftp-server * sftp-port * sftp-user * sftp-password * sftp-target-path * sftp-files * sftp-original-data You can use these in external comments. External comments are ones that are above or below your csvpath, but not within the csvpath. Comments are delimited with the `~`. Obviously some of these values are required. Server, port, user, password, and sftp-files are mandatory.

Notice the ALL CAPS env references and var|variable dynamic substitutions.

| Setting | Description | Example | | -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------- | | `sftp-server` | The network name or IP of the SFTP server. | `sftp-server: localhost` | | `sftp-port` | The port. | `sftp-port: 22` | | `sftp-user` | This is the username of the regular SFTP account, not an admin account. | `sftp-user: frog` | | `sftp-password` | The account password. If the value is ALL CAPS it is swapped for the value of any env var that matches. | `sftp-password: SFTP_USER_PASSD` | | `sftp-target-path` | The directory within the account where the files will land | | | `sftp-files` |

A set of file names in the form X > Y, Z > A, B > C.

This pattern means that X will be copied to a file named Y, Z to a file named A, and so forth.

| `sftp-files: data.csv > results.csv, errors.json > errors.json` | | `sftp-original-data` | If `yes`, the original data file is sent using the same original filename. The original data file is taken from the first csvpath in the named-paths group. That way if we are in `source-mode: preceding`, or doing one of the by\_lines methods, we get the correct data. | `sftp-original-data: yes` | For all these settings you have two options for non-static values: * `ALL CAPS` are swapped for env vars, if there is a match * Any value that is in the form `var|variablename` is swapped for any matching run variable named `variablename`. If none matches `variablename` becomes the value. # SFTPPlus Create an effective automated MFT solution in minutes
CsvPath exists to close the gap between managed file transfer (MFT) and the data lake. SFTPPlus is a managed service that provides a secure route into the organization that is manageable at scale. Combining SFTPPlus and CsvPath enables you to land your data securely, automatically register its identity, check its validity, and canonicalize it, and stage it for efficient downstream use. Likewise, channeling the outbound flow through CsvPath into SFTPPlus and out to external data partners is a robust solution for effective data partnerships. Sounds great. How do we set it up? In fact, it's pretty simple. We'll work on the assumption you have an SFTPPlus server installed. (You can get a [trial here](https://www.sftpplus.com/#trial)). We'll also assume you or your SFTPPlus admin are comfortable enough with Python to follow the simple steps that setup a CsvPath project. We'll use [Poetry](https://python-poetry.org/) for that. ## Requirements The goal is to allow a CsvPath writer to create a landing zone for files from an external data partner. The requirements are: * The files are sent via SFTP to an account owned by the data partner who is sending them * On landing, the files are staged in CsvPath for processing * A named-paths group of csvpaths is applied to each file that arrives * In this processing, the arriving files are * Given a durable identity * Checked for validity * Canonicalized as needed * Staged for ETL in the Archive, possibly with copies transferred via S3, SFTP, locally, etc. * Do all this in an automated way mainly under the control of the csvpath writer with the minimum of setup help That's what we're going for.

Automated data onboarding made easy!

## The Steps Our steps are, for the most part, the same as for any CsvPath integration. However, in this case we are going to run two CsvPath projects. One is for the CsvPath user. The other is for the server automation. On the csvpath writer's side the actions are: * Add SFTPPath information to the user's config/config.ini file * Add SFTPPath directives to the named-paths And for the SFTPPlus admin, they will: * Set up the data partner's account * Create a mailbox transfer to receive instructions from CsvPath * Add information to the server-side CsvPath project's `config/config.ini` file * Add trivial scripts ([downloadable from CsvPath's Github](https://github.com/csvpath/csvpath/tree/main/assets/integrations/sftpplus)) to connect the wires In concept, this is all pretty straightforward. There are a lot of details, of course. Getting it working will likely take a morning and a couple cups of coffee. [Here's a high-level checklist of the steps](/topics/how-tos/sftp/sftpplus/sftpplus-implementation-checklist). ## Who Knows What? There are several pieces of information that need to be shared. For example, the csvpath writer needs to know the type(s) of files they will be receiving from the data partner. And the data partner needs to know the server and account details from the SFTPPlus admin. To be clear, here are the communication flows:

Good communication is the bedrock for long-term data partnerships

To be sure, this information has to be exchanged by these same people regardless of if CsvPath and SFTPPlus are used. We're just putting it into specific places to facilitate an automated process. ## Who does what? Setting up the SFTP integration is straightforward. [Again, here is the high-level implementation checklist](/topics/how-tos/sftp/sftpplus/sftpplus-implementation-checklist). You, as the DataOps driver, just add a few directives to your csvpath files. The DevOps team sets up the server-side of the integration. And the data partner starts dropping files in an FTP directory. Here's an image of who owns what. **Green** for the data person. **Blue** for the DevOps ownership. And **red** for the data partner's contribution. And, just to be clear, these are setup steps. Once the SFTPPlus solution is in place any number of DataOps team members can work with any number of data partners without needing anything beyond account names and logins.

Green for the person using CsvPath. Blue for IT setup. Red for the data partner sending data.

## Setting Up the DataOps Side Here you are setting up the client-side. This part is easy. You simply: * Receive mailbox connection information * Start adding SFTPPlus directives to your csvpaths Let's assume you like Poetry as much as we do. Open a console and create a new Poetry project called sftpplus. ```bash poetry new sftpplus ``` Change into your new project and add CsvPath: ```bash poetry add csvpath ``` Next fire up the CsvPath CLI just for a moment to allow CsvPath to create your config file. ```bash poetry run cli ```
When the CLI comes up just select `quit` and hit enter. CsvPath generated your `config.ini` so we're done with it for now. Now, open `config/config.ini`. We're going to add some information about our SFTPPlus server. Look for `[sftpplus]`. Keep in mind that if you didn't just create this project you may need to update your csvpath version.
Edit these four fields to have appropriate values, or if you don't see them in your `config.ini`, add them. The username and password in the screenshot are in ALL CAPS. That tells CsvPath's SFTPPlus integration that it should look in your environment variables for the values of those names. You don't have to use env vars but that is a better approach than having passwords in config files. You shouldn't need to add the SFTPPlus integration's listener, because CsvPath includes it in the generated `config.ini`. But you do have to indicate that you want to use it. Look for `[listeners] groups` and add sftpplus like this:
If you are using multiple integrations just separate the names with commas. You're done in `config.ini`. Painless! Next, pick a csvpath from your named-paths group. It can be any of them. You're going to be running the whole group against every arriving named-file, but only the csvpaths you decorate with the SFTPPlus instructions are automated in SFTPPlus. The directives you can add are: * `sftpplus-active` * `sftpplus-named-file-name` * `sftpplus-run-method` * `sftpplus-account-name` * `sftpplus-execute-timeout` `sftpplus-active` and `sftp-execute-timeout` are optional. The other three are mandatory, just as you would think.
DirectiveValuesDescription
sftpplus-activeyes or no or delete to delete the SFTPPlus transfer automation

Each time you add your named-paths group your transfer in SFTPPlus will be updated to be active or inactive. Active is the default.

If you make the value delete the SFTPPlus transfer will be deleted so no new files will be automatically processed. Nothing else will be deleted; only the SFTPPlus config changes.

sftpplus-named-file-nameAny named-file name you likeThis is the name your inbound files will be referenced by. The physical file names themselves will likely change, but your automation will always run the current named-file against the current named-paths.
sftpplus-run-method

One of:

  • collect_paths
  • collect_by_line
  • fast_forward_paths
  • fast_forward_by_line
These are the main methods to run a named-paths group against a named-file. (the next_paths and next_by_line methods are not an option because those are not intended for this kind of automation).
sftpplus-account-nameAny SFTP account nameThis is the account the data partner, or their automation, will log into to send their files.
sftpplus-execute-timeoutAn integer number of seconds. The default is 500This is the timeout for running two methods: add_named_file and the method named in sftplus-run-method. If you are storing all files on the local server your run times will be very quick — depending on your file sizes, of course. But if you are configured to store files in S3, a remote filesystem or SFTP your timeout should be considerably longer.
Your csvpath should look something like this:

These four lines automate file arrivals from a data partner

## Setting up the DevOps infrastructure On the DevOps side of things — the server-side — there are three main activities, assuming your SFTPPlus server is already installed: * Create a Python CsvPath project on the server * Create the mailbox account * Create the data partner's account ### Setting up the server-side CsvPath project Creating the CsvPath project is quite easy. It requires a Python 3.10.5 or greater environment. First we add Pipx and Poetry. Pipx keeps Python applications from getting in each other's way. Poetry is our Python project tool. For the official SFTPPlus Ubuntu docker container, the commands are as follows. Otherwise, if you are installing on Windows you could use [Scoop](https://scoop.sh/) or on MacOS [Homebrew](https://brew.sh/). * `apt-get --no-install-recommends install -y pipx` * `pipx ensurepath` * `pipx install poetry` Next you may need a higher Python version. (Check the version by doing `python3 --version`). If you do, follow these steps. (Windows and Mac users will obviously have slightly different steps). * `apt install software-properties-common` * `add-apt-repository ppa:deadsnakes/ppa` * `apt update` * `apt install python3.12` Creating the CsvPath integration project is simple: * cd to the directory wherever you plan to put your scripts. E.g. `/opt/sftpplus/run` or `C:\sftp\run` * `poetry new transfers` *(or whatever project name you prefer)* If you need to raise your Python level, make that change as you create your project: * `cd` to project's desired parent directory. e.g. /opt/sftpplus/run * `poetry add csvpath` * You may need to edit `pyproject.toml` to get the correct Python version for CsvPath and its dependencies. If Poetry complains, change the Python requirement to:`">=3.10,<4.0"` or follow Poetry's suggestion. * Download the following 4 simple scripts and drop them in the root directory of your new project The 4 scripts [live in CsvPath's Github here](https://github.com/csvpath/csvpath/tree/main/assets/integrations/sftpplus). They are: * [handle\_auto\_arrival.py](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_auto_arrival.py) * [handle\_auto\_arrival.sh](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_auto_arrival.sh) or [handle\_auto\_arrival.bat](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_auto_arrival.bat) * [handle\_mailbox\_arrival.py](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_mailbox_arrival.py) * [handle\_mailbox\_arrival.sh](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_mailbox_arrival.sh) or [handle\_mailbox\_arrival.bat](https://github.com/csvpath/csvpath/blob/main/assets/integrations/sftpplus/handle_mailbox_arrival.bat) *(The `.bat` files are for Windows users. They are in the same location in GitHub).* These scripts are just trivial shims that allow SFTPPlus to call CsvPath. On Linux, `chmod` the shell scripts to make them executable. `chmod +x handle_auto_arrival.sh` and `chmod +x handle_mailbox_arrival.sh`. Update the paths in the scripts as needed. Check that the shell scripts run Poetry correctly by running them yourself. They will blow up because you won't be feeding them the right data, but you'll see if they run Poetry. If not, change the path to Poetry used in the script. On Linux, do `which poetry` to see where poetry lives and update the .sh scripts with the right path. E.g. on my laptop, the scripts need to use `/Users/sftpplus/.local/bin/poetry`, rather than just `poetry`. So my `handle_mailbox_arrival.sh` looks like: `/Users/sftpplus/.local/bin/poetry install && /Users/sftpplus/.local/bin/poetry run python handle_mailbox_arrival.py "$1"` ### Setting up the mailbox and data partner accounts The mailbox account is where the SFTPPlus integration sends instructions for creating automations. This activity is completely behind the scenes. Open the SFTPPlus server admin console in your web browser. Create an account called `mailbox`. This account will be shared with all the csvpath writers. Then, if it doesn't already exist, create an account for your data partner. Make a storage folder for inbound content. In your storage area create the following directories: #### Mailbox * ./mailbox * ./mailbox/handled #### Data partner * ./<\> You don't have to create any other directories, but you should know that the integration will create a directory for each named-file name and two more directories within. * ./<\>/<\> * ./<\>/<\>/handled * ./<\>/<\>/meta Assign the root of these directories to the `mailbox` and *\* accounts as their respective storage areas. You do that in the SFTPPlus admin UI accounts page. The final SFTPPlus step is to create a transfer for the `mailbox` account. CsvPath Framework users with the SFTPPath integration configured are connected to SFTPPlus. Behind the scenes the integration sends instructions to SFTPPlus when users load CsvPath Language files. The instructions CsvPath sends go into the `mailbox` account. The `mailbox` transfer runs the `handle_mailbox_arrival.sh` (or `handle_mailbox_arrival.bat)` script, and on success, will move the incoming instructions to its `handled` directory. Those instructions tell SFTPPlus how to handle incoming data partner files. To set this up, create a transfer called `csvpath` (or whatever you like). Set it to check the `mailbox` frequently. It should move files from `./mailbox` to `./mailbox/handled`. Before it moves files make it run `handle_mailbox_arrival.sh`. Be sure to set `Overwriting rule` to `Overwrite existing file`. At this point you should be able to sftp into the mailbox and data partner accounts.
## And you're done Well, done but for testing, of course. The two manual tests you need to see working are: * Add one or more csvpaths to a named-paths group using the `PathsManager.add_named_paths method` and see a transfer created for the data partner and a metadata file show up in the partner's `meta` directory. * Drop a file in the partner's account and see it processed into the `handled` directory and its results show up in the CsvPath archive. You can use the CLI for the first test to make it quick and code-free. You can read an [example of how to do it here](/getting-started/more-csv-and-excel-validation/your-first-validation-the-lazy-way). Assuming your accounts are on the local server, you should see something like the below. In this case `tinpenny` is the data partner, `orders` is the named-file-name, and `sftpplus` is the named-paths group name:

After adding a named-paths group you should see something like this

## Questions? * **Can multiple csvpath writers use the same inbound named-file for their separate named-paths group runs?** Yes, multiple DataOps people can use the same named-file with the SFTPPlus integration. You will see a separate JSON metadata file for each named-paths group that needs to run against an arriving file. The only constraint is that named-path group names must be unique within the namespace. (I.e. the combination of account + named-file name + named-paths name must be unique). * **Are there other settings for the SFTPPlus server accounts and transfers?** Yes, possibly. You can configure your accounts and transfers pretty much any ways that make common sense. You may change the configuration of the automatically created transfers that CsvPath creates, within reason. * **What information needs to be exchanged between the people setting this integration up?** The csvpath writer needs to know how to fill in the directives they add to the comment section of their csvpaths. So, they need server name, mailbox credentials, etc. The DevOps person needs to know the data partner's information so they can create that account. * **As a CsvPath writer, how do I know everything is working?** Great question. There are several ways to make sure things are going well. * [Add a Slack notification](/topics/how-tos/slack) to your csvpath so that you can see the processing happen when files arrive * Use [Marquez or another OpenLineage server](/getting-started/dataops-integrations/openlineage) to track file arrival events * Use an observability tool like [Grafana, New Relic, or another OpenTelemetry system](/getting-started/dataops-integrations/opentelemetry) to see the arrivals * If you have access to the SFTPPlus event log you will see the transfer creation and all its activity there * SFTPPlus allows you to set up email alerts that can help you understand when server actions happen. * Using an SFTP client you can easily watch the metadata file land in the mailbox and see the data partner's named-file directory, with its meta and handled subdirectories. Watching that progression and then sending a file and watching it be processed can take just a few seconds. * The CsvPath logs on both client and server will give you a good understanding of the step-by-step at each end. * As you are writing csvpaths and using the CLI don't forget to set CsvPath to raise exceptions and log on the `DEBUG` level during development. In the CLI there is an option to set those two configuration values, or just do it in `config/config.ini`.

You can configure debugging from the CLI's main menu

The first three bullets are available to CsvPath writers on a csvpath-by-csvpath basis, without DataOps support once the configuration values are available. In general, though, the initial setup of an automated transfer is something that the CsvPath writer, their DataOps support, and the data partner will have to work together on to make sure the automation is buttoned up. Once you have the automation in place you should consider using an observability tool to track when files arrive, if you are seeing the correct amount of data, and if the files process correctly. And if your file arrivals are routine, an observability tool will give you a way to get an alert if the expected processing didn't happen within a time period. * **As a CsvPath writer, what if I need to make changes to the setup?** That's no problem. You can change your SFTPPlus metadata fields and reload your named-paths at any time. The integration will update the transfer. * **What if my data partner sends a file with an unexpected name?** From the CsvPath Framework's point of view, each file that arrives is named by the directory your data partner puts it in. If you have the named-file name `orders` the integration will create an `orders` directory. Your data partner will drop their files in that directory. Within CsvPath, each file will be tracked as part of a well-identified sequence of versions of data arriving with the same physical filename, all within the scope of the single named file name. For you as a CsvPath writer, nothing changes because you're working against the CsvPath Framework's named-file name, which doesn't change. So, to be clear, if you have `orders` as your named-file name an `orders` directory is created. Your data partner may drop `Jan-orders.xlsx`, `Feb-orders.xlsx`, `Mar-orders.xlsx` and each of these will take its turn as the the named-file `orders`. If your partner drops three versions of `Feb-orders.xlsx`, CsvPath Framework will collect, fingerprint, and make all three available in sequence as `orders`. Likewise, if your data partner sends `spring-picnic-menu.csv` to the `orders` directory, CsvPath will track that file in its own sequence of data within the scope of `orders` — even though it seems like that data probably doesn't belong. You can see the progression of each named-file in its `manifest.json`. Look in (by default) `inputs/named_files`. * **When do the handled files get deleted?** That's up to you. Once a file lands in `./mailbox/handled` or `./<>/handled` it can be deleted. The instructions JSON files are copied to the `./<>/meta` directory and the inbound data files are copied into CsvPath's file storage area as part of file registration. (CsvPath's named-files area is configured in the server-side CsvPath project's `config/config.ini`). The original data files are no longer needed. Leaving the inbound files for a relatively brief period of days just in case the World blows up would be prudent. But immediately on processing the inbound files, the source of truth becomes CsvPath's data storage area. * **Do we need a mailbox for every different data partner?** No, your SFTPPlus just needs one `mailbox` account for CsvPath to use no matter how many data partners or inbound files you have. * **The configuration feels like a lot, can I get help?** Conceptually it's all pretty straightforward. But for sure there are lots of details. File and process permissions, in particular, can be a pain in the neck. We're happy to give help and advice — [use the contact form here](/getting-started/a-helping-hand). And if there's a bigger need that goes beyond the SFTPPath integration setup we can point you to partners. # SFTPPlus Implementation Checklist A checklist for implementing SFTPPlus + CsvPath *For this checklist we are assuming the client-side and server-side are on the same machine. In a production situation the client-side would be wherever the CsvPath writers do their work and the server-side would be on a different, centrally-managed machine(s).* ## Steps To Implement SFTPPlus + CsvPath * [ ] **Platforms and applications** * [ ] Install SFTPPlus * [ ] Install Python * [ ] Install Pipx and Poetry * [ ] **SFTPPlus config** * [ ] Create the mailbox account * [ ] Create the `mailbox` user and their storage directory * [ ] In the `mailbox` storage directory create a `handled` dir * [ ] Create the data partner's account and their storage directory * [ ] **Create a server-side CsvPath workspace** * [ ] Pick a location and do: `poetry new ` * [ ] Add CsvPath to the project: `poetry add csvpath` * [ ] Start and quit the CLI to generate the `config/config.ini` file: `poetry run cli` * [ ] Add SFTPPlus integration fields to `config/config.ini` * [ ] Set the `[inputs] named_paths` location to a location that is accessible to the client side (On the local filesystem, S3, or a file share) * [ ] Add the SFTPPlus integration's four scripts from Github to the project's root directory * [ ] Check to make sure the two `.sh` or `.bat` scripts call your Poetry install correctly * [ ] **Create a client-side CsvPath workspace** * [ ] Pick a location and do: `poetry new ` * [ ] Add CsvPath to the project: `poetry add csvpath` * [ ] Start and quit the CLI to generate the config file: `poetry run cli` * [ ] Add SFTPPlus integration fields to `config/config.ini` * [ ] Set the `[inputs] named_paths` property to the location that the server-side is also configured to use * [ ] **Load your first named-paths** * [ ] Add SFTPPlus directives to the csvpath's metadata comments * [ ] Run the CLI and load the csvpath into CsvPath Framework * [ ] See that the named-paths file shows up in the location configured in `[inputs] named_paths` * [ ] Watch as a metadata file lands in the `mailbox` account, creates the named-files transfer, and is moved to the mailbox's `handled` directory * [ ] **Drop your first data file** * [ ] SFTP your file to the data partner's account, putting it in the named-file name directory * [ ] See that the file is moved to the `handled` directory * [ ] Check that the file shows up in the directory or bucket configured in `[inputs] named_files` * [ ] Check that the archive is created at the location configured in `[results] archive` * [ ] See that the new file is processed, moved into `handled`, and the named-paths run's results show up in the archive ## What you have achieved You're done. Congratulations! What you have achieved is: * Your csvpath writers can **easily configure more automated transfers** with minimal assistance * The server operator **never needs to worry about new data drops**. They just create user accounts as needed and otherwise manage the SFTPPlus server. * Your csvpath writers can **turn transfers on and off and make other changes without help** * The data partner can **drop their files with confidence** * You can improve the robustness of your DataOps with **strong identification, validation, and canonicalization capabilities**. The data you stage now has **traceability and durability**. And you now have the ability to **easily rewind and replay data processing steps.** * You can **easily add pre-integrated observability and alerting** tools so you can keep track of data flows and react quickly to any anomalies # Registering Files From SFTP Registering files from an SFTP server into a named-file Like with HTTP(S), you can pull files to register from an SFTP server that is not a regular project storage backend. That gives you four sources of files to register into named-files: * The local filesystem (whether configured as one of the project's storage backends or not) * Via [HTTP(S)](/topics/how-tos/storage-backends/https) from publically available URLs * From any location in a [project backend provider](/topics/how-tos/storage-backends) that is accessible to the account used for the backend * From one of a set of arbitrary SFTP servers configured on a particular named-file This page is about the last bullet. #### Adding an SFTP To a Named-file Like named-paths groups, named-files have definition.json files that hold configuration information, such as default templates and SFTP servers. When you add an SFTP server any registration that involves SFTP is first matched against the backend SFTP config in the form at Config > Integrations > SFTP in FlightPath Data or `[sftp]` in the project's `config.ini` file. If there is no match, the FileManager then looks for what are called `ServerConfig`s in the `description.json`. A server config in a `definition.json` file is pretty straightforward (and simpler than for a named-path). It looks like this: ```json { "sources": { "myserver": { "address": "192.168.1.182", "port": 2022, "username": "david", "password": "myPassw0rd" } } } ``` Even so, the easier way to create an SFTP definition is in FlightPath Data. To do it that way, first stage a named-file by right clicking on a file or folder in the project tree and using the Stage Data Dialog. Then right-click on the named-file and select `Set SFTP sources`. A dialog opens that lets you create a list of named SFTP servers. Enter your server's information, click the test button to confirm your config works, and click `Set`. Your `definition.json` will then include a block like the JSON shown above.
Based on the configuration above, when I attempt to register: ``` sftp://192.168.1.182:2022/myfiles/geotags.csv ``` CsvPath Framework will find the server in the config by matching on the address and port, grab the credentials, and do the registration into the named-file. And as a reminder, if you don't want to put your credentials in your definition.json files (good call!) you can add them as UPPERCASE project or OS environment variables and and use those uppercase names in the form. CsvPath Framework will see the uppercase-ness and look in the env variable location configured for the project for a variable with the same name. # Creating a Custom Listener CsvPath Framework listeners are easy to create and powerful CsvPath Framework is very much event oriented in its core functions. It relies on listeners to track file registrations, collect errors, capture run results, and more. The listener groups it supports send data to SQL databases, webhooks, scripts, JSON files, and more. You can add a new listener, written by yourself, in just minutes. Here's how. Listeners are managed in groups. Look in your `config/config.ini` file for the \[listeners] section. There is a `groups` key that takes a comma separated list. Each name in the list is a member of a set of listeners that interface in the same way for a common purpose. The members of the set each handle one type of event: * File registrations * Named-paths group loads * Run start by csvpath * Run starts * Run results * Result serialization * Errors Most of these are self-evident. Run starts are for the group as a whole; whereas, the run starts by csvpath statement are specific to members of a named-paths group that is running. The run results events are fired at the end of runs. The result serialization events are fired when an individual csvpath completes. To create your own listener, you just: * Create a subclass of `csvpath.managers.listener` `Listener` * Implement the event handling method `def metadata_update(self, mdata: Metadata) -> None` * Put your module file in a folder under the `/config` directory * Add your group name to `[listeners] groups` * Add your listener's import value to a key in `[listeners]` that starts with the group name and a period and ends in one of `file`, `path`, `result`, `results` At a super high level, that's all there is to it. A trivial listener that listens for file registrations looks like: ```python from csvpath.managers.listener import Listener from csvpath.managers.metadata import Metadata from csvpath.managers.files.file_metadata import FileMetadata class DemoListener(Listener): def __init__(self, config=None, csvpaths=None): super().__init__(config=config) self._csvpaths = csvpaths def metadata_update(self, mdata: Metadata) -> None: if isinstance(mdata, FileMetadata): print(f"DemoListener: mdata: {mdata}") ``` And the `config.ini` setup looks like: ```ini [listeners] groups = default,activation, demo demo.file = from rc4_int.demo_listener import DemoListener ``` Where my project is called `rc4`, my folder for python files is `rc4/config/rc4_int`, and my listener group is named `demo`. What could be simpler? # CKAN How to use CsvPath to publish data products to CKAN with confidence
## What is CKAN? [CKAN](https://ckan.org/) is the leading data portal. It powers massive data repositories, including the US, EU, and UK government's open data publishing, along with those of numerous other countries, provinces, cities, research centers, and NGOs. CKAN is used by private companies to host data assets internally for use across departments and divisions. Corporate data portal implementations include ones at LEGO, National Grid, Suncorp Bank, and many others. CKAN is a **data portal**. A data portal is a purpose-built catalog for data products in the form of downloadable data and metadata references to online or on-request sources. The goal of a data portal is to offer high-value, validated, versioned datasets with sufficient metadata to fully characterize their content and provenance. Data portals often provide known-good snapshots of datasets that support research, open government, AI development, BI development, and data mastering tools such as ontologies, reference datasets, and controlled vocabularies. ## Steps to using CKAN + CsvPath There are only a few steps to start using the CKAN integration: * Create or get access to a CKAN instance * Make two small changes to your CsvPath config.ini * Add CKAN directives to a csvpath * Load your named-paths group and run it The first one is the only heavy-lifting. If you have a CKAN instance you can skip it. The remaining three steps should take you about 15 minutes, using CsvPath's CLI and following the instructions below. First, a screenshot of CKAN + CsvPath and a word about why are we doing this. ## The gap between data lake and data portal **High quality data is useless unless it is known and accessible to solve high-value problems. Likewise, a high-value data portal connecting consumers to producers is useless if the data produced is untrustworthy.** Most organizations have piles of data. Many organizations have some form of a data lake. Regardless of tooling and investment, most data lakes quickly become a collection of messy, lossy, inexplicable silos. How can the right data get to the data portal in a validated, known-good form for clear presentation to consumers? CsvPath can help. Data products are an interface to a data operation. One of CsvPath's core use cases is in the automated validation, canonicalization, metadata management, and publishing of data products to data catalogs. It is the filter that guarantees that a known source presents known-good data in an expected form through a durable and explainable process. CsvPath does this by applying the [**Collect, Store, Validate Pattern**](broken://pages/OejPZGOrRCbYG54Vym2l) to the challenge of data departure. Collect, Store, Validate centralizes operations, makes processes highly consistent, keeps records in the form of immutable intermediate products and metadata, and verifies that data matches a schema and/or set of business validation rules. The details of how CsvPath does this are on every page of this site, so here we'll just focus on linking up CsvPath and CKAN. Our goal here is to get CsvPath to post valid data to CKAN. It will look something like this screenshot of files that came from running a named-paths group.

The results of a CsvPath run in CKAN

## Installing CKAN This section isn't a step-by-step how-to for installing CKAN. The [instructions for setting up CKAN are here](https://docs.ckan.org/en/2.9/maintaining/installing/install-from-package.html). This page may help too, but rely on CKAN's docs first and foremost. A CKAN implementation comprises: a Python web application, Python applications and APIs for data management, a Postgres database, and a Solr search engine. We used the package method CKAN's docs suggest. While we aren't Docker wizards, we can share a quickly-made dockerfile that helped us. No doubt you can improve on it! {% file src="/files/gF1SQMkU6Cv5GzvP9EfH" %} This is not a sophisticated solution, but feel free to use it as a getting started aid. {% endfile %} We installed CKAN on a Mac with Apple Silicon. That required this run command that accounts for the different architecture: ```bash docker run --platform linux/amd64 -p 80:80 -it ckan_local:latest ``` The dockerfile uses this script to do some setup work that we didn't bother to automate for our dev and test instances. Obviously this isn't how you'd do it in a regular dev or production setting. {% file src="/files/ZM46rgG546XBcZHKsL2Q" %} Remember to edit the ckan.ini file to have your server IPs and passwords. This file lives at: `/etc/ckan/default/ckan.ini`. With that dockerfile we had a basic CKAN server up and running in just a few minutes. Raising the [Solr](https://solr.apache.org/) instance using CKAN's Solr docker image was even more of a snap. You should be able to login at . If all is well you will see the CKAN frontpage.

You should see this page on http://localhost:80

CKAN isn't ugly, but it does look plain just out of the box. To see for yourself how beautiful CKAN can be take a look at [the CKAN showcase sites](https://ckan.org/showcase). ## Setting up CsvPath Next let's set up CsvPath to talk to CKAN. This part should be a snap, partly because it's simple and partly because you've probably done it already from other examples on this site. We'll use the example from [Another Example](/topics/how-tos/a-longer-example/another-example-part-2). First create a Poetry project. You can use Pip or any tool you like, but we like Poetry. In the terminal do: ```bash poetry new ckan ``` `cd` into the new `ckan` project directory. Then add CsvPath to your new project like this: ```bash >> poetry add csvpath Using version ^0.0.501 for csvpath Updating dependencies Resolving dependencies... (1.3s) Package operations: 31 installs, 0 updates, 0 removals - Installing certifi (2024.12.14) - Installing charset-normalizer (3.4.1) - Installing idna (3.10) - Installing packaging (24.2) - Installing six (1.17.0) - Installing typing-extensions (4.12.2) ... ``` You'll see a few more dependencies installed than I'm pasting in here, but otherwise, that's it. Now we'll copy the files from the [Another Example](/topics/how-tos/a-longer-example/another-example-part-2) pages. The ones attached here are slightly updated so use them, even if you did the example and have your own. Put the csv file in: `assets/csvs` and the csvpaths in `assets/csvpaths`. You *can* put these files anywhere within the project, really, because we are going to use CsvPath's CLI to import them, but for now, still with those directories. Put the JSON file in the project root directory; again, it *could* go anywhere you like within the project. {% file src="/files/gm6PPSe5OpMampGu6sZd" %} {% file src="/files/GQohaVzwJfUmCV3kBS1k" %} {% file src="/files/64YoSU7a6njg77I6Z5nz" %} {% file src="/files/aLb9vLz1RTGyRoGowDp2" %} {% file src="/files/Fm011sPckDhG9MqZ8E6p" %} {% file src="/files/wrYwbt9WAVPZZ1kpDHR7" %} {% file src="/files/oa5dKaETatIm8qEzcMar" %} {% file src="/files/t5f1UzEabIH8v2ZWXiZW" %} {% file src="/files/lpjg1WxPBaTjIu7A5qM7" %} That makes: * 7 csvpath files * 1 csv file * 1 json file ## Config file update The integration works by adding a CKAN listener to `results` events. A `results` event is generated when a run starts or completes. To tell CsvPath to include the CKAN listener we need to make a small change to `config/config.ini`. Since it's much simpler to have CsvPath create a default config file, let's fire up the CLI to give CsvPath a chance to generate it. In the terminal do: ``` poetry run cli ``` If you aren't using poetry have a look in the pyproject.toml to see the command we're running so you can run it yourself. When the CLI comes up you should see:
Check to make sure the config directory was created. If it was, select `quit`. Next open `config/config.ini`. Check the CKAN listener configuration under the \[listeners] section. If it is commented out, remove the `#` comment marker. Then add ckan to the listener `groups`. Your file's `[listeners]` section should look like:
Create an API token in CKAN in your profile page. It's [a quick task described here](https://docs.ckan.org/en/2.9/api/index.html?#authentication-and-api-tokens). Add your API token to the `api_token` key in the `[ckan]` section. If your CKAN server is at a different address, change the server key to point to it.
While you are in CKAN, create an organization called `Archive`. Click on the `Organizations` tab and then click the `Add Organization` button.
CsvPath's `Archive` will map to CKAN's `Archive` organization. You can change the name of the archive to anything you like (the setting is in `config.ini`), but for now, stick with `Archive`. Now CsvPath will send named-group run `results` events to the CKAN integration so that it can post metadata and files to CKAN using CKAN's API. ## Adding CKAN metadata directives The last part of connecting CKAN and CsvPath is to add instructions for how the events should be handled. The instructions will be in the form of metadata directives, similar to CsvPath's [modes settings](/topics/practical-stuff/the-modes). Metadata directives are instructions you put in the external comments of a csvpath. They are special metadata fields that the CKAN integration looks for. Metadata fields are created by keywords followed by colons, like: ``` description: this is a user defined metadata field named description. lunch-menu: this is another user defined metadata field, it is named lunch-menu. ``` In this example `lunch-menu` starts a new metadata field because it has a colon. That means that the `description`-keyed metadata is: `this is a user defined metadata field named description.` Metadata goes in external comments. An external comment is one that is outside the csvpath; above it or below. Here are the possible directives with possible values and/or examples. You can [learn more about CKAN directives here](/topics/how-tos/ckan/ckan-csvpath-setup). * `ckan-publish`: `always` | `on-valid` | `on-all-valid` | `never` * `ckan-group`: `use-archive` | `use-named-results` | any alphanum string * `ckan-dataset-name`: `use-instance` | `use-named-results` | `var-value:name` | a literal * `ckan-dataset-title`: a metadata field name | `var-value:name` * `ckan-visibility`: `public` | `private` * `ckan-tags`: any alphanum | `instance-identity` | `instance-home` | `var-value:name` * `ckan-show-fields`: e.g. `line_number`, `identity`, `validation-mode`.... * `ckan-send`: `all` | `printouts`, `data`, `metadata`, `unmatched`, `vars`, `errors`, `manifest` * `ckan-printouts-title`: e.g. `Background` * `ckan-data-title`: e.g. `Orders` * `ckan-unmatched-title`: e.g. `Orders` * `ckan-vars-title`: e.g. `Orders` * `ckan-meta-title`: e.g. `Orders` * `ckan-errors-title`: e.g. `Orders` * `ckan-split-printouts`: `split` | `no-split` Yes, that's a lot! You won't use them all, and very likely you will come to appreciate the flexibility. When you are first getting started you may want to have the [docs page](/topics/how-tos/ckan/ckan-csvpath-setup) at hand. Here's how we updated the `sku_upc.csvpath` file with CKAN directives. You don't have to use all of these, but it doesn't hurt to try them.

That's a lot of CKAN instructions to apply to your csvpaths. Most likely you'll usually use fewer than this.

{% file src="/files/qzIq1OCx2nhFmkR41pLt" %} ## Let's see it working! To see the CKAN integration in action we have to run a csvpath using a `CsvPaths` instance, of course. The fastest way for us to do that is using CsvPath's minimalist CLI. Creating a small Python driver script is also super simple, but the CLI allows us to even skip that little bit of Python. Fire up the CLI again using `poetry run cli`. You will again see:
We're going to stage our data file and load our csvpaths. That essentially means we're going to import those assets into the CsvPath Library's workspace so we can run our named-paths group. A named-path group is simply a collection of csvpaths that are run as a single group and known by a name. Hit return with `named-paths` selected.
Then select `add named-paths`. Give your paths a name. Call them `Orders`.
Next we're going to tell the CsvPath Library where the csvpaths that will go into `Orders` are. We'll do that with our JSON file. Select JSON.
And pick your `orders.json` file that you downloaded a moment ago.
We're done setting up our named-paths group. Now let's stage our data as a named-file. The process is the same. Click on named-files.
Then click on add-named file.
Call your file `March-2024`.
We have our file handy and it's just the one we stuck in the assets directory, so let's just pick it specifically. Our other options would be a list of named files in a JSON or adding all the files in a directory named by their filenames. Select `file` and hit `return`. Follow along with the next three screenshots.
After selecting the CSV file it should import and take you back to the top menu. Now we're good. Time to run our named-paths group and see the results in the `Archive` directory and also automatically promoted into CKAN. Running a named-paths group is easy. Select `run`.
The CLI asks you for a data file first. Pick the one you are offered. It's what we just staged.
Next we pick the named-paths group. In this case we have two choices because there were two groups in the JSON we used to define and load the groups. `top_matter_import` is used by `orders`. `orders` is the group we want to run. Select `orders` and hit `return.` Lastly, the CLI wants to know what run strategy you want to take, `collect` or `fast-forward`. The `collect` approach captures all the lines that match your csvpaths' rules. `fast-forward` simply runs the csvpaths without capturing matches. Doing a `fast-forward` run gets you variables, errors, validations, etc. so it is quite useful, and also lightweight. But for our purposes here let's use `collect` to capture matches.
And away we go! Your run will produce validation messages and informational printouts. We intentionally fed the csvpaths data with problems. And we created some output that is going to a separate `Printer` instance so we can see how multiple printouts can be created. What you see should look like:
Your run produced lots of assets in your new archive directory. Let's have a look. Open the project's root directory and drill into the `archive` folder. The Archive is where the CsvPath Library stores results. You can name it anything you like — `archive` is just the default. This is what you should see:

Your archive after one run of the orders named-paths group, as seen in the MacOS Finder

In this image you're looking at the `order` group result flies for the `2024-12-25_04-46-55` run (in my case; your run identifier will be different, of course) in the results of the csvpath identified as `upc-sku`. These files are: * The `data.csv` of matched lines * The `unmatched.csv` of lines that did not match your csvpath's rules * The `manifest.json` that gives metadata about the `upc_sku.csvpath` part of the named-paths run * `meta.json`, a file of any user-defined metadata and the runtime metadata and stats * Our `printouts.txt` containing all the printed statements from the run * The `vars.json` file that contains all the variables that were created during the run. That's all standard CsvPath stuff. We haven't looked at anything specific to CKAN yet. Keep in mind that the files you see in the screenshot are for just one of the six csvpaths in the `orders` named-paths group. All six csvpaths were run against the input data file. Each has its own outputs. In this example we are only sending results to CKAN for the `upc-sku` csvpath. ## The CKAN results What should we see in CKAN? Looking at our `upc_sku.csvpath`'s metadata you can see what we're asking for: * `ckan-group: A Big Test`. This says we want to have our results associated with a CKAN group. If the group doesn't exist, it will be created. * `ckan-dataset-name: orders_march`. We're explicitly giving our CKAN dataset a name. The name will become a slug in the website and an identifier that can be used like an ID in some cases. The dataset will also have an autogenerated ID. * `ckan-dataset-title: Orders March 2024`. Setting a title gives the dataset a prettier name than if we just used the actual name. * `ckan-visibility: public`. As you would guess, we're making this dataset immediately visible to anyone with access. * `ckan-send: data, printouts, unmatched`. This is the big one. Here we say what data we want to send to CKAN. In this case, we send three of the standard files CsvPath generates to CKAN. * `ckan-split-printouts: split`. Printouts come from calling `print()` on `Printer` instances. Each printer is separate and handles print statements in its own way. The `upc_sku.csvpath` uses both the default printer and also a different named printer, called `Headers by line`, for some printouts. In the `printouts.txt` the default and `Headers by line` printouts are separated by a delimiter so they can be easily extracted. With `ckan-split-printouts` we can split the printouts into one file per `Printer` instance. This makes it easy to create user-friendly focused reports that are delivered in CKAN in a way that is clear for report readers who don't know CsvPath. In this example, our default printouts are the validation errors. The `Headers by line` printouts report the headers in effect at each line in the file. In CKAN these reports will be separated into two files and given titles that make clear what each file contains. Because we have two sets of printouts and are splitting them we will send CKAN four files total, not three. * The remaining CKAN directives assign more helpful names to the files we're sending to CKAN. Again, we don't want to assume that all CKAN users know what CsvPath's standard files contain. Our assumption is that CKAN users should not have to know the details of CsvPath Language or the CsvPath Library's workflow. What we get is a dataset in the `Archive` organization associated with the `A Big Test` group titled `Orders March 2024`. When you open the new dataset it looks similar to this screenshot.
Each time we rerun our named-paths group we will get new data and metadata files in a new run directory. And each run's events will be forwarded to CKAN. The result will be that this page is updated, new versions are captured, and the activity stream is updated *(Side note: if for any reason you want to delete your group or dataset and start again, remember that you have to log in as admin and empty the trash at* [*http://localhost/ckan-admin/trash*](http://localhost/ckan-admin/trash) *to hard delete your assets. CKAN uses soft deletes. Simply deleting as a regular user doesn't clear assets out of the CKAN system.)*
Meanwhile, back in CsvPath, the archive and the inputs directories will capture each change of all your artifacts for every run, in perpetuity with clear identities and hash codes to help you pin down exactly what happened if you should ever be asked about the lineage or chain of custody. And should there be a validation failure, that problem will never get to CKAN — instead you'll be able to handle it at the source and only promote trustworthy data to CKAN and its data customers. There's a lot going on in this integration. At a high-level it's quite simple. Of course, the configuration details and use cases will settle in gradually. Spend a bit of time exploring. You'll be impressed with what CKAN offers and how well its mission fits with CsvPath's. # CKAN CsvPath Setup CsvPath is integrated with CKAN, the leading open source data portal. [CKAN](https://ckan.org/showcase) is a portal purpose-built for data discovery and distribution. It is backed by the [Open Knowledge Foundation](https://okfn.org/en/) and used by large-scale data publishers, from the US federal government's [data.gov](https://catalog.data.gov/dataset/) to LEGO. CKAN is integrated with CsvPath through the event listener mechanism. When named-paths groups run, CKAN is notified and receives content. The integration is standard and requires only two minor changes to `config.ini` to activate. See this page with [more step-by-step guidance on getting started with CKAN](/topics/how-tos/ckan). ## Setup the link to CKAN Open `config/config.ini` (or wherever your config file is). We have two changes to make: * Enable the listener * Add the server details Look for the `[listeners]` section in `config.ini`. Make sure the `ckan.results` key has the name of the CKAN listener class. Second, add ckan to the list in the `groups` key. If there are other groups enabled put `ckan` at the end after a comma. Your file should look like this:
Next, make sure you have a `[ckan]` section. It should have two keys: * server * api\_token The server key takes a URL for your CKAN instance. If you're just trying out CKAN you may be running CsvPath on the CKAN server or, more likely, on the same machine as you are running the CKAN docker containers. In this case, put in `http://localhost:80`. The `api_token` key takes your CKAN token. Log into CKAN and open your profile (see the link at the top right of every page). You should see three tabs. Click on the tab for API tokens and create one. Paste the value into your `config.ini` on the `api_token` key. Your `config.ini` should look like this:
## The directives You're all integrated! Time to see what you can do with CKAN. Publishing datasets to CKAN requires that you add directives to your csvpaths. The directives go in an external comment. An external comment is one that is outside the csvpath, above or below it. You can add as many CKAN directives, modes settings, user-defined metadata, etc. as you like in your comments. A set of directives might look like:
Let's go through what these directives mean. | Directive | Values | Explanation | | ---------------------- | --------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ckan-publish` | `always` \| `never` \| `on-valid` \| `on-all-valid` |

These have the meaning you would expect. The default is that csvpath's results are not published to CKAN.

on-all-valid means that all csvpaths in the named-paths group must be determined to be valid.

Remember that is\_valid is True by default. A csvpath that has an internally defined validation failure (e.g. treating "five" as an integer) may be marked invalid, depending on your configuration, without you having to use fail() explicitly.

| | `ckan-group` | `use-archive` \| `use-named-results` \| any name |

Results will be associated with a group if this directive is used. If the indicated group doesn't exist it will be created.

use-archive means that the name in config.ini under the archive key is used to identify the group.

used-named-results makes the group name the same as the named-paths group's name. Remember that named-paths group names and their named-results names are the same.

Any name means any user-friendly word or words you like.

| | `ckan-dataset-name` | `use-instance` \| `use-named-results` \| `var-value:name` \| any name |

use-instance means use the identity property of the csvpath. The identity of a csvpath is the name or id field in its metadata (so set in an external comment) or its zero-based index in the run.

use-named-results means the dataset should have the same name as the named-paths group.

var-value points to a variable, the word after the colon. The value of the variable will be used for the name.

Alternatively, any word or words.

| | `ckan-dataset-title` | `var-value:name` \| any title |

var-value points to a variable, the word after the colon. The value of the variable will be used for the title. E.g. var-value:city would indicate that the dataset's title would be something like "New York City".

Alternatively, any user-friendly title.

| | `ckan-visibility` | `public` | The default is `private`. Any word other than `public`, or no setting, results in the dataset being marked private. | | `ckan-tags` | Any alphanumeric words or numbers separated by `-`, `_` or `.`. Separate multiple tags with commas. | CKAN's tags are super useful for grouping assets for management and discovery. These are assumed to be simple free-form tags, not CKAN's vocabulary-controlled tags. | | `ckan-show-fields` | Names of metadata fields separated by commas. See any `meta.json` for the available fields. |

This setting allows you to include any field from meta.json as a named-value in the dataset's main view.

Recall that meta.json contains all the metadata (settings, user defined fields, and ad hoc comments or documentation) and runtime data (line\_number, number of matches, current headers, etc.)

| | `ckan-send` | `data` \| `printouts` \| `unmatched` \| `errors` \| `meta` \| `vars` \| `manifest` |

The names of the standard files you want to send to CKAN, minus their extensions.

Note that transfers and any Jinja files are not an option. Also note that you do not need to call out individual printers.

| | `ckan-split-printouts` | `split` |

A csvpath may have any number of Printer instances for various purposes.

Each printer prints to its own print stream (which may or may not include standard out on the console). All the printers' captured printout lines are added to the same printouts.txt with a separator marking each contribution.

The CKAN integration can split the different printers' print output into separate files before sending them to CKAN. If you do this, each file will be named by the name of its printer.

This feature can be useful for keeping different kinds of reports. E.g. you could let the built-in validations go to the default printer, but send your own business rule validations to a separate printer.

| | `ckan-printouts-title` | Any title | Any title for the default printer file | | `ckan-data-title` | Any title | Any title | | `ckan-unmatched-title` | Any title | Any title | | `ckan-vars-title` | Any title | Any title | | `ckan-meta-title` | Any title | Any title | | `ckan-errors-title` | Any title | Any title | In general, user-friendly titles may include upper and lower case, spaces, and common punctuation. Most names, URL slugs, IDs, tags, etc. can only have alphanums, `.`, `_`, and `-` and will be lowercased. # Jenkins An example of using a simple automation tool to feed data to CsvPath We run eight nightly builds testing CsvPath Framework with different storage backend and OS combinations using Jenkins. It occured to me that since using Jenkins is a good tool for automated CsvPath preboarding, our simple use case might make a good how-to. First, what we did. Our setup is mainly for unit and integration tests. That's not exactly the same as the preboarding step in a data onboarding process, but it's close enough that it might be 90% of what you need. We pulled [the official Jenkins docker image](https://hub.docker.com/r/jenkins/jenkins/) and execed into the running container using a command like: ``` docker exec -u 0 -it jenkins bash ``` We installed Python and [Poetry](https://python-poetry.org/). CsvPath Framework supports Python 10 or greater. We use the latest version of Poetry. We installed Poetry using [Pipx](https://github.com/pypa/pipx). The [instructions for doing that are here](https://python-poetry.org/docs/#installation). It should all take just a couple minutes. Next, log into the Jenkins admin console. (By default: ). On the Dashboard, look for `+ New Item` at the top left. In the new item form, give your new workflow a name and choose `Freestyle project` as the item type.
In the configuration form that opens you have a few options. Our goal was an automated build of the CsvPath project, so Git was the first important fill-in for us.
For you, Git may not be important. If you're just running a script to start onboarding data, and it doesn't live in Git, you wouldn't need this part. However, automation scripts should be in a source control system so the Git setting is important for most people. Next, we wanted nightly builds. Your data may arrive on some other schedule, but let's say you want to run nightly. You would add a cron-like expression to say when. We used `H H(21-23) * * *` to run at the end of every day.
Finally, we added a build step to run simple shell commands: ```sh export CSVPATH_CONFIG_PATH="assets/config/jenkins-local-azure.ini" export AZURE_STORAGE_CONNECTION_STRING="DefaultEndpointsProtocol=https;AccountName=********" /var/jenkins_home/.local/bin/poetry install /var/jenkins_home/.local/bin/poetry run pytest ``` It looks like:
Our `CSVPATH_CONFIG_PATH` env variable points to a config file in the assets directory of the project. You might just use the default `config/config.ini`. {% hint style="warning" %} Remember that the `[config] path` value in your config file must be the same as that of the file it is in. If it is not the same path, CsvPath will reload from that named config file. This makes it easy to bootstrap from a default config file generated by CsvPath. But if you aren't bootstrapping and don't want to use `config/config.ini` you must remember to sync the path key with the file name. {% endhint %} Since that config file is specific to this Azure Blob Storage build, so we don't have anything to do there. Your config file will of course be different from ours. Your script will also be different. We're running pytest. You would probably want to be running something like more like:
Admittedly, there are more elegant ways to create a simple preboarding script. But that's the basic Python code you'd need. 5 lines (+/- whitespace). Not bad. You'll need your own paths and names, of course. Save the job and try running it. # config.ini Configuring CsvPaths is easy. The first step is adding it to your project using your Python dependency management tool of choice. Then create a simple test harness file and run it. ``` from csvpath import CsvPath path = CsvPath() ``` CsvPath will create a config directory. The config directory will have the default config.ini file. The default config file has sensible defaults. If you want to move your config to another location, simply add the other location to the default config file as path.
Once you do that, you can clear out any other configuration values. Or you can leave them. Either is fine. When you start CsvPath it will check to see if it should load your config from another location. It will find the location in an env variable or in the path key in the default config.ini.
## The Defaults When you start a new CsvPath project in an empty directory, the first time you run a `CsvPaths` or `CsvPath` instance `Config` will create default assets for you: * The `config` directory and a default `config.ini` * An empty `log` directory * An empty `cache` directory * And, when you use a `CsvPaths` instance, an `archive` directory to hold serialized results The `log` directory will be empty until your first run. The `cache` directory will be empty until your first `CsvPaths` instance run. `CsvPath` does not cache. # Debugging CsvPaths is a declarative rules-based path language. As such, creating csvpaths can be drop-dead easy for easy things. For more complex situations you will undoubtedly sooner or later come to a head-scratcher. You can of course run CsvPath in an IDE debugger. But what other options are there for gaining an understanding of what your csvpath is doing? Here are some ideas. * [Logging Levels](#logging-levels) * [Error Policy](#error-policy) * [Emit JSON](#emit-json) * [Stop Early](#stop-early) * [Create a Simple Test Csvpath](#create-a-simple-test-csvpath) * [More Advice](#more-advice) * [The Source Code](#the-source-code) ## Logging Levels The obvious place to start is with the log. By default CsvPath logs to ./logs. That will probably not be ideal for production. You can easily reset it by changing config.ini. You also have the option to reset the path to config.ini when you create your instance. You could even populate the csvpath.util.config.CsvPathConfig instance programmatically. (You probably shouldn't, but you can!) Here's an example of the config showing all the log-related options.
As you can see you have logging and error options for CsvPath and CsvPaths. If you use CsvPaths you still use the settings for CsvPath for those instances that CsvPaths is managing. The logging is, today, heavily focused on matching. That's where most of the complexity creeps in. You can see when matching starts, what the match structure is, how the match components behave and what their focus is — meaning are they value producers, match deciders, or side-effects. Lots of information.
### debug() You may also want to try the `debug()` function. As a csvpath function you can use it within your csvpath to control how much logging happens at any particular point. It isn't a precision tool. Your use of `debug()` is only effective at the line level, meaning you can't adjust the log level match component by match component. Still, `debug()` helps you cut down the log noise considerably so you can focus on the logging that is most important to you. ### vote\_stack() The vote\_stack() function is another potentially helpful debugging match component. Its job is to tell you how the match components voted on a given line. The best way to use vote\_stack() is to push it: ```xquery push("votes", vote_stack()) ``` This will populate your `votes` variable with a list of `True` / `False` votes for each of the match components. That can help you understand why a match happened, or didn't, in more complex csvpaths. ## Error Policy CsvPaths and CsvPath have a multi-setting error policy. If you need more information, or more or less run protection, you should have a look. The options are: * `raise` — reraises exception, at the risk of interrupting production processes * `collect` — collects Error objects with the associated Exception * `stop` — stops the run, as if your csvpath called the `stop()` function. Keep in mind you are only stopping the CsvPath the error occurred in. If you are working in the context of a CsvPaths instance, CsvPaths will either: * Continue by starting the next CsvPath, if any, if you are processing serially, or * If processing breadth-first, continue with the next CsvPath in the list of CsvPaths considering each line until the run completes or all CsvPaths enter the stopped state * `fail` — indicate that the CSV file is invalid. This does not stop the run. * `quiet` — handles the error quietly with minimal additional noise in the log, low information availability, and no threat to the continuance of the production runtime You can activate multiple of these options at once. `quiet` and `raise` obviously don't go together. Keep in mind that your errors are collected by default, but if you remove `collect` from the error policy your error collector will not keep them. `quiet` without `collect` is a recipe for losing information. ## Emit JSON The Matcher instance held by a CsvPath can output JSON for its match components. The match components are held as Expression instances in a list of expressions in the Matcher. To produce JSON you'll want to do something like: ```python csvpath.matcher.dump_all_expressions_to_json() ``` You can also explore the expressions like this: ```python for i, e in enumerate(csvpath.matcher.expressions): json = csvpath.matcher.to_json(e[0]) print(f"JSON for expression[{i}] is {json}") ``` ## Stop Early You have a lot of control over how many lines and which lines CsvPath sees, as well as when to stop processing. Remember that you can have the scanner pick out just one line with something like: ``` $my_test.csv[7][ print("what the heck?") ] ``` Sometimes that's the simplifying condition that helps you see what's going on. Likewise, `stop()`ing early can act as a kind of breakpoint leaving you with access to the variables, match components, etc. ## Create a Simple Test Csvpath This may seem like obvious advice. CsvPath provides good options for breaking down sizable validation into smaller steps with good separation but still runnable as a unit. When you hit a hard problem, try to isolate it in its own csvpath. You can run it with other csvpaths in CsvPaths or you can `import()` it into another csvpath like a component. Running simple paths together in CsvPaths has the added benefit of allowing you to easily separate out the problem csvpath's print statements and errors — they are held separately in each csvpath's Printer and CsvPathErrorCollector instances. ## More Advice * `print()` is your friend — if you've been leaning on debuggers rather than print statements lately you may feel like the debugger is the better way to go. Try print as well. It is simpler than learning the CsvPath internals and provides access to [a large amount of metadata](/topics/practical-stuff/the_reference_data_types). * Use the $.csvpaths.headers field — headers change mid-file because CSV. You can dump the current headers using the headers reference in a print statement. You can dump the line to compare to the headers using the `print_line()` function. Keep in mind that while the CSV file's headers may change, your csvpath's headers do not change unless you use `reset_headers()`. * `push()` line-by-line indicators — consider using `push()` to push indicators, variables, headers, etc., into a stack variable, line by line. This can be a handy way of seeing how state progresses over a run. * Check counts vs. numbers — one of the CSV file things you wouldn't think would be hard turns out to be harder than expected: counts vs. references. When you refer to a line or a header you are pointing to an item in a 0-based list. When you count something you are indicating how many times you've seen it; implying a 1-based counter. When we talk about match counts and line numbers, or even line counts and line numbers, we are talking about different kinds of things. Then there's the distinction between "physical" lines and "data" lines. The former are essentially a count of line feed characters in a series of bytes. The latter is a series of line feeds plus the content between them. And finally, there is the problem of lines that have whitespace but no delimiters, as well as lines with too few or too many delimiters. Lines with a single space look blank to us. Technically they are a single header line containing whitespace. CsvPath takes pity on us and treats those as blanks, meaning non-data lines. You can inspect all these numbers in the line monitor. For a quick understanding, do: `print(f"{csvpath.line_montor}")` * Were the variables frozen? CsvPath always calls any `last()` functions on the last line scanned or the end of the file. However, if the csvpath is not activated for that line because the line is empty and would usually be skipped, the `last()` functions still run, but in a restricted context. Their variables are frozen, including stacks and tracking values. That is usually not a problem — `last()` not running would be a much bigger issue — but it has the potential to be mysterious in some corner cases. * Value Producers, Match Deciders, and Side-effects — remember that different match components have different focuses. This primarily goes for functions. A function can be a producer, a decider, or a side-effect. Where this becomes interesting is when producers match or deciders give values. Typically the behavior is what you would expect. E.g. a match decider producing a value will return True or False. However, you could run into one of two gotchas. A match decider that is being assigned provides its value, which in a small number of cases may be different from its match decision. {% hint style="danger" %} **An example recently seen** An `any()` with `onmatch` was being assigned to a variable. `any()` is a match decider. But an assignment always receives the value of the component, not its match contribution. Usually, these are the same. Because the `any()` was contributing only `onmatch`, its value was `None` on lines that didn't match. On those same lines, as a non-contributor, its match defaults to `True`. Then that same `any()` was wrapped in a `not().` This was the second gotcha: *assignment is not transitive*. Meaning that the `any(),` now `not(any.onmatch()),` was no longer being assigned. The `not()` was. That meant the `any()` reverted to its more typical result, its match decision. These are rare gotchas! But of course, people may see them. Once you know the logic, the behavior makes sense. {% endhint %} * Access the variables and metadata programmatically — there are several places to look for indicators if you want to do it programmatically: * **CsvPath** * csvpath.metadata * csvpath.headers * csvpath.variables * csvpath.line\_monitor * csvpath.matcher * csvpath.scanner * csvpath.printers * **CsvPaths** — in CsvPaths the main place to look is the results manager and its named sets of CsvPathResult objects. Remember that results are named for the csvpath sets that created them. That means your named-paths name is the same as your named-results name. * csvpaths.results\_manager ## The Source Code Finally, if nothing else gives you the information you need, [the source is available here](https://github.com/dk107dk/csvpath/tree/main). While I wouldn't suggest diving into the library code as your first resort, CsvPath is a pretty small library and should be pretty accessible. # Creating a Derived File How would you create a new CSV file from an existing CSV file? Easy! * First set up a project * Create a `CsvPaths` * Load the file manager and paths manager with the original file and your csvpath * Create a simple csvpath, like the one below (or not so simple, if needs be) * Run your csvpath * Check the `archive` folder to see your results Here is the Python side of things: ```python from csvpath import CsvPaths if __name__=="__main__": paths = CsvPaths() paths.paths_manager.add_named_paths_from_file(name="derived", file_path="assets/derived.csvpath") paths.file_manager.add_named_file(name="data", path="assets/Medicare_Claims_data-550.csv") paths.collect_paths(pathsname="derived", filename="data") ``` As usual, we create a `CsvPaths` instance and feed it a data file and a csvpath file. Then we call `collect_paths`, passing the named-file and named-paths we used. Here's a simple csvpath we could run on some Medicare data. It limits the data collected, adds a column, replaces some text, and creates the new file. It's obviously not the absolute simplest example, but the extra feature-use gives a sense for the possibilities. {% code lineNumbers="true" %} ```xquery ~ name: create derived file description: we're going to create a new csv file that has only the lines and headers we want. : ~ $[1*][ #Question append("Day", now() ) regex(/Acute Myocardial Infarction/, #Topic) -> replace("Topic", "AMI") collect("Topic", "Question", "Day") ] ``` {% endcode %} Let's break this down. Line's 1-5 are just comments. There are two metadata fields. The name field sets the identity of this csvpath. The identity would help us trace validation and syntax errors if we had multiple csvpaths. Since we don't have multiple csvpaths it is really just documentation. Line 6 says we are going to skip the header row. We will of course use the headers, we just don't want to treat them as data. Line 7 is an existence test for values in the header we name. Only lines that have values in `Question` will be collected. Remember that we're ANDing all the match components together to figure out which lines to collect. On line 8 we append a new header that always has the `datetime` value given by `now()`. This line has no effect on matching. Line 11 does a replacement in the `#Topic` header. It changes `Acute Myocardial Infarction` into `AMI`. We're using a when/do expression. The left-hand side of a when/do impacts matching, unless you explicitly say it shouldn't using a `nocontrib` qualifier. Since we didn't use `nocontrib`, line 11 limits our results to lines where the topic header includes the words `Acute Myocardial Infarction`. Finally line 12 limits collection to only the named headers. Those will go into our output data file. This is what your project and results would look like:
The derived file you created is `data.csv`. And your new data would look like this:
It is also possible to use `print()` to create new files. That approach is flexible and may be valuable for certain cases. However, `print()` is a relatively slow function and for most purposes it doesn't add much additional value. `Collect()`, along with `replace()` and `append()`, are usually the better way to go. # A Longer Example # Another Example, Part 1 In the first example we created validation rules for a straightforward file. In this next example we'll take on a harder format, handle more rules, and set ourselves up for production use. Let's dive in! ## Dealing with top matter Some CSV files have documentation or ancillary data at the top, above the main data lines. In Excel this is even more common. While the additional data is useful, it complicates recognizing and handling headers correctly. Validation becomes more difficult. We're going to validate an example like some we've seen in the wild. In this example, you will see more functions, headers, qualifiers, and references. We'll explain them as we go. Afterwards, to learn more about these topics you can take a look at: * [Functions](https://github.com/dk107dk/csvpath/blob/main/docs/functions.md), including the list of all built-in functions * [Qualifiers](https://github.com/dk107dk/csvpath/blob/main/docs/qualifiers.md) * [Headers](https://github.com/dk107dk/csvpath/blob/main/docs/headers.md) * [References](https://github.com/dk107dk/csvpath/blob/main/docs/references.md) and [the reference data types](/topics/practical-stuff/the_reference_data_types) And, for a general overview of the match components, [start here](https://github.com/dk107dk/csvpath/tree/main?tab=readme-ov-file#components). ## The file This is a simplified order for goods from retail stores. It is a monthly report delivered automatically.
Here is the file. It is shortened, but it is enough for this example. {% file src="/files/F4y5OZl9Avfs3inTk3fI" %} There are so many things to possibly validate here. Types of products, IDs, prices. Just sticking with simple checks we could come up with many rules. We'll create a csvpath that applies six rules while dealing with the complicating top-matter. As far as that top-matter, our csvpath has some requirements. It needs to: * Recognize that the header line is line 9 (0-based) * Discard the lines that are comments — unless * There is information we want to capture in that line, specifically: user and run ID Doable? Absolutely! And let's try doing it without just skipping the top 7 lines. Skipping the lines would obviously be trivial. Just create the scanning part of the csvpath like: ```xquery $[8*][ ... ] ``` But let's say we don't trust that those lines will always be there or a consistent number to skip. And, anyway, we want that user and run ID metadata. ## Validation requirements We need to pull two fields from the comments: * The username of the person to contact about the file * The run ID of the process that generated the file As well, say that the orders files will always: * Be more than 10 headers wide * Have just 1 header row per file * Have more than 10 data rows And of course there will be requirements for the fields: * The product category must be correct * Lines must have UPCs and SKUs * Prices must be in the right format If this were an actual order file in a real-world situation we could imaging collecting average selling prices, checking the vender's name, looking for duplicate lines, and more. All things csvpaths can do. We'll step through the strategies for each of the bullets. Then see them together in one file with a trivial Python script that runs the csvpath. Then we'll switch to CsvPaths to create a more long-term management friendly version. That will show you the advantages of small modular csvpaths working together. ## Rule 1: capture the metadata You can capture the metadata using regular expressions. The comment lines are prefixed with a `#` character. We can use that and a regex to grab the values we want and put them in variables. Variables are new; we haven't seen them in the previous examples. Everytime you run a csvpath your CsvPath instance collects several things: * Metadata * Runtime stats * Variables You can [read about these types of data here](/topics/practical-stuff/the_reference_data_types). Variables are like session variables in web development. They come in three flavors: * Strings or numbers * Dictionaries of named "tracking values" * Stacks of strings or numbers We'll see each of these types. The variables are available in: * Your CsvPath instance at `path.variables` (assuming your CsvPath is called "path") * In your csvpath strings using `@varname` * Within any csvpath string `print()` statements at `$.variables.varname` Again, we'll see each of these uses of variables as we go. So, then, let's capture that metadata! Take your last example and replace the rules with these two lines. You can also remove the validity check at the bottom, update the comment, etc. ```xquery starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) ``` Your file will look something like: ```python from csvpath import CsvPath csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() ``` These two [match components ](https://github.com/dk107dk/csvpath/tree/main?tab=readme-ov-file#components)look at comment lines and capture data. ### Starts\_with() Notice that the `starts_with()` function looks at the 0th header, `#0`. We used the `#` character to indicate headers in our last example. This is the same thing, except this time we're pointing to the headers using their 0-based index number. `#0` is the first header. We use `#0` because there is no proper header line at the top of the file so we don't have a name of the 0th header. \#0 looks a lot like the CSV file's comments. But we know comments in CsvPath use the `~` character. This CSV file just happens to use `#` as its comment character. The `starts_with()` function is much like similarly named functions in Python, SQL, etc. It returns `True` if a variable starts with a certain string. ### When/do and notnone Next we have a when/do expression. When the left-hand side of `->` is True, we do what is on the right-hand side. The variable `@runid` will capture the ID of the run if it is found in the comments. It has the `notnone` qualifier. Qualifiers are explained [on this page](https://github.com/dk107dk/csvpath/blob/main/docs/qualifiers.md). The `notnone` qualifier does what it sounds like. It prevents the variable from capturing the `None` value. `None` is the Python way of saying null. Regular expressions Regular expressions, or regexes, match substrings. They are powerful, inscrutable, incredibly useful in virtually every language and many tools, and even sometimes fun. Regardless, they are a deep topic on their own. We won't dissect these expressions here. You can learn more about regexes from many tutorials, including [this one](https://www.geeksforgeeks.org/write-regular-expressions/). This [regex debugger ](https://www.debuggex.com/)may also be a help. The page on [CsvPath's regex tools is here](https://github.com/dk107dk/csvpath/blob/main/docs/functions/regex.md). In brief, we're saying capture a value from a line that has the substring `Run ID:` in the 0th header. ```xquery regex( /Run ID: ([0-9]*)/, #0, 1 ) ``` To recap, now that we have our metadata fields captured from the CSV file's comments your script should look like this: ```python from csvpath import CsvPath csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() print(f"path.variables: {path.variables}") ``` When you run it you will see something like:
Notice that because this is the first time I ran this script in a new project CsvPath created a default config file and a logs directory. But more importantly, we got our two metadata values in the CsvPath instance's variables. Perfect! ## Rule 2: Find the Headers Moving on from the comments at the top of the file, we know there is one header row. Our requirements said there are >= 10 headers. That's easy to spot. One way to do it might be: ```xquery skip( lt(count_headers_in_line(), 9) ) gt(count_headers_in_line(), 9) -> reset_headers() ``` These two [match components](https://github.com/dk107dk/csvpath/tree/main?tab=readme-ov-file#components) handle those requirements. The first one skips a line if the line doesn't have enough headers. At the top of the file, those are probably comment lines and we already took care of the comments. This is an illustration of how order matters in CsvPath. Match components are activated from left to right, top to bottom. What I do in match component A may affect match component B. Or, in this case, we're just skipping B, C, D, etc. altogether. `count_headers_in_line()` explains itself pretty clearly. The `reset_headers()` function updates the CsvPath instance's understanding of what the file's headers are. When we use `reset_headers()` we are saying take the current line as being the header row. That resets the names of the headers to be the values in the current row. When we see the number of line values jump to 10 or more we can safely assume we hit the header row and act accordingly. Here is a more complete approach that includes a few nice-to-have things: ```xquery skip( lt(count_headers_in_line(), 10) ) @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers( print("Resetting headers to: $.csvpath.headers")) print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) ``` The three parts are: * We skip any line with less than 10 headers * Then we reset the headers if they jump to 10 or more * When we see the headers reset the first time we print an alert ### Skipping The `skip()` function is a way to jump ahead to the next line from anywhere in a csvpath. Again, order is important in csvpaths. When we use `skip()` we are saying skip ahead to the next line without doing anything else. In this case, we use the `lt()`, or less than, function to tell `skip()` if it should act. In the next block, we use the `mismatch()` function. `mismatch()` tells us the difference between how many header values we see in a line versus the number of headers for the file. Imagine if a SQL database table had four columns, but in one row there were only three columns. That could not happen in a SQL database, but in a CSV file, it happens frequently. ### Spotting the mismatch We use the string `"signed"` to tell mismatch() that it should give us a positive or negative number, not just the absolute value. Without `"signed"` when there are four headers in the file but only three in a row mismatch reports `1`. With "signed" mismatch() reports `-1`. In our case, we only care if we suddenly have many more headers, so we need to see a positive number. ### Printing an alert The last of the three parts has a lot going on. Here's the match component by itself: ```xquery print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) ``` This is our first use of the `print()` function. Unlike most general purpose langages, in CsvPath printing is a big deal. The reason is that the act of validating a file means using rules to find unexpected things and communicating that information to the user. Schematron is the schema language most like CsvPath. It points to problems and communicate with the user in plain English. (Or your humans language of choice). XSD, JSON Schema, etc. all have the dual purpose of defining data structures and communicating when data doesn't match. CsvPath has some nice printing tricks up its sleeve. We're using a couple of them. First, the qualifiers. Qualifiers are awesome. They can do so many neat things to make match components more powerful. In this case we are using both `onchange` and `once`. Both do exactly what they sound like: * `print()` with `onchange` only prints when the print string is different. That is useful when you are printing variables that may not change in some lines. With `onchange` you only see the `print()` when it has something new to say. * `print()` with `once` simply prints just one time. That's it, one and done. * `print()` with both `onchange` and `once` is even more interesting. It means that `print()` takes its first chance to print. The first printout is considered a change. But then `once` prevents any more printouts. This is good because `mismatch()` will report `0` as the number of headers that are unexpectedly found or missing. That `0` would be considered a change by `print()`. We don't need to be told that nothing happened. As you can see, `print()` can take a second argument that is executed only if `print()` itself runs. We use this to nest a `skip()` that jumps us to the next line. As with most things in CsvPath, we could have done this other ways, but this is a good approach. Your script should now look like: ```python csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers( print("Resetting headers to: $.csvpath.headers")) print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() ``` Running your script should look like what you would expect:
Congrats! You are done with the hard part. The rest of the validations are simple business rules. ## Rule 3: Product Category Next we'll check if the product category is correct. This is a pretty straightforward rule. ```xquery not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) ``` Following the same pattern we saw in the last example, we are going to identify a problem row and print a validation message. The `in()` function looks at the value of the `#category` header and checks if it is in a delimited string. We could equally well ask `in()` to check against the values of one or more other match components. Or, we could even use a reference to point `in()` towards a list of values created by a different csvpath. But for now we're keeping it simple with the delimited list. Notice that we asked `print()` to activate a `fail()` function, as well as printing a message. `fail()` sets the `is_valid` property of a csvpath to `False`. We are saying that the CSV file is invalid. If we needed to, we could use this information programmatically in Python, in CsvPath `print()` messages, or to take action in other match components using the `failed()` or `valid()` functions as triggers. When you run your script now, you see nothing changed. That's because all our categories are correct. To make sure your rule works change line twelve's category from OFFICE to OPERA.
Now you assuming your script looks like this: ```python from csvpath import CsvPath csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers( print("Resetting headers to: $.csvpath.headers")) print.onchange.once( "Number of headers changed by $.variables.header_change", print("See line $.csvpath.line_number", skip())) not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() ``` When you run it you should see something like this:
## Rule 4: Price Format Moving right along, the next rule is that prices must exist and be in the correct format. ```xquery not( exact( end(), /\$?(\d*\.\d{0,2})/ ) ) -> print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail()) ``` Again we use the same rule pattern. In this case, we tap regular expressions again to check that a price: * Exists in the last header * Starts with a $, optionally * Is made of numbers * Has a decimal point * Has at most two numbers byond the decimal point This probably isn't the perfect price field description, but it is pretty good. The new thing here is `end()` function. `end()` is a pointer to the last header. If we're not certain what the last header name or index is we can use `end()` to refer to it. Our options are: * Use a header name, possibly quoted, like `#"a price"` * Use an index like `#14` * Use `end()`, possibly with an offset integer like like `end(-2)` In this case, let's say we know price is always the last column. Drop this rule in your script and run it. If your file follows how we've been creating the example, you will hear some complaints from Python. It is unhappy about invalid escapes. Ah, validity. Easy to fix. Change your line to: ```xquery not( exact( end(), /\\$?(\\d*\\.\\d{0,2})/ ) ) -> print("Bad price $.headers.'a price' at line $.csvpath.count_lines", fail()) ``` The additional `\` chars escape the regular expression escapes for Python. This is normal regular expression fun. We all give up some amount of our sanity for accessing the power of regexes. Your script should now look like: 
from csvpath import CsvPath

csvpath = """$March-2024.csv[*][

            ~ Capture metadata from comments ~
                starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 )
                starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 )

                skip( lt(count_headers_in_line(), 9) )

                @header_change = mismatch("signed")
                gt( @header_change, 9) ->
                      reset_headers(
                        print("Resetting headers to: $.csvpath.headers"))


                print.onchange.once(
                    "Number of headers changed by $.variables.header_change",
                        print("See line $.csvpath.line_number", skip()))

                not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) ->
                    print( "Bad category $.headers.category at line $.csvpath.count_lines ", fail())


                not( exact( end(), /\\$?(\\d*\\.\\d{0,2})/ ) ) ->
                    print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail())
          ]"""

path = CsvPath()
path.parse(csvpath)
lines = path.collect()
It is definitely getting long. But we're getting close to complete. And in Part 2 of this example we'll see how to make the validation csvpath much simpler and more manageable. Run your script and you should see output like this:
## Rule 5: UPCs and SKUs Retailers, distributors, and manufacturers use Universal Product Codes to identify products. Retailers use Stock Keeping Units to identify and count products in inventory. Both are very important numbers to the business. If they were missing the orders file would be for sure invalid. For all that, though, checking that they are present is easy. By now you should expect the pattern we are following: when/do. We're checking the #SKU and #UPC headers. Remember that CsvPath is case sensitive. Paste or type in these two lines at the bottom of the csvpath string in your script. ```xquery not( #SKU ) -> print("Line $.csvpath.count_lines: No SKU", fail()) not( #UPC ) -> print("Line $.csvpath.count_lines: No UPC", fail()) ``` When you run your script you again see no additional issues. So let's make one. In line 11 remove the value in the SKU field, the fourth header from the end: `9933757492`. It should look like this:
Run your script again. You should see something close to:
If that works correctly your script looks like this: ```python from csvpath import CsvPath csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers( print("Resetting headers to: $.csvpath.headers")) print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) not( exact( end(), /\\$?(\\d*\\.\\d{0,2})/ ) ) -> print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail()) not( #SKU ) -> print("Line $.csvpath.count_lines: No SKU", fail()) not( #UPC ) -> print("Line $.csvpath.count_lines: No UPC", fail()) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() ``` We're getting there! One more rule to go. ## Rule 6: Total Expected Lines Our requirements say we are expecting a file that is greater than 10 lines. That gives room for the comments at the top and at least a one or two orders. If we don't see at least 10 lines we fail the file. Let's use this match component: ```xquery below(total_lines(), 11) -> print.once("File has too few data lines: $.csvpath.total_lines.. Contact $.variables.userid about this batch: $.variables.runid at $.csvpath.file_name..", fail()) ``` We'll use `print()`'s `once` qualifier to make sure we don't generate a lot of noise. Also note that we use a double period in two places. Doubling up periods is an escape for when you want to put a period directly at the end of a reference in a `print()`. That is the only place it is needed. With all our requirements met let's take a moment to clean up a bit. We don't need to display the updated headers, now that we know that is working. And we can put the last total\_lines() check first in the list of concerns. Also, a few more comments wouldn't be a bad idea. Here's how your script will look with those changes: ```python from csvpath import CsvPath csvpath = """$March-2024.csv[*][ ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) ~ Check the file length ~ below(total_lines(), 27) -> print.once("File has too few data lines: $.csvpath.total_lines.. Contact $.variables.userid about this batch: $.variables.runid at $.csvpath.file_name..", fail()) ~ Reset the headers when we see the full set ~ @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers() ~ Print the line number of the header reset in case we need to check ~ print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) ~ Check the categories ~ not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) ~ Check the chenck the prices ~ not( exact( end(), /\\$?(\\d*\\.\\d{0,2})/ ) ) -> print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail()) ~ Check for SKUs and UPCs ~ not( #SKU ) -> print("Line $.csvpath.count_lines: No SKU", fail()) not( #UPC ) -> print("Line $.csvpath.count_lines: No UPC", fail()) ]""" path = CsvPath() path.parse(csvpath) lines = path.collect() ``` I bumped up the number of lines requirement so that we could see the rule's effect. When you run this final version of your script you should see something like:
Congrats! You have a very functional csvpath to check your CSV files with. ## A Simple Python CsvPath Runner As we saw in the first two examples, the Python side of CsvPath is easy. ```python import csvpath path = CsvPath() path.parse( ORDER_RULES ) path.fast_forward() print(f"\nThe file is {'valid' if path.is_valid else 'invalid'}") ``` Step-by-step: * We import CsvPath and create an instance that will run our csvpath * Assuming we drop our csvpath into the ORDER\_RULES variable, we parse our csvpath to prepare it to run * Since we aren't actually using the matched lines programmatically let's switch to the `fast_forward()` method. With `path.fast_forward()` we are asking CsvPath to run the CSV file through our csvpath without stopping or returning any data to us. We do that because our validations all print their gotchas report-style. * Last, we print out our csvpath's verdict: is the CSV file goood, or not For this simple example that's enough. But in a production setting you might imagine sending an email, updating a database, moving the file to a good or not good directory, or the like. ## And you're done! That's a lot. But the result is well organized, commented, and self-documenting. Of course, longer validation rulesets are easy to find, but this one isn't trivial. Still, we have to ask, is there a better way? Something more manageable over the long term? An approach that might be easier to develop and debug? Yes, absolutely! We'll take all that on in Part 2 of this example. # Another Example, Part 2 In this second part of the orders data file example we are going to modify our csvpath and its Python runner to be more automation and management friendly. ## Requirements First let's call out the requirements. What are we trying to achieve? * Separate the code from the csvpath * Break down the validation rules * Give ourselves more options for reporting and error handling * Set ourselves up for making references to other data for lookups Let's say more. #### Separate the code Separating the code from the csvpath is straightforward. We don't want to colocate the csvpath in a Python variable. That constrains our formatting choices, complicates updates and the code, and makes the solution single use. We don't want to keep that physical file reference built into the csvpath, either. Our ideal root for our path is this: ```xquery $[*] ``` #### Componentize the csvpath rules Secondly, having our six validation concerns in one file makes us work harder in development. With six rules you have to take all of them into account when you test your csvpath. Writing and testing one rule at a time, separately, is simpler. Our solution, ideally, should let us have six csvpaths that we can run and manage as a group. #### Capture print statements and errors Third, printing validation messages is an excellent way to do data validation. This kind of reporting may seem simple, but simple is good. That said, could we wish for more control over the output? Sure. We might want to craft an email or some other kind of report. It would be nice to be able to do that without scraping the command line. Likewise with error handling. We'll test our csvpath, of course! But at runtime things happen. And keep in mind that the data may change—we don't control that. So we'd like error handling that is robust with outputs that are inspectable. #### References to other data Finally, our csvpath doesn't currently refer to outside data, but it could. And, given what the data is, there is a strong possibility that in a real scenario we would want to check UPCs or company names, or other things against known-good values. To do that we need our references to be able to find other data sets. The way our script is currently set up, we can't do that. ## The Solution We're going to use a CsvPaths instance. CsvPaths is a manager class. It organizes CSV files, csvpath strings, and CsvPath instances and their run results. A CsvPaths instance creates an instance of CsvPath for each csvpath it runs. It has managers to find files, sets of csvpaths to run, and capture the results of runs. And it gives you options that let you decide the best way to run multiple csvpaths for your situtation. As you may already know from other pages, the main difference in setting up a CsvPaths instance is that you need to point its managers to your files. Your results live in the results manager. You access them using the same name as you used for the paths that created the results. That's about it. Easy! ## The Python In this second part of the example, we're going to change our csvpath string more than the Python code. But the Python motivates our changes to the csvpath. So let's start with the Python. Create a new directory and make a Python file with the contents below. Also create a `cvspaths` directory and a `csvs` directory. ```python from csvpath import CsvPaths paths = CsvPaths() paths.file_manager.add_named_files_from_dir("csvs") paths.paths_manager.add_named_paths_from_dir(directory="csvpaths") paths = CsvPaths(print_default=False) LogUtility.logger(paths, "warn") name = "metadata" #name = "reset" #name = "file_length" #name = "categories" #name = "prices" #name = "sku_upc" lines = paths.fast_forward_paths(filename="March-2024", pathsname=name) print(f"lines: {lines}") valid = paths.results_manager.is_valid(name) print(f"is valid: {valid}") ``` None of this is complicated stuff. Let's go through it. ### Printing and logging First we import the CsvPath library and create an instance of `CsvPaths`. We create it with `print_default=False`. `print_default=False` prevents the `CsvPath` instances that run your csvpaths from printing to the default command line `Printer`. Your print statements will still be captured and available with your results from the `ResultsManager`. By default both things would happen: you would see results on the command line and you would also get print statements with your results. Now that you've seen `print_default=False`, go ahead and delete it. We'll want to see the output in the terminal, at least at first. Next, we do another completely optional thing: set the logging level. We're just seeing how to do it, for future reference. Out of the box, CsvPath is already set to `warn` by default. You can [read more about setting up logging and error handling policies here](/topics/how-tos/debugging). ### Named-files, named-paths, named-results On to more important things. First, we set up the named-files and named-paths. Named-files are just short names that point to full filesystem paths. They are convenient and help you keep your CSV files organized. Named-paths are more interesting. These are sets of csvpaths strings that can be run as a group. You set them up by one of: * Creating a `dict` in Python or using a JSON file * Pointing to a directory full of csvpath files * Pointing to a file that contains multiple csvpaths strings We'll try each. When we're done with Part 2 of our orders file example, we're going to have the last one—multiple csvpaths in a single file—but this time in a more DataOps-friendly way than in Part 1. For more background, you can [read more about named-paths and named-files here](/topics/higher-level-topics/data-validation-files-and-storage/named_files_and_paths). ### The fast\_forward\_paths() method After we set up the named files and named paths we have CsvPaths do `fast_forward_paths()`. This is similar to the `fast_forward()` method we called on our `CsvPath` instance in the first part of the example. The difference is that CsvPaths runs all the csvpaths in the named-paths you are using. That's CsvPaths's job: running multiple csvpaths against CSV files. With `fast_forward_paths()` CsvPaths runs your csvpaths serially. That means a few things: * Order is guaranteed * Results of earlier csvpaths are available for later csvpaths to use * Every row is seen before the next csvpath is run, making side-effects like `print()` simpler * Every csvpath has to iterate on the same CSV file, which is not the most efficient way There is an equally simple to use fast forward method that run the csvpaths breadth-first: `fast_forward_by_line()`. That method solves the efficiency problem. But let's leave it as a topic for another time. At the bottom of the Python we pull the results from the results manager using the same name as our named-paths name. We store them under the same name because the results are the result of running those csvpaths. ## Improving Our Csvpath The cool part happens over in the csvpath. We want to achieve a few things. * Easy development of independent rules * Separated results, printouts, and error handling * Overall better flexibility We're going to do this by creating six csvpaths, in place of the one we have now. When we're done we'll have the option of putting them all in one file or in multiple files in one directory. There's only one real challenge with breaking down our big csvpath into multiple little ones. It is a challenge that is specific to this example. The challenge is the top-matter that precedes the data in our CSV file. Each csvpath has to handle the top matter. It definitely complicates things. That exactly why we used it as the example! We're talking about this part: ```xquery ~ Capture metadata from comments ~ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) ~ Reset the headers when we see the full set ~ @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers() ~ Print the line number of the header reset in case we need to check ~ print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", skip()) ``` Since this part came first in our csvpath and shielded all the remaining match components below it, we need a way to give that same shielding to our match components in their individual csvpaths. And we don't want to repeat this code six times. ### top\_matter\_import.csvpath The answer is to use the `import()` function. We can import this fragment into our other csvpaths to get the same effect without cut-and-paste. Creating an importable csvpath is simple because its just a csvpath. Create a file named `top_matter_import.csvpath`. Copy in this csvpath and you're done: ```xquery ~ reset headers when they go up and otherwise if there aren't enough headers just skip ~ $[*][ @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers(skip()) lt(count_headers(), 9) -> skip() ] ``` {% file src="/files/ydNrj8yr6b38m79Q9qyx" %} We're going to create six more csvpaths files. Most will import this one. No reason to go slow since you've already created these once in a single file, so here we go. ### metadata.csvpath ```xquery ---- CSVPATH ---- ~ collect metadata fields from comments ~ $[*][ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) skip( lt(count_headers_in_line(), 9) ) and( @runid, @userid ) -> print(" Contact: $.variables.userid for batch ID: $.variables.runid", stop()) ] ``` {% file src="/files/xkHT32giQ5HKu5pkI2pN" %} Wait, what's that `---- CSVPATH ----` block? I'm glad you asked. It is a separator between csvpaths that live in the same file. It is of course completely optional at this stage because we're using multiple files with one csvpath in each. Doesn't hurt to have it there, though. ### reset.csvpath In this file you'll see our `top_matter_import` for the first time. ```xquery ---- CSVPATH ---- ~ print the line number when we reset headers ~ $[*][ import("top_matter_import") print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", stop()) ] ``` {% file src="/files/cVmkoEB4qWv1aY8wOhcV" %} You may have noticed that these csvpath files have comments outside the csvpath itself. These comments are important. While we're not doing anything with them at the moment, we could add metadata fields that describe the csvpath. If you add an `ID` or a `name` metadata field to a comment, you can use it to reference the individual csvpath, even when it is bundled with others under the same named-paths name. Important, for sure, but we'll look at how to do that another time. Also notice that we call `stop()`. This ends processing for this csvpath. It does not affect the other csvpaths that our `CsvPaths` instance is also running as part of this batch. CsvPaths will count the number of `stops()` and if all csvpaths in the batch are stopped it will stop the run early. Since we're running our csvpaths serially, when CsvPaths gets the `stop()` it just skips on to the next csvpath. ### file\_length.csvpath ```xquery ---- CSVPATH ---- ~ Check the file length ~ $[*][ import("top_matter_import") below(total_lines(), 27) -> print.once("File has too few data lines: $.csvpath.total_lines", fail_and_stop()) ] ``` {% file src="/files/EtyUlJZlU0lH7Hr6l1k5" %} ### categories.csvpath ```xquery ---- CSVPATH ---- ~ Check the categories ~ $[*][ import("top_matter_import") not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) ] ``` {% file src="/files/Al6tZMloCSe3m5amAww9" %} Two more csvpaths to go! ### prices.csvpath ```xquery ---- CSVPATH ---- ~ Check the prices ~ $[*][ import("top_matter_import") not( exact( end(), /\$?(\d*\.\d{0,2})/ ) ) -> print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail()) ] ``` {% file src="/files/5olSv3KVLWvndypXw7F4" %} ### sku\_upc.csvpath ```xquery ---- CSVPATH ---- ~ Check for SKUs and UPCs ~ $[*][ import("top_matter_import") not( #SKU ) -> print("Line $.csvpath.count_lines: No SKU", fail()) not( #UPC ) -> print("Line $.csvpath.count_lines: No UPC", fail()) ] ``` {% file src="/files/JisWVOD8EYkwNPMdoObi" %} There, that's all of them. You should now have seven .csvpath files in your csvpaths dir. It's worth pointing out, you don't have to use the .csvpath extension. We use it because it makes it clear what is contained in the file. But whatever works for you is fine. If you choose to use another file extension take a quick look in `config/config.ini` to make sure to let CsvPath know what your extension of choice is.
Back to your modified script. Here's where we left it. From this you can run any of the csvpath files you just created. Each file will be named in the named-paths manager by its name (minus the extension). Or you could iterate over your csvpaths by name. Ultimately, this isn't the way we want to run them, but it is a good test. ```python from csvpath import CsvPaths paths = CsvPaths() paths.file_manager.add_named_files_from_dir("csvs") paths.paths_manager.add_named_paths_from_dir(directory="csvpaths") names = [ "metadata", "reset", "file_length", "categories", "prices", "sku_upc" ] for name in names: paths.fast_forward_paths(filename="March-2024", pathsname=name) valid = paths.results_manager.is_valid(name) print(f"is valid: {valid}") valid = paths.results_manager.is_valid(name) print(f"is valid: {valid}") ``` The results should be completely unsurprising. These are just the same csvpath steps we created in Part 1. They are just pulled apart for easier development and management. But take a look back at the results of Part 1. That script is far more complex than these small, mostly independent csvpaths. The change is a win for both rapid development and long term maintainability. ## Deployment Choices We're not quite done, though. We want to run all of the csvpaths as a single unit. Sure, we can loop over them, as we just did. But we can do better! Let's take one more step: bringing them all together in a neat package. This need raises the big question: do we want all the working csvpaths in one file or one directory, or do we want to create a JSON file that identifies the groups of csvpaths that are used together? So many options! Luckily all are easy to do. | Approach | As one | Run apart | Run in order | Reuse parts | Easy | Locations | | ------------------ | ------ | --------- | ------------ | ----------- | --------------- | --------------------- | | Named by directory | Yes | Yes | No | Yes | Super easy! | Single dir | | Multi-csvpath file | Yes | No | Yes | No | Most work | Single file | | JSON file | Yes | Yes | Yes | Yes | Not hard at all | Files can be anywhere | All are good approaches. But the JSON option sure has a lot going for it. Let's look at that option first. ### The JSON option Using a JSON file to define the group of csvpaths is not hard. Create a JSON file that looks like this: ```json { "orders": [ "csvpaths/metadata.csvpath", "csvpaths/file_length.csvpath", "csvpaths/reset.csvpath", "csvpaths/categories.csvpath", "csvpaths/prices.csvpath", "csvpaths/sku_upc.csvpath" ], "top_matter_import": [ "csvpaths/top_matter_import.csvpath" ] } ``` This creates a "named-paths" group named `orders`. We need a second named-paths group for the top matter csvpath. It has to be separate so that it can be imported. You would run orders csvpaths using Python like this: ```python from csvpath import CsvPaths paths = CsvPaths() paths.file_manager.add_named_files_from_dir("csvs") paths.paths_manager.add_named_paths_from_json("orders.json") paths.fast_forward_paths(filename="March-2024", pathsname="orders") valid = paths.results_manager.is_valid("orders") print(f"is valid: {valid}") ``` That's easy. At the cost of creating one more file, you get all the benefits with very little work. ### The directory option Now the directory option. Oh, wait, we already did that. Just make two minor changes in the Python: ```python from csvpath import CsvPaths paths = CsvPaths() paths.file_manager.add_named_files_from_dir("csvs") paths.paths_manager.add_named_paths_from_dir(directory="csvpaths/orders", name="orders") paths.paths_manager.add_named_paths_from_dir(directory="csvpaths/top_matter", name="top_matter_import") paths.fast_forward_paths(filename="March-2024", pathsname="orders") valid = paths.results_manager.is_valid("orders") print(f"is valid: {valid}") ``` We still need two named-paths groups, so we have to create a directory for each. Call the directories orders and top\_matter\_import, though the names don't matter. Copy the csvpaths files into their directory. Then we just import the directories, giving each a name. Last time we used `add_named_paths_from_dir` we did not specify a name, resulting in each file being its own named-paths group. ### The one file option The last option, putting all your csvpaths in one file, takes the most work. It's not hard, but it takes a minute, unlike the other two options. Create a file called `csvpaths/orders.csvpath`. Open the file. We're going to fill it with the six csvpaths we created in the six files. They will be separated by the `---- CSVPATH ----` separator. That's four dashes, a space, the word `CSVPATH` in uppercase, another space, and four more dashes. Paste in the paths between the separators. Use the order we named them in our Python script: * metadata * reset * file\_length * categories * prices * sku\_upc The order is not super important to this example. But order is important in CsvPaths in general, so it's good to get used to being aware of it. We're leaving aside our top matter csvpath. It needs to be imported so it needs to be its own named-paths group. Now `orders.csvpath` should look like this: ```xquery ---- CSVPATH ---- ~ collect metadata fields from comments ~ $[*][ starts_with(#0, "#") -> @runid.notnone = regex( /Run ID: ([0-9]*)/, #0, 1 ) starts_with(#0, "#") -> @userid.notnone = regex( /User: ([a-zA-Z0-9]*)/, #0, 1 ) and( @runid, @userid ) -> print(" Contact: $.variables.userid for batch ID: $.variables.runid", stop()) ] ---- CSVPATH ---- ~ print the line number when we reset headers ~ $[*][ import("top_matter_import") print.onchange.once( "Line $.csvpath.count_lines: number of headers changed by $.variables.header_change", stop()) ] ---- CSVPATH ---- ~ check the file length ~ $[*][ import("top_matter_import") below(total_lines(), 27) -> print.once("File has too few data lines: $.csvpath.total_lines", fail_and_stop()) ] ---- CSVPATH ---- ~ check the categories ~ $[*][ import("top_matter_import") not( in( #category, "OFFICE|COMPUTING|FURNITURE|PRINT|FOOD|OTHER" ) ) -> print( "Line $.csvpath.count_lines: Bad category $.headers.category ", fail()) ] ---- CSVPATH ---- ~ check the prices ~ $[*][ import("top_matter_import") not( exact( end(), /\$?(\d*\.\d{0,2})/ ) ) -> print("Line $.csvpath.count_lines: bad price $.headers.'a price' ", fail()) ] ---- CSVPATH ---- ~ check for SKUs and UPCs ~ $[*][ import("top_matter_import") not( #SKU ) -> print("Line $.csvpath.count_lines: No SKU", fail()) not( #UPC ) -> print("Line $.csvpath.count_lines: No UPC", fail()) ] ``` And `top_matter_import.csvpath` should look like this: ```xquery ---- CSVPATH ---- ~ reset headers when they go up and otherwise, if there aren't enough headers, just skip ~ $[*][ @header_change = mismatch("signed") gt( @header_change, 9) -> reset_headers() lt(count_headers(), 9) -> skip() ] ``` Now, with our orders file still in the csvs directory, those same two lines of Python provide the lightning. Your script should look like: ```python from csvpath import CsvPaths paths = CsvPaths() paths.file_manager.add_named_files_from_dir("csvs") paths.paths_manager.add_named_paths_from_file(name="top_matter_import", file_path="csvpaths/top_matter_import.csvpath") paths.paths_manager.add_named_paths_from_file(name="orders", file_path="csvpaths/orders.csvpath") paths.fast_forward_paths(filename="March-2024", pathsname="orders") valid = paths.results_manager.is_valid("orders") print(f"is valid: {valid}") ``` As you can see, this approach has the fewest files. It is definitely a bit less flexible than the other options. But its conciseness allows you to manage few assets, version control and view all the csvpaths together, and run them in the most obviously deterministic way. (Emphasis on the word *"obviously"*; the JSON option is also completely deterministic). Options are good! You can pick whichever deployment option is best for your requirements and work style. Regardless of the choice, you know you are getting the same result. And that's it. Congrats on a job well done! You now have an automation-friendly rule set using a pattern that will scale to any size DataOps operation. And you have three options for how to package and deploy your CsvPath files. Way to go! # How-to videos
There is a small but growing collection of very short how-to videos on Atesta's YouTube. Simple, practical stuff. The videos are focused on FlightPath Data. But they are also great illustrations and how-tos for CsvPath Framework itself. Take a look. {% embed url="" %} # Practical Stuff # Language Basics The CsvPath's Validation Language is simple, declarative, and purpose-built. Here is a quick introduction. As well as in these pages, there is [more information and more examples on Github](https://github.com/dk107dk/csvpath/tree/main). * [Root, Scanning, and Matching](#root-scanning-and-matching) * [File References and Match Components](#file-references-and-match-components) * [Scanning Expressions and Variables](#scanning-expressions-and-variables) * [Comments and Linebreaks](#comments-and-linebreaks) * [When/Do Expressions and References](#when-do-expressions-and-references) * [Multiple Validation Rules](#multiple-validation-rules) * [A Complex Validation Rule](#a-complex-validation-rule) ## Root, Scanning, and Matching
## File References and Match Components
## Scanning Expressions and Variables
## Comments and Linebreaks
## When/Do Expressions and References
## Multiple Validation Rules
## A Complex Validation Rule
# CsvPath Framework Cheatsheet A super high-level cheatsheet for concepts and approaches
{% file src="/files/mFTI0hQR1GAjkTXbhTt6" %} # The CLI CsvPath's command line interface is a productivity booster CsvPath includes a CLI to help you iterate quickly on validation and canonicalization dev. It is also helpful when setting up and testing integrations. While the Python required for CsvPath automations is not hard or extensive, the CLI is often a faster alternative. {% hint style="warning" %} The CLI supports development and triage activities. Ultimately, it is **a rough and ready and effective tool that includes just the minimum a DataOps person would need** to work with CsvPath. In the future there will likely be more capable and polished tools available. {% endhint %} {% hint style="danger" %} Need the best possible CsvPath Framework dev and/or ops environment? Try [**FlightPath Data**](/getting-started/get-the-flightpath-data-app)! The CLI is still useful, but *the future has arrived*. In most cases FlightPath is the better tool for the job. With FlightPath's launch, the CLI will get updates only to fix problems or if someone finds an exceptionally cool way it can fill a gap. {% endhint %}
The main menu of the CsvPath Framework's command line app

Simple and effective!

## Turn it on If you are using Poetry to manage your CsvPath projects just do: ``` poetry run cli ``` Alternatively, you can use a couple of lines of Python to create an entry point runner. ``` from csvpath.cli import Cli Cli().run() ``` When it comes up, you will see a click-through splash screen and then a menu of tasks. As the splash screen says, the CLI tries to make life easier in two ways: * It offers tab completion for named-path group names and name-files names * In most lists (directory drill downs excepted), the cursor jumps to the sorted initial letters when you press a key ## What does it do? At this time (again, the CLI improves frequently), the CLI offers the ability to: * List registered data and register new files * List named-path groups of csvpaths and load new ones * List results and pop open the (local) archive to results directory * Run named-paths groups against named-files * Set some debugging config settings * List functions and see basic usage information {% hint style="danger" %} At this time the CLI only works with local archives and inputs directories. In the future non-local files will likely be supported. {% endhint %} ## Register data CsvPaths registers CSV, Excel, and other delimited data files before processing them. Registration means giving them a durable identity and a well-known location. Think of it as a birth certificate and a social security number at the beginning of its lifecycle. When we register a file we are staging a immutable copy for downstream processes. Most often that will be CsvPath Framework itself. CsvPath will apply named-paths groups to validate, upgrade, and stage modified immutable copies in a consumer-oriented archive. Keep in mind, however, it is possible for consumers to go directly to the registration tree to access files, if validation and canonicalization are not needed. To register data you just need to use one of the `FileManager`'s `add_named_file` method and/or its variants. In the CLI this becomes a three or four step process: * Select `add named file` * Pick the way you want to identify the file: dir, file, or JSON * If you are selecting the file itself, giving it a name * Drill down in your local inputs tree to select the file or directory You can list the named-files you have staged. When you list your named files you are listing the top of your file inputs tree. These are the names in named-files concept. Within each name is a `manifest.json` tracking changes to the named files and the files themselves. Each individual dataset (one version of the named-file) is kept in a hash identified file within a directory that has the same name as the original file that you registered. Sound complicated? Have a look, it's not that bad. But keep in mind that your work with CsvPath is at the abstract named-files name level, not the bytes of physical files. ## Load csvpaths Loading csvpaths means creating named-paths groups. When you run a csvpath in the CsvPath Framework you are usually actually running a group of csvpaths. The group can have just one member. You run and manage groups of csvpaths by a name, hence `named-paths`. The main reason for this approach is that it allows you to break validation and data-upgrading down to fine-grained steps that are easier to develop, triage, reuse, and rerun. {% hint style="success" %} You can of course use the `CsvPath` class to run a single csvpath without a named-paths group. That is not the ideal way to use the CsvPath Framework in production, in most cases. Regardless, the CLI does not support running individual csvpaths using only a `CsvPath` instance. That makes exactly 0% of use cases harder. And actually, in many, many ways, even though you have a bit more terminology to remember, things get much easier because of the Frameworks opinionated way of working. {% endhint %} To load named paths groups you use the `PathsManager`'s `add_named_paths` method or one of its variants. In the CLI this breaks down into four steps: * Select `add named-paths` * Chose a way to identify the group of csvpaths: a csvpaths file, a directory, or a JSON file * If you are loading a csvpaths file, give the name you are going to use for the group * Locate your file on your hard drive You can also list your named-paths. When you do that you are looking at the names that you have given to groups of csvpaths, i.e. the named-paths names. Each named-paths directory has a `manifest.json` that describes the named-paths group. It has a `group.csvpath` file with one or more of your csvpaths aggregated into it. And, if you loaded your named-paths group from a JSON definition, a copy of the JSON file is stored in its named-paths directory. While changes to the named-paths group are tracked in the manifest, named-paths group files are not versioned in the inputs tree the way data files are. This is because we expect that as code they will primarily live in a revision control system, typically Git. Each csvpath from the group that you run is also captured in the run metadata along with its hash fingerprint, so there is no loss of identity, durability, or traceability from this approach, even if you don't use Git. ## Access the archive The CLI can list results and pop open the runs directory of a named-paths group. Results of runs are often called `named-results`. The name in named-results is the same name used for the named-paths group that generated the results. We use the named-paths name to identify results, rather than the name of the named-file, because the data in files can be used by multiple downstream users with different needs; whereas, each named-paths group is tailored for a particular use case. Named-results directories have run directories that are named for the date-time of the run. Inside each run directory is a `manifest.json` that describes the run and its results and a directory named for each csvpath in the named-paths group. Each csvpath directory (sometimes referred to as an instance or an identity) contains the files generated by running that csvpath: `data.csv`, `unmatched.csv`, `errors.json`, etc. ## Run named-paths groups Running a named-paths group against a named-file is easy. Doing a rewind/replay takes a bit more thought, of course. To do a regular run you just pick your named-file and named-paths groups as the CLI lists them. You then pick your run method. The run methods currently available are: * `collect` * `fast_forward` In reality there are six run methods available on a `CsvPaths` instance, not just two. The two options offered are actually: * `CsvPaths.collect_paths()` * `CsvPaths.fast_forward_paths()` Both are serial run methods. Serial runs walk through the csvpaths of a named-paths group one-by-one, each finishing before the next starts. The other run methods that are not supported in the CLI are: * `CsvPaths.next_paths()` * `CsvPaths.collect_by_line()` * `CsvPaths.fast_forward_by_line()` * `CsvPaths.next_by_line()` There is no significant effort required, beyond the on-going testing, to add `collect_by_line` and `collect_fast_forward`. If there is demand it will happen! Since only very specific use cases benefit substantially from the difference in serial vs. parallel runs we'll wait for requests. The `next_paths` and `next_by_line` methods are not supported because they are very much intended for programmatic use as generators. ## Do rewind/replay When you use the CLI to run named-paths groups against named-files you have the option to do a rewind/replay run. Rewind/replay is a way to rewind the named-paths group back by some number of csvpaths and replay from there with the same or updated data. | Scenarios | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |

Imagine you receive a CSV file of hospital procedures every month. It is typically 5 gigabytes. You run a 25-csvpath named-paths group on it. As the paths progress, the data shrinks down to a few hundred kilobytes. One month the data provider makes a mistake and 10 days after delivery sends a 500k supplemental file.

Consider two scenarios:

You realize you could substitute the new file into the process at the fifteenth csvpath in the group and save yourself a lot of processing time. You decide to rewind the process to the 15th csvpath rather than marking the first 14 csvpaths with run-mode: no-run, which would require a change to production, and potentially errors if you forgot to reset run-mode

— Or —

You realize that you made a mistake on the 12th csvpath in the named-paths group. You want to fix that csvpath and rerun the same data from there, skipping the first 11 csvpaths.

| Rewind/replay is particularly useful in cases where you are chaining csvpaths together, the output of one becoming the input of the next, using `source-mode: preceding`. A rewind/replay uses references, rather than just a named-file name and/or a named-paths name. The references are in the form: * For data: `$named-results-name.results.run-datetime.csvpath-identity` * For csvpaths: `$named-paths-name.csvpaths.csvpath-identity` There is more to it, of course. You can read about references and rewind/replay [here](/topics/how-tos/rewind-replay/replay-using-references), [here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-1), and [here](/topics/practical-stuff/the_reference_data_types) — and there is an example of doing rewind/replay specifically in the CLI [here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-2), including a video of actually doing it. In the CLI, running a rewind/replay is easy. When you select `run` you are presented with a list of named-files. You can type `$` and hit return to switch to building a file reference. (It doesn't matter what named-file the cursor is next to when you hit `$`). Likewise, when you are asked to pick a named-paths name you can hit `$` and return to build a cvspaths reference. In both cases, just follow the CLI prompts. ## Errors and debug settings Errors are typically presented in one of two ways: * Errors caught by a csvpath that is not configured to raise exceptions on errors simply print their error messages to the screen * Csvpaths that are set to raise exceptions on errors will show a simple statement that an error happened and allow you to type `e` and hit `enter` to show the stack trace (which is often a mess of information, and rightly so) You can also look in the log and in `errors.json` in the csvpath instance's results directory. If you're seeing to much or too little, you can set the debugging config options right within the CLI. You can also set the same options in the `config/config.ini` file. And you can override the config setting within an individual csvpath using `validation-mode`; however, setting `validation-mode` requires reloading your csvpath. To change the debug settings you can use the config selection in the top menu, or click `c` and `enter` when you are faced with an error message that offers that option.
The message and options you see when your csvpath file encounters an error, expected or not.
The config dialog that opens lets you change these settings: * Set logging in logs/csvpath.log to the `DEBUG` or `INFO` levels * Raising or suppressing exceptions on csvpath errors * Stopping or continuing on csvpath errors * Printing detailed validation error lines or bare errors messages without additional metadata
The debug config dialog
Error messages can be customized in the `[errors] pattern` key of `config/config.ini`. The fields are pretty straightforward. You can [read more here](/topics/practical-stuff/working-with-error-messages). ## List functions The last (for now!) feature of the CLI is functions documentation. On the main menu, select `functions` to see a list of all 150+ CsvPath Language functions. Selecting any one of them shows information like this:
A function definition giving call signature, qualifiers, a description of what the function does, etc.
Not all functions have this much information. Luckily [you can get even more detail from the GitHub function docs](https://github.com/csvpath/csvpath/blob/main/docs/functions.md). But regardless, the CLI information is very consistent, well-formatted, and quick to access. All of the functions have signatures and a focus, and all the `Type` functions (those that subclass `Type`) indicate that they can be used to create schemas using the `line()` function. Descriptions and argument names are still thin on the ground. Function look-ups is a new feature. Over time there will more information for each function. There are a few things to know: * CsvPath Language functions are match components. That means when they are evaluated they may have a role to play in deciding if a line is a match. The `Focus` line tells you if the function is mainly used to decide matches, calculate values, or do some side-effect that doesn't impact the data. (E.g. printing.) * Functions take match components as arguments. That means their signatures can be written in terms of the types of data they work on or the types of match components they use as data sources. The former is the `Data signatures` column and the latter is the `Call signatures` column. `Data signatures` are evaluated for validity on every line of a data file. `Call signatures` are validated one time when the csvpath begins. That means the data signatures are about the data file and the call signatures are about CsvPath Language correctness. * Some CsvPath Language match components are schema types. Schema types are used with the `line()` function to create structural schemas. CsvPath Language supports both structure validation and rules-based validation. Most csvpaths or named-paths groups benefit from including both structure definition and rules-based validation. Basically, you generally know what your file should look like and you typically know the business impact of the data it contains, and therefore the constraints. The schema types include: * `string()` * `decimal()` * `integer()` * `boolean()` * `date()` * `datetime()` * `email()` * `url()` * `blank()` * `wildcard()` * `none()` # Working With Error Messages The CsvPath Framework has rich and flexible built-in error handling. Let's look at how you can use it in the CLI. For this how-to, create a CsvPath project called `title_fix`. *(We're not going to actually fix titles in this example, that's just a handy example project).* Using Poetry, that would be something like: ```sh poetry new title_fix cd title_fix poetry add csvpath ``` Drop these files in an `assets` directory (or wherever you like). {% file src="/files/R6jDsx2A2SZplMLy85Qh" %} {% file src="/files/kQN4TJZEAEtnxmsMrk0n" %} If you haven't seen this data file before, it is a small cut of a public dataset of books checked out in the Seattle library system. When your project is ready, fire up the CLI with `poetry run cli`. When you do that CsvPath will create directories for config, logs, etc. We don't need to work on them yet, though. First, add the named-file:

Our data is a single file so just select the file option

Next, add the named-paths group:

Select file and navigate to your csvpath file

Now we're ready to run. At the top menu, click `run`.

I happen to have two csvpaths. You'll just see the one you added a moment ago.

Either method works, but let's go with collect

And we're off and running. This is where it gets interesting. Your results should look like this:
What you are seeing is five errors. The left part of each line is a set of information intended to tell you exactly where the problem happened. The last part on the right is the error message. In this case, numbers in brackets were rightly deemed to be invalid integers. The amount of detail you see here is intended to help you, as a csvpath writer, diagnose a problem brought to you by an ops team member after an error was observed in production. As you may know, there is far more information available in the metadata captured by the CsvPath Framework — all so you can answer the question: *what just happened?* And then do something about it. Let's break down a line of what you're seeing: ```log 2025-02-06 23h45m41s-835847:title_fix:4:title_fix_schema:0:checkout.integer[11]: Cannot convert [2019] to int ``` These are the fields: * time: `2025-02-06 23h45m41s-835847` * named-file: `title_fix` * line: `4` * named-paths: `title_fix_schema` * chain: `0:checkout.integer[11]` * message: `Cannot convert [2019] to int` The `chain` field may be new, let me explain. This field points uniquely to the match component that was the source of the error. In this case `0` is the index of the component. The 0th match component in our csvpath is `line.checkout()` . The `line()` function is how we define schema entities. This entity is called `checkout`. Within checkout we have 12 fields, starting with these three strings: ```python string.notnone(#UsageClass), string(#CheckoutType), string.notnone(#MaterialType), ... ``` The `integer[11]` part of the chain says that we should look at the function in position 11 (the 12th function within `line()`, because 0-based). That integer on line `4` of the data file is the source of the validation error. Admitedly this would be easiler to read if we named our functions better. So let's quickly do that in the csvpath:

Our integer is now a year

The result of running the modified csvpath is a nicer error message: ```log 2025-02-07 00h00m57s-958206:title_fix:4:title_fix_schema:0:checkout.year: Cannot convert [2019] to int ``` It's easy to see that the 0th match component is an entity called `checkout` and on line `4` its year was invalid. Not bad. But can we do better? Maybe we don't need all this information right at the moment. There are two directions to go at this point. On the one hand, we can cut out the extra info by switching it off. Or, on the other hand, we can streamline things by defining a simpler pattern. Let's do both, in order. At the CLI's top menu select `config`. This opens a dialog that allows you to tweak a few config options that are most useful for debugging.
The CsvPath Framework separates some config options into a `CsvPath` instances setting vs. a `CsvPaths` instance setting. When you use the CLI you always have a `CsvPaths` instance that manages one `CsvPath` instance per csvpath expression in a named-paths group. When you use this dialog you will be setting both the parent `CsvPaths` and its `CsvPath` children to behave the same way. When you step away from the CLI and work programmatically you can be more specific, if needed. The option to split the config makes a difference to ops because the system that runs validations has different error reporting needs from the validations themselves. That's a whole other interesting conversation. In this page we're all about the last option in the dialog: `Print detailed errors`. As you can imagine, this is where we can choose to not see all those fields we talked about above. First, however, a more general word about debugging. ## Be careful what you ask for One of the challenges with CsvPath Framework and CsvPath Language is their flexibility. There's generally a few ways to attack a problem. That means you have to be careful to think through what you're seeing when you debug. This dialog is a case in point. If you set `Raise exceptions`, your runs will stop at the first problem encountered. Likewise, if you select `Stop on errors` the Framework will stop when it runs into an issue; it won't, however, throw an exception — again, the difference is an operational concern. If you want to see all your errors at once you need to suppress exceptions and not stop at errors. But keep in mind, it is possible to halt on an error, or on the use of `stop()`, without there being an error message. Likewise, it is possible to suppress exceptions and then not realize you encountered them. All of this gets even more fun when you remember that a csvpath writer can override the Framework's config settings on a csvpath-by-csvpath basis using [the modes](/topics/practical-stuff/the-modes). The reason the modes exist is so that ops teams can set a standard config that csvpath writers can override during development or because they have more specific requirements and/or greater knowledge of the data. All this flexibility is there for important operational reasons. You just have to be mindful of it. ## Back to error messages! Uncheck `Print detailed errors`, if it is selected. When you hit `Ok` (use `tab` or the mouse) you are setting a key in the errors section of `config/config.ini`: `use_format`. `use_format` is either `full` or `bare`. What we saw above was `full`. Now you've set CsvPath Framework to report errors as leanly as possible:
Nice and clean, right? But you don't see a line number. A line number may be useful information for larger files than our example data. We can add that back in by turning detailed errors back on but also customizing the message pattern. Go ahead and open the config window and reset `Print detaled errors`. To set the new pattern we have to exit the CLI. The CLI is constantly improving, but at this time it does not offer a way to change the `[errors] pattern` key. No matter, editing `config/config.ini` is painless. Open it and look near the top for the `[errors]` section. If you don't see a `pattern` key you can add one. (It should be there in the auto-generated `config.ini`, but if you are using an older CsvPath Framework install you may need to add the key yourself.)
Create a pattern like the one shown above: `{file}:{line}:{chain}: {message}`. Then save `config.ini` and restart your CLI. Now when you run the `title_fix` named-file against the `title_fix_schema` named-paths you will get a less cluttered set of messages with just the information you need to start debugging your data and/or csvpaths.
One last call out. If you haven't tried creating rules-based error messages using the `error()` function you should. The `error()` function allows you to generate error messages that are co-equal to the built-in validation errors CsvPath Language provides. That means that when you do something like: ```python not( #PublicationYear ) -> error("You must provide a publication year") ``` Your error message will be available with the same fields as the built-in error would have — or none, if you turn the details off. And your error will be generated into the `errors.json` file that captures all of a run's errors in a machine-friendly format. Pretty cool, right? # The Modes In the context of a `CsvPaths` instance's run, an individual `CsvPath` instance can operate in several possible modes that allow you to configure its behavior without resorting to the global `config.ini` or applying settings programmatically. In particular, the modes help you configure groups of csvpaths more flexibly. You can use them to easily disable individual csvpaths or configure them differently than other csvpaths in the same named-paths group. Modes are set in your csvpath's comments. The modes are: * [`error-mode`](#error-mode): \[`bare` / `full`] * [`explain-mode`](#explain-mode)`:` \[`explain` / `no-explain`] * [`files-mode`](#files-mode)`:` *(all or any combination of)* * `all` * `data` / `no-data` * `unmatched` / `no-unmatched` * `printouts` / `no-printouts` * [`logic-mode`](#logic-mode)`:` \[`AND` / `OR`] * [`print-mode`](#print-mode)`:` \[`default` / `no-default`/`separate`/`no-default-separate`] * [`return-mode`](#return-mode)`:` \[`matches` / `no-matches`] * [`run-mode`](#run-mode)`:` \[`run` / `no-run`] * [`source-mode`](#source-mode)`:` `preceding` * [`transfer-mode`](#transfer-mode)`:` `data` / `unmatched` `>` `var-name` * [`unmatched-mode`](#unmatched-mode)`:` \[`keep` / `no-keep`] * [`validation-mode`](#validation-mode)`:` *(any combination of)* * `print` / `no-print` * `raise` / `no-raise` * `stop` / `no-stop` * `fail` / `no-fail` * `collect` / `no-collect` * `match` / `no-match` Modes are only set in *external* comments. External comments are comments that are outside the csvpath, above or below it. External comments can also have other user-defined metadata and plain text mixed in with mode settings. If a mode setting is followed by plain text there must be a stand-alone colon between the mode and the text. ## Defaults When a mode is not explicitly set CsvPath uses sensible defaults. Some modes default to options set in `config/config.ini`. For example, `validation-mode` overrides `[errors] csvpath` in `config.ini`. ([Read here for more about the config file](/topics/how-tos/config.ini).) Other defaults are built-in, for instance, `logic-mode` overrides the library's built-in default matching using ANDed operations. The defaults are: * `error-mode`: defaults to `bare`, meaning `error()` and built-in errors are presented minimally * `explain-mode`: no explanations are logged when logging is set to `INFO` * `files-mode`: there is no check for optional files having been generated * `logic-mode`: match components are ANDed * `print-mode`: print statements go to the console * `return-mode`: matches are returned * `run-mode`: the csvpath is run * `source-mode`: the named-file that was passed to the named-paths group is used as input * `transfer-mode`: no result data transfer is made * `unmatched-mode`: the lines not returned are discarded * `validation-mode`: validation errors are only printed and logged ## An Example These settings are configured like in this example of two trivial csvpaths in a named-paths group called `example`: ```bash ~ id: hello_world run-mode: no-run ~ $[*][ yes() ] ---- CSVPATH ---- ~ id: next please! explain-mode: explain validation-mode: no-raise, print logic-mode: OR return-mode: matches unmatched-mode: keep print-mode: default : All of these mode settings are optional, of course! And they don't have to be written as neatly as this, either. ~ $[*][ import($example.csvpaths.hello_world) yes() ] ``` `hello_world` will not be run when the named-paths group runs, but it will be imported into the second csvpath identified as `next please!`. This example doesn't do much, but it gives an idea of how you can easily configure individual csvpaths within a group that will be run as a single unit. As you can see, some modes can take multiple values separated by commas. ## Detailed Descriptions ### Run Mode
Setting
no-runThe csvpath will not be run on its own. It only runs as an import into another csvpath that is runnable.
runRun is the default.
### Validation Mode Validation mode controls how the `CsvPath` instance reacts to built-in validation errors. Built-in validation errors have two types: * Problems with the csvpath's syntax or structure * Problems with the data being validated
Setting
raiseThe setting raise indicates that when a validation problem occurs, an exception should be raised that will likely halt the program. The opposite is no-raise. Setting neither value defaults the decision back to the global config.ini setting.
printThe print setting makes the CsvPath instance print validation messages to all configured Printer instances. The opposite is no-print.
stopThe stop mode setting makes the CsvPath instance stop as soon as a validation problem occurs. no-stop prevents this premature completion, enabling the CsvPath instance to alert and continue.
failThe fail setting sets the csvpath being run to invalid. Effectively this means setting the CsvPath instance's is_valid property to False. The opposite setting is no-fail. Failing has no effect on the program or the validation run continuing.
matchWhen match is set a built-in validation error will match, rather than fail to match. The thing to remember is that this setting applies to errors in the data (e.g. adding "five", not 5) only. Errors in the CsvPath Language are still not allowed. As a practical example add("five", 5) never works, but add(@five, 5) always does because even if @five turns out to not be a number on a particular line we still match on it in accordance with this setting. Regardless of if you set match or not, if you don't have no-raise, your csvpath will blow-up on validation errors.
collectWhen collect is set errors are captured. When no-collect is set they are dropped. You can drop errors and still fail a file to make it invalid; just as you can capture errors but choose to not use fail(). Keep in mind that when you don't collect errors CsvPath.has_errors() is False. Also bear in mind that if you are using the OpenTelemetry integration (e.g. to push events to Grafana, New Relic, etc.) you can choose to drop errors but still fire error events.
### Logic Mode
ANDAND is the default logic mode. It requires that all match components evaluate to True for a line to match.
OROR mode is similar to how the or() function works. Any match component that evaluates to true makes the line match.
### Return Mode
Setting
matchesAll the matching lines will be returned by next() or collect(). (fast_forward() never returns lines, regardless of mode). This is the default behavior.
no-matchesAll the lines that fail to match will be returned.
### Print Mode CsvPath supports printing errors and user-defined messages to any number of `Printer` objects using the `print()` and `error()` functions. Printers send text to separate queues. By default a "standard out" printer is enabled that prints to the console, as well as to a file. If you don't want anything printed to the console you would set `no-default`.
Setting
defaultWhen default is set the CsvPath instance prints to the console, as well as any other Printer instances you configure. Built-in validation errors always go to the default printstream.
no-defaultWhen no-default is set the standard console printer is disabled.
separateDefault output goes to default.txt. Other printstreams go to text files named by the stream name. e.g. print("hello world", "greetings") will land in greetings.txt.
no-default-separateStandard out printing is disabled. Printing still goes to files. Specific printstreams that the csvpath writer may use get their own files. In this case print("hello world", "greetings") will still land in greetings.txt, but the built-in error resulting from add("five", none()) would not be printed.
### Explain Mode
Setting
explainWhen set a step-by-step explanation of the values, assignments, match, etc. are dumped to INFO for each line in the file being processed. This can be a good aid to debugging but is performance expensive. The hit can be around 20-25%.
no-explainno-explain is the default.
### Unmatched Mode
Setting
keepReturn mode determines if matches or non-matches are returned. Unmatched mode determines if the non-returned lines are kept available in the Result instance or on the CsvPath instance. If the lines are kept and you are using a CsvPaths instance, the Result instance will be serialized to the archive directory and you will see an unmatched.csv file containing the lines.
no-keepNo lines that were not returned are kept.
### Files Mode The impact of `files-mode` is that the run instance manifest and the csvpath's manifest will show that files were created as expected, or not. There are various reasons why printouts.txt, data.csv and unmatched.csv might not be generated. For e.g., if we expect no validation output from user-created `print()` statements or built-in validation error messages we might set the `files-mode` to `no-printouts`. If a validation error was then printed we would be alerted in the metadata. In another example, if we set `unmatched-mode` to `no-keep` (the default) and `files-mode` to `unmatched` we have a conflict that we'll be alerted to in the metadata. Similarly, if we set `files-mode` to `data` and then run `fast_forward_paths()` we will not get `data.csv` files and the metadata will alert us to the mismatch. `errors.json`, `vars.json`, `meta.json`, and `manifest.json` are always generated, regardless of `files-mode`. When you set `files-mode` to `all` the CsvPath Library will double-check that meta, vars, errors were correctly created, but that part of its checking is superfluous.
Setting
allAll file types are expected to be generated
data / no-dataDetermines if the data.csv file is expected
unmatched / no-unmatchedDetermines if the unmatched.csv file is expected
printouts / no-printoutsDetermines if we expect anything to be sent to the Printer instances using print()
### Source Mode Usually the data for a csvpath in a named-paths group comes from the data input for the whole group. I.e., all the csvpaths in the group run against the same source file. However, in some cases you might want the input to a csvpath to be the csvpath preceding it. Meaning that the results captured from the first csvpath are piped into the second. To do this, you set `source-mode: preceding` on the second csvpath. Keep in mind that `CsvPaths` instances' `_collects` methods and `_by_line` methods are [quite different in how they handle data sources](/topics/practical-stuff/serial-or-breadth-first-runs). Source mode does not apply to by-lines runs—i.e. it is for linear, not breadth-first runs—because in a by-lines run each line is passed through each of the csvpaths in the named-paths group before the next line is considered. Csvpaths in a by-lines run can change data for downstream csvpaths in their named-paths group, and they can skip or advance the run in order to filter data so that downstream csvpaths don't have a chance at it. This just means that there are multiple ways of allowing earlier csvpaths to have an effect on later csvpaths. [Source mode has a lot to do with rewind/replay](/topics/how-tos/rewind-replay/doing-rewind-replay-part-1), also [references between data sets](/topics/how-tos/rewind-replay/replay-using-references), as well as [strategies for validation and canonicalization](/topics/higher-level-topics/validation/validation_strategies). | Setting | | | ----------- | ----------------------------------------------------------------------------------------------------------- | | `preceding` | Instructs the csvpath to use the output of the preceding csvpath in the named-paths group as its input data | ### Transfer Mode `transfer-mode` let's you copy `data.csv` or `unmatched.csv` to an arbitrary location in the `transfers` directory. The `transfers` directory is configured in `config/config.ini` under `[results] transfers`. To use `transfer-mode` you use the form `data` | `unmatched` `>` *var-name* where *var-name* is the name of a variable that will be the relative path under the `transfer` directory to the data you are transferring. Note that `transfer-mode` has no effect on the original data, in keeping with CsvPath Library's copy-on-write semantics. You may have as many transfers as you like by separating them with commas. [Read more about using transfer-mode here](/topics/how-tos/transfers/file-system-transfers).
Setting
data > var-nameIndicates you are transferring data.csv to the value of var-name as a relative path within the transfer directory
unmatched > var-nameIndicates unmatched.csv to the value of var-name
### Error Mode `error-mode` allows you to output errors with log-like information or as plain plain messages.
Setting
bareErrors are output as simple strings
full

Errors are output according to the [errors] pattern config value using the following fields:

  • time: Time
  • file: Named-file name
  • line: Line number
  • paths: Named-paths name
  • instance: Csvpath instance ID/name
  • chain: Match component chain
  • message: Message

The default pattern is:

{time}:{file}:{line}:{paths}:{instance}:{chain}: {message}

The chain field gives the parent-child relationships from the top match component to the match component child that was the source of the error.

# The Reference Data Types CsvPath uses a namespace-like path to point to data in various places. These are called references. References are integrated into the match components, print output, and the structure of a csvpath. If you want to do lookups from one csvpath to the results or metadata of another, you use a reference. When you need to print data from the `print()` function, you need references. * [The Parts Of a Reference](#the-parts-of-a-reference) * [Seven Types Of Data](#four-types-of-data) * [The Csvpath Runtime Fields](#the-csvpath-runtime-fields) * [The Metadata Fields](#the-metadata-fields) ## The Parts Of a Reference A reference has this structure: ```perl $paths-name.data-type.name.child ``` Let's break this down a bit more.
PartDescriptionExample
$The root of the csvpath
paths-nameThe name of a group of csvpaths or a named-file. This is referred to as a named-paths name or a named-file name. In print() statements the name can be empty to indicate the currently active csvpath the reference is in.
  • $test.csv[*][yes()]
  • $mypaths.variables.my_variable
  • $.variables.my_variable
type-of-data
  • csvpath
  • csvpaths
  • files
  • headers
  • metadata
  • results
  • variables
$mypaths.metadata.description
name-of-data-itemAny name. In the case of headers the name can be quoted or can be the index of the header. In the csvpaths type the name is the identity of a specific csvpath within the named-paths group.
  • $mypaths.headers."my header"
  • $mypaths.headers.0
tracking value nameThis is called a tracking value. Tracking values are keys in dict variables. In the case of references they can also be an index into a stack() variable.
  • $mypaths.variables.cities.Boston
## Seven Types Of Data The seven data types are always the second component of a reference. Their position in the reference is: `$root.`**`datatype`**`.name.name`. The types are pretty simple. * `csvpath` is either runtime data about the current csvpath or it is post-run residual data about another named-paths group the reference is pointing to * `csvpaths` is the namespace for the identities of the individual csvpaths in the named-paths group. * `files` indicates that the reference is [a pointer to a named-file version as described here](/topics/how-tos/rewind-replay/referring-to-named-file-versions) * `headers` are headers. The header names and indexes are available post-run. The data associated with the headers, line-by-line, may be available or not, depending on if the run method captured data. At this time CsvPath doesn't offer a way for a reference to point to a header value in an individual row. * `metadata` is descriptive data about the csvpath the reference is pointing to * `results` references point to the data.csv intermediate results of the csvpaths in a named-paths group. Each csvpath's `data.csv` is automatically collected (unless configured not to) and positioned in a standard location so that it can be referenced and piped into other csvpaths. You can [read more about results references here](/topics/how-tos/rewind-replay/doing-rewind-replay-part-1). * `variables` are variables. Variables from completed runs are available from the CsvPath that the reference points to. We only lose the variables when the Python instance shuts down. ## The Csvpath Runtime Fields The `csvpath` data type's fields include: * `stopped` — `True` if the csvpath stopped the CsvPath from processing using the `stop()` function. Stopping a CsvPath that is run by a CsvPaths instance does not affect any other CsvPath instances that the parent CsvPaths is also running. * `failed` — `True` if the csvpath failed the CSV file using the `fail()` function. A CsvPath instance that enters the failed state continues to process lines until the end of the CSV file or until the csvpath stops the run by calling the `stop()` function. * `delimiter` — the CSV file delimiter. By default a "," * `quotechar` — the CSV file character used to quote header names and values * The parts of the csvpath as their original text strings: * `scan_part` — something like `$myfile[1-10+20-30]` * `match_part` — something like `[concat("validation", "is", "good")]` * The counts of lines, total lines, matches, and scans * `count_matches` — the 1-based count (all counts are 1-based) of the matches that have happened so far in the scan * `count_lines` — the 1-based count of the number of lines seen. This is also referred to as data lines because by default CsvPath skips blanks. You may also see references to "physical" lines. Physical lines means the number of line feeds in the file, regardless of if they create blank lines. * `count_scans` — the 1-based count of lines seen by the scan so far. If the scan is for 1+3+5 and CsvPaths is at line 3 the count will be 2. * `total_lines` — the 0-based count of all the physical lines in the file. This number was found before the first line is scanned. * The validation failed and run stopped properties * Basic timing: * `line_time` — the cumulative time processing lines so far * `last_line_time` — the time spent processing the line before the current line * `headers` — a string created from the currently set headers. This is largely for debugging. Keep in mind that the headers can be reset on demand using the `reset_headers()` function. Resetting headers is fairly common due to the irregular way CSV files are often constructed. ## The Metadata Fields The `metadata` fields come from the comments around a csvpath and from the CsvPath files, paths, and results managers. Metadata's most important contribution is the identity of a csvpath. You set the identity of a csvpath by adding an ID or name field to a comment above or below the csvpath. The ID can be like: `id: my id` `ID: my id` `Id: my id` All three forms will be recognized. If not found, the same forms of the metadata key `name` are looked for. The identity is used for importing csvpaths using `import()`. It is also used by header references and for traceability in validation printouts and logging. The other metadata coming from the managers includes: * `paths_name` — the named-paths name * `file_name` — the named-file name * `data_lines` — the count of total data lines. This is a 1-based count of all lines with data. It is set before the first line. * `csvpaths_applied` — the number of csvpaths that will be applied to the CSV file, all keyed under paths\_name * `csvpaths_completed` — the number of csvpaths completed to that point. This number is static after a run is complete. At that point csvpaths\_completed may not equal csvpaths\_applied if there are csvpaths that were stopped by the csvpath itself using the `stop()` function. * `valid` — tells us if the file is considered valid according to all the paths applied so far. Much of the information above is available less conveniently from other sources. More importantly, csvpath comments can provide user defined keyed-metadata values. These are similar to tags in AWS, GCP, and Azure. Using metadata fields in your comments can be a huge win for the long-term maintainability of large csvpath collections. Keys take the form of a word with a colon at the end. For example: ```clike ~ name: Order Batch File Valdiations description: The orders file arrives nightly between 1 - 3 a.m. ~ ``` This comment would result in two entries in the metadata collection. One for the `name` and the other under the key `description`. You can add a colon to end a metadata field without starting a new one, like this: ``` ~ name: header reset import description: this csvpath is used to reset the headers if they change : more testing is needed ~ ``` In that example there are two metadata fields: `name` and `description`. The additional comment, `more testing is needed`, is not captured in the metadata fields because the `description` field was closed with a following colon and no new field was started. In this way, `name` and `description` are machine-readable, while `more testing is needed` is only for humans. # Manifests and Metadata The CsvPath Library is all about the structure you need to trust the data you process As you may have read in [File Management ](/topics/higher-level-topics/data-validation-files-and-storage/file-management)and [Where Do I Find Results](/topics/higher-level-topics/data-validation-files-and-storage/where-do-i-find-results), the CsvPath Library generates a lot of metadata. The goal is to provide a high-trust environment to do Collect, Store, Validate Pattern processing. When you are dealing with delimited data in file-based data flows you have the potential for control problems due to both low-structure data and low-structure data flow. The Collect, Store, Validate pattern captures complete lineage and action records and limits the degrees of flexibility in the overall process architecture to counteract the risks and enable fast remediation. Let's look at where the data is. The CsvPath Library keeps files in: * `inputs/named_paths` for CsvPath Language validation files * `inputs/named_files` for source data files * `archive` for results of running named-paths against named-files Each area has its own strategy for files and data management. At the highest level, the common feature is a `manifest.json` file. ## inputs/named\_paths Named-paths are captured in a single file that contains all the csvpaths that you create for the same group, regardless of if you put them in one file, a single directory, itemize them in a JSON file, or pass them in programmatically as a list of csvpath strings. The single file is always named `group.csvpaths`. Named-paths also have up to two JSON files a `manifest.json` and a copy of any JSON file that identified the csvpath statement members of the group. The latter is always named `definition.json`, regardless of what the original JSON file was named. `definition.json` includes the entire original JSON file contents, not just the definition of that named-paths group. If you didn't use a JSON file to create the named-paths group there will naturally be no `definition.json`. manifest.json is where we get into controls. The CsvPath Library expects CsvPath Language controls from two directions: * Before you add your CsvPath Language files you manage them in a revision control system of some kind. Git is the standard-bearer. You don't have to follow this practice, but the Library assumes that you do. * After you add a file containing one or more csvpaths, the Library tracks changes to the content of the named-paths group in the manifest.json. The named-paths manifest is the simplest of the manifest types used by the Library. It contains: * **`file`**: the full path to your `group.csvpaths` file * **`fingerprint`**: a [SHA 256](https://en.wikipedia.org/wiki/SHA-2) hash of the content of `group.csvpath` at the time it was last added or updated giving a unique exact ID to the content of the file * **`time`**: the timestamp of the most recent add or update of `group.csvpath` We don't need a lot more information in this file area. The version control you do with your CsvPath Language files gives you version security. The metadata the Library captures when you run a named-paths group gives you the content of the action-based change. All that manifest.json needs to do for the named-paths group is allow you to connect those dots so that you can go from: * results of executed validation statements at a point in time * to the version IDs registered with the Library at a point in time * to the versions in your version control system
Keep in mind that you also have `logs/csvpath.log`. The log is on `WARN` by default, but you can get lots more information by putting it on `INFO` or even `DEBUG`. Experiment with that setting in [config/config.ini](/topics/how-tos/config.ini). And, of course, you have control of if the Library and CsvPath Language team up to complete your run, or stop early, using the `validation-mode` [settings in your csvpath](/topics/practical-stuff/the-modes) statements and error policies in `config.ini`. ## inputs/named\_files There's more going on in the named-files metadata and control structures. Named-files are stored in directories under `inputs/named_files`. Each directory name is the name of the file it contains. Each directory has a directory within named for the original file. And that directory has one or more files named by the SHA 256 hash of the contents of the original file each time it changes. The named-file directory also contains a `manifest.json`. In the case of named-files, the CsvPath Library doesn't make assumptions about any external version control system. Instead it captures each version of the source file you present to it permanently, tracking it with that hash. While a system like Git is much more sophisticated, the Collect, Store, Validate pattern doesn't really require all the things Git can do. It mainly requires a way to identify versions, trace where they are used, and inspect them or reuse them when needed. Named-file manifests are a list of states of the file they track. Each state has this information: * **`type`**: the file type, as identified by the extension * **`file`**: the file path to the version of the file at this point in time * **`fingerprint`**: the SHA 256 has of the contents of the file at this point in time * **`time`**: the time the version of the file was registered with the CsvPaths Library * **`from`**: the file system location of the version that was copied into the CsvPaths Library under this name There are a couple of things to remember. * CsvPath Library checks if the fingerprint of the most recent version is the same as the fingerprint of the content about to be run. If those fingerprints differ, and if `config/config.ini`'s `[inputs]` section has a `on_unmatched_file_fingerprints = halt` (which is the default) the Library will throw an exception. This is so that people don't make changes to the files that have been registered with the Library. * A reference to a named-file looks to the results of another named-paths run. Read [more about how that works here](/topics/how-tos/rewind-replay/replay-using-references). The point here is that when you do that you are using a file that has no fingerprint or bytes in the named-files area. This doesn't completely eliminate your ability to trace down how a result came to be. But it does may the linage path very different and tracing have a few more steps. * `source-mode: prededing` will also result in fingerprint mismatches. Recall that `source-mode` determines if your csvpaths all work off the same file, or if they work off the file resulting from the csvpath preceding them. When `source-mode` is `preceding` the data input is `data.csv` from the path directly prior, not the named-file; therefore, there is no fingerprint to match. And again, controls are still robust, but it takes a bit more effort to trace because of the multiple locations. ## archive The `archive` holds what we call named-results. A named-result is a set of results named for its named-paths group. Your results are called by the same name as the group of scripts that created them. The `archive` directory has: * One `manifest.json` for all results in the archive * In each named-results directory there are time-stamped run directories, each containing a `manifest.json` * Within each run directory there is a directory for each csvpath in the named-paths group, each with files of data results, data exhaust, report output, and metadata files, including a `manifest.json` First the top-level `manifest.json`. The file is a flat list of runs of individual csvpaths by the CsvPaths Library in the order they happened without grouping. Each csvpath is run by a `CsvPath` instance that is managed by a single `CsvPaths` instance. The run bookeeping is sequential in run-by-run order and, within runs, cvspath-by-csvpath. Each looks like: ```json { "file_path": "inputs/named_files/accounts/Washington_State_Certified_Public_Accountants.csv/f936d73efab8e94554eae80020b328a8924482536969c513e541412a9d080435.csv", "file_size": 4918741, "file_last_change": "Thu Nov 21 16:22:47 2024", "fingerprint_provided": "f936d73efab8e94554eae80020b328a8924482536969c513e541412a9d080435", "fingerprint_found": "f936d73efab8e94554eae80020b328a8924482536969c513e541412a9d080435", "time": "2024-11-21 16:22:47.478917", "target": "archive/autogen5/2024-11-21_04-22-47", "identity": "General data integrity checks" }, ``` The results metadata in `manifest.json` is entered at the beginning of the run. The contents of the named-result instance files are spooled out as the run happens or written at the end. A run instance is a directory under the named-results that has a date stamp name like `2024-11-21_04-26-41`. Each time-stamped directory contains the results of a single run of the named-paths group. The timestamp is, of course, an important piece of metadata. Beyond that, there's a lot more. Directly within the run directory there is a manifest.json that gives: * **`all_completed`**: `true` if all `CsvPath` instances finished running their delimited data file through their csvpath * **`all_valid`**: `true` if all `CsvPath` instances report that they ended up in the valid state * **`error_count`**: a count of all the errors collected by the `CsvPath` instances involved in the run. Note that this error count doesn't count any `CsvPaths` instance errors that might happen during setup or tear-down of the run. * **`all_expected_files`**: `true` if all files expected to be generated according to the `file-mode` setting (or default when there is no explicit `file-mode` setting) were in fact generated * **`time`**: the time the manifest was generated Within each instance directory there are directories named for the individual csvpath scripts in the named-paths group. When you run a csvpath using the CsvPath Library it has an identity. If you use a CsvPath Language comment to give your csvpath a `name` or `id`, that is its identity. Otherwise, the identity is the csvpath's index in the run sequence.
The files included in the named results instance directory are: * **`data.csv`**: the data generated by the csvpath. This can be matched lines (typically) or the unmatched lines * **`unmatched.csv`**: optionally, whichever set of lines is not captured in `data.csv` may be captured in this file * **`meta.json`**: the metadata from the runtime `CsvPath` instance, along with any user-defined metadata and comments * **`errors.json`**: the output collected at the point of any exceptions, regardless of if they are raised or suppressed * **`printouts.txt`**: the output of printers, with each `Printer` instance having its own segment of the file * **`vars.json`**: the variables created by this csvpath * **`manifest.json`**: the summary report of the csvpath's outcome data.csv, unmatched.csv, and printouts.txt may be absent if their contents was not generated. The others are created even if they are empty. The theory is that errors, variables, etc. are sufficiently interesting even when there aren't any that we should see an empty json array or dictionary. The manifest isn't large, but it has some key data. It looks like this:
There are three important summations. `completed`, `files_expected`, and `file_fingerprints` are unique to this file. `valid` and `time` are available elsewhere as well. * **`completed`**: this boolean indicates if the data file was fully considered, or if some lines were not seen due to an error or early stopping. You can calculate this value from line counts in meta.json, but this is the more authoritative value because there are several types of line counts (1-based, 0-based, scans, physical, etc.) in meta.json that might lead you astray. * **`files_expected`**: the file-mode setting allows you to specify what files you expect to be generated. Valid choices are `all` for all files expected, `data` or `no-data`, `unmatched` or `no-unmatched`, `printouts` or `no-printouts`, or blank for any of these files not of concern one way or the other * **`file_fingerprints`**: these are the SHA 256 hashes of the contents of the generated files. You can verify that the files haven't be changed at any time by regenerating the hashes and comparing to these values. Now, our purpose here is mainly the metadata that helps you control your data operations. The core of that is in `meta.json`. Here is a typical `meta.json`:
You can see several threads you could trace back upstream: * `paths_name` is a pointer to the named-paths group's home directory, in this case: `inputs/named_paths/autogen5` * Likewise, `file_name` is a pointer to `inputs/named_files/accounts` * `run_time` is a precise timestamp for the run. This can be cross checked against the `manifest.json` timestamps in `inputs/named_files`, `inputs/named_paths`, and `archive`. * In the user-defined metadata you can see a `NAME` and an `id`. `NAME` is the least of the six possible identity keys (`id`, `Id`, `ID`, `name`, `Name`, `NAME`), so it always holds the csvpath's index as a fallback. This csvpath was the first to run, so `0`. The `id` holds the user-defined `id` key that you can see embedded in the `original_comment` key. The identity can help you check that you are looking at the right csvpath. In addition, `errors.json`, the log, and the built-in validation output (e.g. the human-friendly error message you would see if you were to try to use `"five"` as a number) contain the identity to help you track down the source of what you are seeing. * Lower down `file_name` is the fully qualified path to the registered named-file version used for the run. Or, in the case of a `source-mode: preceding` or use of a reference, to the actual data file used in the run, wherever in the `archive` it was found. * The `scan_part` and `match_part` are the two halves of the CsvPath Language statement that we're running here. These tell you exactly what was run. With the date stamp here and the date stamp and fingerprint in `inputs/named_paths` you should be able to verify that your csvpath file's content was what you expected, given the state of the files you imported into the CsvPath Library. There is more below the fold, but that gives you a good starting idea of how the data fits together. When you are working in `source-mode: preceding` or using references you have more steps. But the basics remain the same: look at the identifiers in `meta.json` and work your way backward, whether through another named-result or directly back to `inputs/named_files` and `inputs/named_paths`. # Serial Or Breadth-first Runs? Using CsvPaths, you have a choice to run multiple csvpaths against a file serially or line-by-line. What is the difference? Why would we choose one over the other? ## The methods On CsvPaths look to these methods for serial runs: * `collect_paths()` * `fast_forward_paths()` * `next_paths()` Using these methods, CsvPaths runs a csvpath through every line in the CSV file before it moves to the next csvpath and restarts the file from the first line. For "breadth-first" runs, look for these CsvPaths methods. They have every csvpath in the run examine each line before CsvPaths moves forward to the next line: * `collect_by_line()` * `fast_forward_by_line()` * `next_by_line()` ## How to choose Usually, top-to-bottom serially vs side-to-side breadth-first is not a big decision. In the usual case it just doesn't matter. However, as your use of CsvPath expands and your needs grow there are reasons why sometimes the choice becomes important. A breadth-first run has as its most important consideration that it allows one csvpath to modify the inputs to the next csvpath. This is because all the csvpaths are working on the same data in memory. A a simple example, this output came from a property inventory CSV made available by the City Of Boston. The top run is a serial run of two csvpaths. The bottom run is a parallel run of the same two csvpaths. Notice the change in capitalization in the #4 and #6 headers in the bottom, parallel run:
Here is the Python code that generated those results: {% code lineNumbers="true" %} ```python from csvpath import CsvPaths paths = CsvPaths() paths.files_manager.add_named_file( name="property", path="csvs/fy2024-property-assessment-data_1_5_2024.csv") paths.paths_manager.add_named_paths_from_file( file_path="csvpaths/downstream/property.csvpath", name="downstream") paths.paths_manager.add_named_paths_from_file( file_path="csvpaths/downstream/property2.csvpath", name="downstream") print("\n============= ONE: paths ================") paths.fast_forward_paths(pathsname="downstream", filename="property") print("\n============= TWO: by line ================") paths.fast_forward_by_line(pathsname="downstream", filename="property") ``` {% endcode %} * Line 3: we create our `CsvPaths` runner * Line 4: pick a CSV file * Lines 6 and 9: there are two simple csvpaths that we will run in serial and in parallel * Line 14: the serial run * Line 17: the breadth-first run And here are the two cvspaths. The top, first csvpath: {% code lineNumbers="true" %} ```xquery $[10][ row_table(4,6) replace("CITY", lower(#CITY)) replace(4, upper(#4)) ] ``` {% endcode %} And the second, bottom csvpath: ```xquery $[10][ row_table(4, 6) ] ``` The file is >80 Mb but we're only looking at line 10 and the match components are simple so this is a quick test. And the file manager caches some metadata so that allow fast iterations. Together the csvpaths print out the tables in the screenshot above. You can see that in lines 3 and 4 of the first csvpath we changed the city and street name headers by lower-casing and upper-casing them. In the serial execution that had no effect on the second csvpath. Whereas, in the breadth-first run you can see that what we did in `property.csvpath` had an impact on what `property2.csvpath` received. Here is a visual the highlights the main considerations of serial vs. parallel runs. There aren't many. However, when they become important, they are very important.
--- [Next Page](/llms-full.txt/1)