Supported file types for remote data sources in Data Virtualization for IBM Cloud Pak for Data

Supported file types for remote data sources in Data Virtualization

Last updated: Nov 26, 2024

In addition to using data from remote data sources or integrated databases, you can use data from files. You can work with data from the following types of files: Comma-separated values (CSV) files, Tab-separated values (TSV) files, and Microsoft Excel spreadsheets.

Data Virtualization also supports accessing CSV, TSV, and JSON files that are stored in Cloud Object Storage. For more information, see Creating a virtualized table from files in Cloud Object Storage in Data Virtualization.

Supported data types in CSV, TSV, and Excel files that are accessed by using remote connectors

Data Virtualization supports data types in CSV, TSV, and Excel files that are accessed by using a remote connector.

Limitations for data types in CSV, TSV, and Excel files that are not stored in Cloud Object Storage

All characters in comma-separated plain text files that are accessed by using a remote connector can be part of a data field. However, you must use quotations to wrap fields that contain special characters, such as commas and carriage return or line-feed characters. Double and single quotation marks, and back-ticks are supported to wrap fields that contain special characters. You must double any nested instances of the wrapping delimiter character inside a wrapped data field to escape and include them. Column names in the first row are subject to the same rules as data fields, which mean that if column names contain special characters, you must wrap these column names in quotations.
You can specify column names in the first row of the file.

Known issues for CSV, TSV, and Excel files that are not stored in Cloud Object Storage

CSV, TSV, or XLS files are not listed in the browse view when:
- The file doesn't have a recognized type or name extension (.csv, .tsv, .xls, .xlsx, .xlsm).
- The file is identified as XLS but doesn't have a sheet name.

Supported data types for columns in the virtualized file that are not stored in Cloud Object Storage

The following table shows which data types and formats in CSV, TSV, and Excel files are supported in Data Virtualization when you virtualize flat files that are not stored in Cloud Object Storage.

Data types	Valid values
CHAR(n)	`1 < n < 254`
VARCHAR(n)	`1 < n < 8168`
BIGINT	`-9223372036854775808 to 9223372036854775807`
BOOLEAN	For TRUE: `t`, `true`, `y`, `yes`, `on`, `1`, or any nonzero, numeric value. For FALSE: `f`, `false`, `n`, `no`, `off`, `0` as a string value, or `0` as a numeric value. The string values are case-insensitive.
INTEGER	`-2147483648 to 2147483647`
SMALLINT	`-32768 to 32767`
DOUBLE	`-1.79769e+308 to 1.79769e+308 and 2.225e-307 to -2.225e-307`
FLOAT	`-1.79769e+308 to 1.79769e+308 and 2.225e-307 to -2.225e-307`
DECIMAL(p,s)	`1 < (p)recision < 31, (s)cale <= (p)recision`
REAL	`-3.402e+38 to 3.402e+38, & 1.175e+37 to -1.175e+37`
Date	`yyyy-mm-dd` `mm/dd/yyy` `dd.mm.yyyy` `d-MMM-y` `yyyyMMdd` `MMM d, yyyy` `EEE, MMM d, y` `EEE d, yyyy` `EEE, d MMM yyyy` `*M/d/y`
Time	`hh:mi[:ss], hh.mi[.ss], hh[:mm] {AM\|PM}`
Timestamp	`yyyy-mm-dd hh:mi:ss[.nnnnnn], yyyy-mm-dd-hh.mm.ss[.nnnnnn]`