NULL and Empty Markers

Any column that is declared nullable in a CREATE TABLE statement can store a NULL value. A null marker is a character string that defines the presence of a NULL value. Typical null markers are the 4-character string NULL and the 2-character string \N.

Only CHAR and VARCHAR columns can store an empty value (a zero-length string). An empty marker is a string that defines the presence of an empty string between adjacent fields.

Depending on the format of the exported source data that you are going to load, you may have to use ybload options that define how NULL values or empty strings are marked. These options are called --nullmarker and --emptymarker. In some cases, you may need to specify one of these options so that ybload can distinguish intended NULL values from intended empty strings. In practice, you should not have to specify both of these options in any ybload command; most exported data is formatted such that either one type of marker is needed or no markers are needed.

The need for these options depends in part on the --format setting you use:

CSV: standard CSV files do not need markers to be identified, but custom CSV files may require them. A standard CSV file identifies:
- Empty strings by placing two quotes inside two field delimiters: <delimiter>''<delimiter>. For example, if the field delimiter is a comma: ,"",
- NULL values by placing field delimiters immediately next to each other: <delimiter><delimiter>. For example, if the field delimiter is a comma: ,,
A sample CSV data row with five fields might look like this:
```
"2018","","Mini",,"Cooper S"
```
In this example, the second field contains an empty string, and the fourth field contains a NULL value. If your source files look like this, you do not need to specify --nullmarker or --emptymarker.
TEXT: you may need to specify a null marker or an empty marker. The exported data may specify empty strings with adjacent delimiters and NULL values with either \N or the string NULL (for example). The ybload null marker default is \N.
Check the format of the exported TEXT data and make sure you know how each marker is represented. Some flat file formats do not natively distinguish between NULLs and empty strings; therefore, you need to discover the format of the exported data and specify the appropriate ybload options.
BCP: If you are using --format BCP, you should not specify a null marker or an empty marker in the ybload command. The default empty marker is \0, which matches the BCP exported data. Adjacent delimiters define NULLs in BCP format.

ybload Behavior If Both Markers Are Specified

The following tables demonstrate the ybload processing rules that apply if you specify both a null marker and an empty marker in a ybload command. In general, specifying both markers is not necessary, and the command output will contain a warning message to that effect.

Note: The examples in the first table apply to the CSV format only.


Source Data for Three Adjacent Fields	Data Type of Second Field	ybload Marker Settings	Second Field Parsed As?	Notes
`abc,"",def`	`CHAR/VARCHAR`	N/A	Empty string	`--nullmarker` and `--emptymarker` settings are ignored.
`abc,"",def`	Non-string	`N/A`	NULL	`--nullmarker` and `--emptymarker` settings are ignored.
`abc,"FOO",def`	`CHAR/VARCHAR`	`--nullmarker 'FOO'` and `--emptymarker 'FOO'`	The string `FOO`	The quotes "protect" the string `FOO` from being interpreted as a marker.

Note: The examples in the second table apply to all three formats.


Source Data for Three Adjacent Fields	Data Type of Second Field	ybload Marker Settings	Second Field Parsed As?	Notes
`abc,,def`	Non-string	`N/A`	NULL	`--nullmarker` and `--emptymarker` settings are ignored.
`abc,,def`	`CHAR/VARCHAR`	`Not defined`	NULL
`abc,,def`	`CHAR/VARCHAR`	`--nullmarker` is set	Empty string
`abc,,def`	`CHAR/VARCHAR`	`--emptymarker` is set	`NULL`
`abc,,def`	`CHAR/VARCHAR`	`--nullmarker` and `--emptymarker` both set	`NULL`
`abc,FOO,def`	`CHAR/VARCHAR`	`--nullmarker 'FOO'`	`NULL`
`abc,FOO,def`	`CHAR/VARCHAR`	`--emptymarker 'FOO'`	Empty string
`abc,foo,def`	`CHAR/VARCHAR`	`--nullmarker 'FOO'`and `--ignore-nullmarker-case` is set	`NULL`
`abc,foo,def`	`CHAR/VARCHAR`	`--nullmarker 'FOO'`and `--ignore-nullmarker-case` is not set	The string `foo`
`abc,foo,def`	`CHAR/VARCHAR`	`--emptymarker 'FOO'`and `--ignore-emptymarker-case` is set	Empty string
`abc,foo,def`	`CHAR/VARCHAR`	`--emptymarker 'FOO'`and `--ignore-emptymarker-case` is not set	The string `foo`