Read CSV or other delimited files

This guide shows you how to read data from CSV (and other delimited) files into in-memory Deephaven tables by using the readCsv method. The result is a static table that holds the file's contents when you read it. To work with data that keeps arriving, see Kafka.

Tip

To upload a CSV without writing code, see Upload a table from a file.

readCsv

The two most common ways to load a CSV file are using the file path or the file URL. The header row of the CSV file supplies the column names.

The basic syntax for readCsv follows:

The optional specs argument is a CsvSpecs object that holds read options such as the delimiter and header handling. To create one:

  1. Call CsvTools.builder.
  2. Set the options you need. The readCsv reference lists them all.
  3. Call build.

The builder starts from the same defaults that readCsv(path) uses, such as rewriting headers that aren't valid Deephaven column names into valid ones. The examples from Headerless CSV files onward each build a CsvSpecs object.

Let's dive into some simple examples. These examples use CSV files from Deephaven's examples repository, so you don't have to create your own. We encourage you to use your own files by replacing the file paths in our queries.

The URL examples need no setup. The examples that read from a file path expect those files under /data/examples. To set that up, follow the directions in the examples repository README and see The /data mount point.

readCsv with a file path

Use readCsv to read a CSV file from a file path. In this example, we read a CSV file that contains R.A. Fisher's classic iris flower dataset, which is commonly used in machine learning.

The /data mount point

The Compose examples in the Docker install guide mount ./data:/data in docker-compose.yml. With that mount, Docker shares the data folder next to your docker-compose.yml file with the running Deephaven container at /data. See Docker data volumes for more about how container paths map to your computer. A file you save at ./data/abc/file.csv on your computer is readable in Deephaven at /data/abc/file.csv. A container started with docker run and no -v option still has a /data directory, but Docker doesn't share it with your computer.

Note

With that mount, if the ./data directory does not exist when you launch Deephaven, Docker creates it.

readCsv with a URL

Now, let's read the same iris dataset, but this time we pass readCsv a URL to the file in Deephaven's examples repository on GitHub instead of a file path.

Note

This works with a publicly accessible http or https URL that points to an uncompressed CSV file. readCsv decompresses a compressed file only when it reads the file from a file path or a file: URL.

Headerless CSV files

CSV files don't always have headers. To read one, pass readCsv a CsvSpecs whose hasHeaderRow option is set to false. The example below reads the headerless DeNiro CSV this way.

Because no column names are provided, the table uses default column names (Column1, Column2, etc.). To name the columns, pass the builder's headers option a list of names. Names are applied by position, so list them in the file's column order.

Other delimiters

Deephaven lets you specify other delimiters if your file is not comma-delimited. Set the delimiter option of CsvSpecs, which accepts any single 7-bit ASCII character.

Tab-delimited data

The example below reads a tab-delimited file with delimiter('\t' as char).

Pipe-delimited data

The pipe character (|) is a common delimiter. In the example below, we supply a CsvSpecs with the delimiter set to |.

Read a poorly formatted file

In the following example, we read the deniro_poorly_formatted.csv file from Deephaven's examples repository. This file has a number of issues that we need to address, but readCsv can handle them if we give the right CsvSpecs options:

In the example above, we:

  • Use headers to replace the file's column names.
  • Use skipRows to skip the first 5 data rows after the header row.
  • Set numRows to 20 to read at most 20 lines after the skipped rows.
  • Set ignoreEmptyLines to true to skip the empty line in the CSV file. The skipped line still counts toward the numRows limit, so the table has 19 rows. Without ignoreEmptyLines, readCsv treats an empty line as a row with missing columns. It throws an exception, or it reads a row of null values when allowMissingColumns is true.
  • Set allowMissingColumns to true to avoid throwing an exception due to a missing column in the CSV file. Note that the 1983 entry in our table has null in the missing column.
  • Set ignoreExcessColumns to true to avoid throwing an exception due to an extra column in the CSV file. The entry for 1985's Brazil has an extra column with a brief review that we don't need in our table.
  • Set trim to true to remove leading and trailing white space from inside quoted strings, such as some of our movie titles. By default, readCsv keeps this white space and reads " The Mission " with its leading and trailing spaces.

To skip lines that come before the header row, use skipHeaderRows instead of skipRows.

See the readCsv reference documentation for every CsvSpecs builder option.