# Read > For the complete documentation index, see [llms.txt](https://kotlin.github.io/dataframe/llms.txt). The Kotlin DataFrame library supports CSV, TSV, JSON, XLS and XLSX, and Apache Arrow input formats. API reference: [read](https://kotlin.github.io/dataframe/api-docs/core/org.jetbrains.kotlinx.dataframe.io/read.html), [readCsv](https://kotlin.github.io/dataframe/api-docs/dataframe-csv/org.jetbrains.kotlinx.dataframe.io/read-csv.html), [readJson](https://kotlin.github.io/dataframe/api-docs/dataframe-json/org.jetbrains.kotlinx.dataframe.io/read-json.html), [readExcel](https://kotlin.github.io/dataframe/api-docs/dataframe-excel/org.jetbrains.kotlinx.dataframe.io/read-excel.html), [readArrow](https://kotlin.github.io/dataframe/api-docs/dataframe-arrow/org.jetbrains.kotlinx.dataframe.io/read-arrow.html), [readSqlTable](https://kotlin.github.io/dataframe/api-docs/dataframe-jdbc/org.jetbrains.kotlinx.dataframe.io/read-sql-table.html) Reading from SQL databases is also supported. Read [here](readsqldatabases.html) to know more or explore the [example project](https://github.com/zaleslaw/KotlinDataFrame-SQL-Examples). Each IO source has its own set of `read` functions, like `.readCsv()`, `.readJson()`, `.readExcel()`, etc. For instance: ```KOTLIN DataFrame.readCsv("input.csv") ``` The input string can be a file path or URL. ## Read from CSV Before you can read data from CSV, make sure you have the following dependency: ```KOTLIN implementation("org.jetbrains.kotlinx:dataframe-csv:1.0.0-rc02") ``` It's included by default if you have `org.jetbrains.kotlinx:dataframe:1.0.0-rc02` already. To read a CSV file, use the `.readCsv()` function. Since DataFrame v0.15, this new CSV integration is available. It is faster and more flexible than the old one, now being based on [Deephaven CSV](https://github.com/deephaven/deephaven-csv). To read a CSV file from a file: ```KOTLIN DataFrame.readCsv("input.csv") // Alternatively DataFrame.readCsv(File("input.csv")) ``` To read a CSV file from a URL: ```KOTLIN DataFrame.readCsv(URI("https://raw.githubusercontent.com/Kotlin/dataframe/master/data/jetbrains_repositories.csv").toURL()) ``` Zip and GZip files are supported as well. To read CSV from `String`: ```KOTLIN val csv = """ A,B,C,D 12,tuv,0.12,true 41,xyz,3.6,not assigned 89,abc,7.1,false """.trimIndent() DataFrame.readCsvStr(csv) ``` ### Specify delimiter By default, CSV files are parsed using `,` as the delimiter. To specify a custom delimiter, use the `delimiter` argument: ```KOTLIN val df = DataFrame.readCsv( file, delimiter = '|', header = listOf("A", "B", "C", "D"), parserOptions = ParserOptions(nullStrings = setOf("not assigned")), ) ``` Aside from the delimiter, there are many other parameters to change. These include the header, the number of rows to skip, the number of rows to read, the quote character, and more. Check out the KDocs for more information. ### Column type inference from CSV Column types are inferred from the CSV data. We rely on the fast implementation of [Deephaven CSV](https://github.com/deephaven/deephaven-csv) for inferring and parsing to (nullable) `Int`, `Long`, `Double`, and `Boolean` types. For other types we fall back to [the parse operation](parse.html). Suppose that the CSV from the previous example had the following content: [Embedded content](./resources/csvTypeInference.html) Then we get a [DataFrame](dataframe.html): ```KOTLIN val df = DataFrame.readCsv( file, delimiter = ',', parserOptions = ParserOptions(nullStrings = setOf("not assigned")), ) ``` [Embedded content](./resources/readCsvTypeInference.html) with the following schema: ```TEXT A: Int B: String C: Double D: Boolean? ``` [DataFrame](dataframe.html) can [parse](parse.html) columns as JSON too, so when reading the following table with a JSON object in column D: [Embedded content](./resources/csvWithJsonColumns.html) We get this [DataFrame](dataframe.html) ```KOTLIN val df = DataFrame.readCsv(file) ``` [Embedded content](./resources/readCsvWithJsonColumns.html) with a data schema where D is a [ColumnGroup](datacolumn.html#columngroup) with two nested columns: ```TEXT A: Int D: B: Int C: Int ``` For a column where values are lists of JSON values: [Embedded content](./resources/csvWithJsonListsColumns.html) We get this [DataFrame](dataframe.html): ```KOTLIN val df = DataFrame.readCsv(file) ``` [Embedded content](./resources/readCsvWithJsonListsColumns.html) with the following schema: ```TEXT A: Int G: * B: Int C: Int D: Int ``` ### Work with locale-specific numbers Sometimes columns in your CSV can be interpreted differently depending on your system locale. [Embedded content](./resources/csvLocale.html) Here a comma can be a decimal- or thousands separator, and thus become different values. You can deal with it in multiple ways, for instance: 1. Provide locale as a parser option ```KOTLIN val df = DataFrame.readCsv( file, parserOptions = ParserOptions(locale = Locale.UK), ) ``` [Embedded content](./resources/readNumbersWithSpecificLocale.html) 1. Disable type inference for a specific column and convert it yourself ```KOTLIN val df = DataFrame.readCsv( file, colTypes = mapOf("numbers" to ColType.String), ) ``` [Embedded content](./resources/readNumbersWithColType.html) ### Work with specific date-time formats When parsing date or date-time columns, you might encounter formats different from the default `ISO_LOCAL_DATE_TIME`. Like: [Embedded content](./resources/dateTimeSample.html) Because the format here `"dd/MMM/yy h:mm a"` differs from the default (`ISO_LOCAL_DATE_TIME`), columns like this may be recognized as simple `String` values rather than actual date-time columns. You can fix this whenever you [parse](parse.html) a string-based column (e.g., using [DataFrame.readCsv()](#read-from-csv), [DataFrame.readTsv()](#read-from-csv), or [DataColumn<String>.convertTo<>()](convert.html)) by providing a custom date-time pattern. There are two ways to do this: 1. By providing the date-time pattern as a raw string to the `ParserOptions` argument: ```KOTLIN val df = DataFrame.readCsv( file, parserOptions = ParserOptions( dateTime = DateTimeParserOptions.Java.withPattern("dd/MMM/yy h:mm a"), ), ) ``` [Embedded content](./resources/readDatesWithSpecificDateTimePattern.html) 1. By providing a `DateTimeFormatter` to the `ParserOptions` argument: ```KOTLIN val df = DataFrame.readCsv( file, parserOptions = ParserOptions( dateTime = DateTimeParserOptions.Kotlin.withFormat( LocalDate.Format { monthNumber(padding = Padding.SPACE); char('/'); day(); char(' '); year() }, ), ), ) ``` [Embedded content](./resources/readDatesWithSpecificDateTimeFormatter.html) These two approaches are essentially the same, just specified in different ways. The result will be a dataframe with properly parsed `DateTime` columns. > **Tip:** > Note: Although these examples focus on reading CSV files, these `ParserOptions` can be supplied to any `String`-column-handling operation (like `readCsv`, `readTsv`, `stringCol.convertTo<>()`, etc.). This allows you to configure the locale, null-strings, date-time patterns, and more. > > > > For more details on the parse operation, see the [parse operation](parse.html). ### Provide a default type for all columns While you can provide a `ColType` per column, you might not always know how many columns there are or what their names are. In such cases, you can disable type inference for all columns by providing a default type for all columns: ```KOTLIN val df = DataFrame.readCsv( file, colTypes = mapOf(ColType.DEFAULT to ColType.String), ) ``` This default can be combined with specific types for other columns as well. ### Unlocking Deephaven CSV features For each group of functions (`readCsv`, `readDelim`, `readTsv`, etc.) we provide one overload which has the `adjustCsvSpecs` parameter. This is an advanced option because it exposes the [CsvSpecs.Builder](https://github.com/deephaven/deephaven-csv/blob/main/src/main/java/io/deephaven/csv/CsvSpecs.java) of the underlying Deephaven implementation. Generally, we don't recommend using this feature unless there's no other way to achieve your goal. For example, to enable the (unconfigurable but) very fast [ISO DateTime Parser of Deephaven CSV](https://medium.com/@deephavendatalabs/a-high-performance-csv-reader-with-type-inference-4bf2e4baf2d1): ```KOTLIN val df = DataFrame.readCsv( inputStream = file.openStream(), adjustCsvSpecs = { // it: CsvSpecs.Builder it.putParserForName("date", Parsers.DATETIME) }, ) ``` ## Read from JSON Before you can read data from JSON, make sure you have the following dependency: ```KOTLIN implementation("org.jetbrains.kotlinx:dataframe-json:1.0.0-rc02") ``` It's included by default if you have `org.jetbrains.kotlinx:dataframe:1.0.0-rc02` already. To read a JSON file, use the `.readJson()` function. JSON files can be read from a file or a URL. Note that after reading a JSON with a complex structure, you can get hierarchical [DataFrame](dataframe.html): [DataFrame](dataframe.html) with `ColumnGroup`s and [FrameColumn](datacolumn.html#framecolumn)s. To read a JSON file from a file: ```KOTLIN val df = DataFrame.readJson(file) ``` To read a JSON file from a URL: ```KOTLIN DataFrame.readJson("https://raw.githubusercontent.com/Kotlin/dataframe/refs/heads/master/data/participants.json") ``` ### Column type inference from JSON Type inference for JSON is much simpler than for CSV. JSON string literals always become a `String`. Number literals are converted to a unified `Number` type which will fit all encountered numbers. Boolean literals are converted to `Boolean`. Let's take a look at the following JSON and read it as a string: ```KOTLIN val text = """ [ { "A": "1", "B": 1, "C": 1.0, "D": true }, { "A": "2", "B": 2, "C": 1.1, "D": null }, { "A": "3", "B": 3, "C": 1, "D": false }, { "A": "4", "B": 4, "C": 1.3, "D": true } ] """.trimIndent() val df = DataFrame.readJsonStr(text) ``` [Embedded content](./resources/readJsonFromString.html) The corresponding [DataFrame](dataframe.html) schema is: ```TEXT A: String B: Int C: Double D: Boolean? ``` Column A has `String` type because all values are string literals, no implicit conversion is performed. Column C has the `Double` type because it's the smallest unified number type for `Int` and `Float`. ### JSON parsing options #### The "value" and "array" columns for standalone values A JSON element that isn't an object has no property name to be used as a column name, so the reader falls back to two fixed names: * "value" holds the JSON element if it's a primitive, else it's `null`. * "array" holds the JSON element as a list if it's an array, else it's `null`. They're created in two situations. The first is at the top level, where the elements of the JSON array are the rows themselves and so have no name at all. This happens regardless of the `typeClashTactic` and even when there's no clash to begin with. * An array of primitives is read into a single "value" column: ```KOTLIN val df = DataFrame.readJsonStr("""[1, 2, 3]""") ``` [Embedded content](./resources/readJsonValueColumn.html) * An array of arrays, into a single "array" column of lists: ```KOTLIN val df = DataFrame.readJsonStr("""[[1], [2]]""") ``` [Embedded content](./resources/readJsonArrayColumn.html) * When those arrays hold objects, "array" becomes a [FrameColumn](datacolumn.html#framecolumn): ```KOTLIN val df = DataFrame.readJsonStr("""[[{ "a": 1 }], [{ "a": 2 }]]""") ``` [Embedded content](./resources/readJsonArrayOfObjectsColumn.html) The second situation is a type clash, described below. #### Manage type clashes By default, if a type clash occurs when reading JSON — the same property holds elements of different shapes across records (objects, values or arrays) — a new [column group](datacolumn.html#columngroup) is created for that property. This column group contains a column for each child object property (across all records), and "value" and "array" columns following the logic of reading row values and arrays as [described above](#value-and-array-columns). Each value will spread out to its own column in the group, other columns will contain `null`. In this case `typeClashTactic = JSON.TypeClashTactic.ARRAY_AND_VALUE_COLUMNS`. For example, this is how the following JSON will be read (including `null` values): ```KOTLIN val complexClashedJson = """ [ { "a": "text" }, { "a": { "b": 2 } }, { "a": [ 6, 7, 8 ] } ] """.trimIndent() val df = DataFrame.readJsonStr(complexClashedJson) ``` [Embedded content](./resources/readJsonTypeClash.html) A clash at the top level has no property to group under, so there "value", "array", and the objects' properties become top-level columns of the [DataFrame](dataframe.html) itself, as in [usual standalone values reading](#value-and-array-columns): ```KOTLIN val df = DataFrame.readJsonStr("""[1, { "label": "record" }]""") ``` [Embedded content](./resources/readJsonTopLevelTypeClash.html) Without a clash there's no such group: a property whose elements are all arrays simply becomes a `List` column under its own name, and one whose elements are all objects becomes a group of just those objects' properties. Since every one of these columns only holds a value for the elements that actually have that shape, all of them are nullable. A JSON `null` element, as well as a missing property, is `null` in all of them. > **Note:** > An empty JSON array `[]` is read as an empty list, which is different from `null`, meaning "there is no array here". > > > > When the arrays contain objects, "array" becomes a [FrameColumn](datacolumn.html#framecolumn) instead. There, an element without an array is an empty [DataFrame](dataframe.html), because frame columns cannot hold `null`. The default type clash tactic makes it more convenient to work with the data, but it can be confusing if you're not expecting it or if you just need the type to be an `Any`. For this case, you can set `typeClashTactic = JSON.TypeClashTactic.ANY_COLUMNS` to get the following: ```KOTLIN val df = DataFrame.readJsonStr(complexClashedJson, typeClashTactic = JSON.TypeClashTactic.ANY_COLUMNS) ``` [Embedded content](./resources/readJsonTypeClashTactic.html) #### Specify Key/Value Paths If you have a JSON like: ```JSON { "dogs": { "fido": { "age": 3, "breed": "poodle" }, "spot": { "age": 5, "breed": "labrador" }, "rex": { "age": 2, "breed": "golden retriever" }, "lucky": { ... }, "rover": { ... }, "max": { ... }, "buster": { ... }, ... }, "cats": { ... } } ``` You will get a column for each dog, which becomes an issue when you have a lot of dogs. This issue is especially noticeable when generating data schemas from JSON, as you might run out of memory when doing that due to the sheer number of generated interfaces. Instead, you can use `keyValuePaths` to specify paths to the objects that should be read as key value frame columns. This can be the difference between: ```KOTLIN val df = DataFrame.readJsonStr(pets) ``` [Embedded content](./resources/readJsonFromStringWithoutKeyValuePairs.html) and ```KOTLIN DataFrame.readJsonStr( text = pets, keyValuePaths = listOf( JsonPath().append("dogs"), // which will result in '$["dogs"]' JsonPath().append("cats"), // which will result in '$["cats"]' ), ) ``` [Embedded content](./resources/readJsonFromStringWithKeyValuePairs.html) To specify the paths, you can use the `JsonPath` class. (The results are wrapped in a [FrameColumn](datacolumn.html#framecolumn) instead of a `ColumnGroup` since lengths between "cats" and "dogs" can vary, among other reasons.) For more examples, see the "examples/json" module. #### Unlocking Kotlinx Serialization JSON parsing features Both `DataFrame.readJson()` and `DataRow.readJson()` have one overload with the `jsonInstance` parameter. This is an advanced option because it exposes the [kotlinx-serialization Json entry point](https://kotlinlang.org/api/kotlinx.serialization/kotlinx-serialization-json/kotlinx.serialization.json/-json/) to you. Generally, the default settings serve best for most JSON you will encounter, but sometimes you may want to customize the behavior. For example, you can read JSON looking like this: ```JSON [{ // some comment a: 123, b: hello, }, { // some other comment a: 456, b: world, }] ``` ```KOTLIN DataFrame.readJson( stream = json.byteInputStream(), format = Json { isLenient = true allowTrailingComma = true allowComments = true }, ) ``` [Embedded content](./resources/readJsonWithJsonInstance.html) ## Read from Excel Before you can read data from Excel, add the following dependency: ```KOTLIN implementation("org.jetbrains.kotlinx:dataframe-excel:1.0.0-rc02") ``` It's included by default if you have `org.jetbrains.kotlinx:dataframe:1.0.0-rc02` already. To read an Excel spreadsheet, use the `.readExcel()` function. Excel spreadsheets can be read from a file or a URL. Supported Excel spreadsheet formats are: xls, xlsx. To read an Excel spreadsheet from a file: ```KOTLIN val df = DataFrame.readExcel(file) ``` To read an Excel spreadsheet from a URL: ```KOTLIN DataFrame.readExcel("https://example.com/data.xlsx") ``` ### Cell type inference from Excel Cells representing dates will be read as `kotlinx.datetime.LocalDateTime`. Cells with number values, including whole numbers such as "100", or calculated formulas will be read as `Double`. Sometimes cells can have the wrong format in an Excel file. For example, you expect to read a column of `String`: ```TEXT IDS 100 <-- Intended to be String, but has numeric cell format in original .xlsx file A100 B100 C100 ``` You will get a column of `Serializable` instead (common parent for `Double` and `String`). You can fix it by providing an additional parameter: ```KOTLIN val df = DataFrame.readExcel("mixed_column.xlsx", stringColumns = StringColumns("A")) ``` ## Read Apache Arrow formats Before you can read data from Apache Arrow format, add the following dependency: ```KOTLIN implementation("org.jetbrains.kotlinx:dataframe-arrow:1.0.0-rc02") ``` It's included by default if you have `org.jetbrains.kotlinx:dataframe:1.0.0-rc02` already. To read Apache Arrow formats, use the `.readArrowFeather()` function: ```KOTLIN val df = DataFrame.readArrowFeather(file) ``` [DataFrame](dataframe.html) supports reading [Arrow interprocess streaming format](https://arrow.apache.org/java/current/ipc.html#writing-and-reading-streaming-format) and [Arrow random access format](https://arrow.apache.org/java/current/ipc.html#writing-and-reading-random-access-files) from raw Channel (ReadableByteChannel for streaming and SeekableByteChannel for random access), ArrowReader, InputStream, File, or ByteArray. > **Note:** > If you use Java 9+, follow the [Apache Arrow Java compatibility](https://arrow.apache.org/docs/java/install.html#java-compatibility) guide.