# Validations ## Column Data Types While creating a sheet you can specify the column to be any of the data types mentioned below. If the incoming CSV data does not match the column data type (and its validation rules), then the user will see a relevant error message identifying what the problem is and how to fix it. ### Text This is the default column data type. It accepts any alphanumeric string. You can provide the `Min Length` and `Max Length` parameters to specify the acceptable length of the text data. ### Number This **Number** column data type accepts integer and float strings only. Additionally, you can provide the `Min Value` and `Max Value` parameters to specify the acceptable range of numbers.
Values for Excel Setting When importing data from Excel files, you may encounter unexpected issues due to the way Excel internally stores and formats different types of values—especially **Numbers**. Excel often applies formatting to numeric values that changes how they appear: * A raw value of `0.15` could be displayed as `15%` * A value of `1200` might appear as `$1,200.00` * A long number like `1234567890123` may be auto-converted to scientific notation (`1.23E+12`) These formatted displays can mislead the importer if you're expecting clean numbers. With CSVbox you have the following options to select the way the values are interpretated for numeric columns. 1. **Formatted Values** Imports numbers exactly as shown in Excel. Examples: * `$1,200.00` stays `$1,200.00` * `15%` stays `15%` * `1.23E+12` stays in scientific notation 2. **Original RAW Values** Strips all formatting and imports the core numeric value. Examples: * `$1,200.00` → `1200` * `15%` → `0.15` * `1.23E+12` → `1230000000000` {% hint style="info" %} Use RAW if you’re planning to run calculations or validations on numeric fields post-import. {% endhint %}
### Email The column under validation must be formatted as an email address. ### Date The column under validation must be formatted as a date. You have to select a `Date Format` from the dropdown list to specify the acceptable format. The default format is '*MM/DD/YYYY*'. If your desired format is not found in the list, you can simply provide your custom format using the formatting tokens below. | | Token | Output | | ------------------------------ | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **Month** | M | 1 2 ... 11 12 | | | Mo | 1st 2nd ... 11th 12th | | | MM | 01 02 ... 11 12 | | | MMM | Jan Feb ... Nov Dec | | | MMMM | January February ... November December | | **Quarter** | Q | 1 2 3 4 | | | Qo | 1st 2nd 3rd 4th | | **Day of Month** | D | 1 2 ... 30 31 | | | Do | 1st 2nd ... 30th 31st | | | DD | 01 02 ... 30 31 | | **Day of Year** | DDD | 1 2 ... 364 365 | | | DDDo | 1st 2nd ... 364th 365th | | | DDDD | 001 002 ... 364 365 | | **Day of Week** | d | 0 1 ... 5 6 | | | do | 0th 1st ... 5th 6th | | | dd | Su Mo ... Fr Sa | | | ddd | Sun Mon ... Fri Sat | | | dddd | Sunday Monday ... Friday Saturday | | **Day of Week (Locale)** | e | 0 1 ... 5 6 | | **Day of Week (ISO)** | E | 1 2 ... 6 7 | | **Week of Year** | w | 1 2 ... 52 53 | | | wo | 1st 2nd ... 52nd 53rd | | | ww | 01 02 ... 52 53 | | **Week of Year (ISO)** | W | 1 2 ... 52 53 | | | Wo | 1st 2nd ... 52nd 53rd | | | WW | 01 02 ... 52 53 | | **Year** | YY | 70 71 ... 29 30 | | | YYYY | 1970 1971 ... 2029 2030 | | | YYYYYY |

-001970 -001971 ... +001907 +001971
Note: Expanded Years (Covering the full time value range of approximately 273,790 years forward or backward from 01 January, 1970)

| | | Y |

1970 1971 ... 9999 +10000 +10001
Note: This complies with the ISO 8601 standard for dates past the year 9999

| | **Era Year** | y | 1 2 ... 2020 ... | | **Era** | N, NN, NNN |

BC AD
Note: Abbr era name

| | | NNNN |

Before Christ, Anno Domini
Note: Full era name

| | | NNNNN |

BC AD
Note: Narrow era name

| | **Week Year** | gg | 70 71 ... 29 30 | | | gggg | 1970 1971 ... 2029 2030 | | **Week Year (ISO)** | GG | 70 71 ... 29 30 | | | GGGG | 1970 1971 ... 2029 2030 | | **AM/PM** | A | AM PM | | | a | am pm | | **Hour** | H | 0 1 ... 22 23 | | | HH | 00 01 ... 22 23 | | | h | 1 2 ... 11 12 | | | hh | 01 02 ... 11 12 | | | k | 1 2 ... 23 24 | | | kk | 01 02 ... 23 24 | | **Minute** | m | 0 1 ... 58 59 | | | mm | 00 01 ... 58 59 | | **Second** | s | 0 1 ... 58 59 | | | ss | 00 01 ... 58 59 | | **Fractional Second** | S | 0 1 ... 8 9 | | | SS | 00 01 ... 98 99 | | | SSS | 000 001 ... 998 999 | | | SSSS ... SSSSSSSSS | 000\[0..] 001\[0..] ... 998\[0..] 999\[0..] | | **Time Zone** | z or zz |

EST CST ... MST PST
Note: as of 1.6.0, the z/zz format tokens have been deprecated from plain moment objects. Read more about it here. However, they *do* work if you are using a specific time zone with the moment-timezone addon.

| | | Z | -07:00 -06:00 ... +06:00 +07:00 | | | ZZ | -0700 -0600 ... +0600 +0700 | | **Unix Timestamp** | X | 1360013296 | | **Unix Millisecond Timestamp** | x | 1360013296123 | {% hint style="info" %} For example, specifying a token string **`dddd, MMMM Do YYYY, h:mm:ss`** will require the date to be in the format **`Sunday, February 14th 2010, 3:25:50 pm`.** {% endhint %} ### Time The column must be formatted in a time format. You have to select a `Time Format` from the dropdown list to specify the acceptable format. The default format is '*HH:mm:ss*'. If your desired format is not found in the list, you can simply provide your custom format using the formatting tokens below. | | Token | Output | | ------------------------------ | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **AM/PM** | A | AM PM | | | a | am pm | | **Hour** | H | 0 1 ... 22 23 | | | HH | 00 01 ... 22 23 | | | h | 1 2 ... 11 12 | | | hh | 01 02 ... 11 12 | | | k | 1 2 ... 23 24 | | | kk | 01 02 ... 23 24 | | **Minute** | m | 0 1 ... 58 59 | | | mm | 00 01 ... 58 59 | | **Second** | s | 0 1 ... 58 59 | | | ss | 00 01 ... 58 59 | | **Fractional Second** | S | 0 1 ... 8 9 | | | SS | 00 01 ... 98 99 | | | SSS | 000 001 ... 998 999 | | | SSSS ... SSSSSSSSS | 000\[0..] 001\[0..] ... 998\[0..] 999\[0..] | | **Time Zone** | z or zz |

EST CST ... MST PST
Note: as of 1.6.0, the z/zz format tokens have been deprecated from plain moment objects. Read more about it here. However, they *do* work if you are using a specific time zone with the moment-timezone addon.

| | | Z | -07:00 -06:00 ... +06:00 +07:00 | | | ZZ | -0700 -0600 ... +0600 +0700 | | **Unix Timestamp** | X | 1360013296 | | **Unix Millisecond Timestamp** | x | 360013296123 | ### Boolean The column under validation must be able to be cast as a boolean. Accepted inputs are *true*, *false*, *TRUE*, *FALSE*, *1*, *0*, *yes*, *no*, *y*, *n*, *on*, *off*, *enabled,* and *disabled*. ### Regex The column data must match the given regular expression. You need to specify the `Regex` and the importer will pattern match it with the incoming data. ### IP The column under validation must be an IP address. You have to select the `IP Version`. ### URL The column under validation must be a valid URL. ### Credit Card The column under validation must be formatted as a credit card number. Acceptable formats are *5555555555554444*, *5555-5555-5555-4444* and *5555 5555 5555 4444*. ### Phone Number The column will be validated for phone number formats based on the [libphonenumber.js library](https://catamphetamine.gitlab.io/libphonenumber-js/). You have to select a default country code. If the incoming phone number does not have a country code then this default country code will be used for validation. ### Currency The column data should be in a currency amount format. You can specify currency formatting options based on the [Validator.js library](https://github.com/validatorjs/validator.js). ### List With the **List** data type, you can specify a list of acceptable values. The importer will compare the CSV column data with the list of acceptable values and throw a validation error if there is a mismatch. You can specify a list of acceptable `Values` and related `Display Labels`. The importer will match the column data with the list of `Display Labels`. If the data passes validation then the `Values` corresponding to the `Display Labels` will be pushed to the destination. For example say, you configure the List column as shown below: ![List Data Type](https://1907234374-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN8YRAnfnCaoVVZflQz%2Fuploads%2FQmP9uAH4EjcvJ4s9mgMw%2FList.jpg?alt=media\&token=686aa01c-130f-4caf-ac3f-d570dddbf787) In this case, the acceptable data in the CSV column will be *Small*, *Medium*, and *Large*. Based on the actual data found in the user CSV, the values *s*, *m* or *l* will be pushed to your data destination. This allows you to accept readable values from your users while pushing data to your system that is in a format it understands. {% hint style="info" %} **Allow Other Values** Selecting the 'Accept Other Values' option will allow the users to input values that are not found in the predefined list of acceptable values. {% endhint %} {% hint style="info" %} #### Accept List Values If you enable this option, the importer will accept List **Values** interchangeably with **Display Labels** as valid data for the column. ![](https://1907234374-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN8YRAnfnCaoVVZflQz%2Fuploads%2FyGgqSQkXRawNveSEehD8%2Flist%20values.png?alt=media\&token=e7fbca88-540f-436f-9a3a-47ff99cd1fb1) Refer to the list of items in the image above. If the column contains the value "NYC," the importer will fail validation since it only accepts the display label "New York City." To enable the importer to accept "NYC" as well, you need to activate the "Accept List Values" option. {% endhint %} ### Dependent List Sometimes, you may want to use more than one list such that the items available in a second drop-down list dependent on the selection made in the first drop-down list. Let us call them dependent lists. Below is an example of a dependent list. ![](https://1907234374-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN8YRAnfnCaoVVZflQz%2Fuploads%2FWB5Rw17khalg3cmj31gZ%2Fimage.png?alt=media\&token=f487339a-58fe-4d82-a452-8ff268eed8ca) You can see that the options in the City column depend on the selection made in the Country column. If you select 'USA' in Drop Down 1, then you will cities from the USA, but if you select Canada in Drop Down 1, then you see the cities from Canada in Drop Down 2. The same functionality can be achieved in CSVbox via a combination of [List](#list) and Dependent List column types (or a combination of [Dynamic List](#dynamic-list) and [Dependent Dynamic List](#dependent-dynamic-list) column types). Here are the steps to create conditional lists using the example above: 1. Create a column 'Country'. Configure column type as 'List'. 2. Create a column 'City'. Configure column type as 'Dependent List'. Select 'Country' as the Primary column.\ 3. Go back to the 'Country' column and add the list of valid values. 4. For each valid country value, you can add dependent (city) values as shown below. ![](https://1907234374-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MN8YRAnfnCaoVVZflQz%2Fuploads%2FyU7PraU8FsEDTjqabUnN%2Fdependents.jpg?alt=media\&token=558a7773-e771-4605-a532-363a0eaa5190) {% hint style="warning" %} If you change the name of the primary column or reorder the priority of any of the columns, then you need to reassign the primary column in the Dependent List column settings. {% endhint %} {% hint style="warning" %} The Display Label and the Value should be unique across all the items. Primary items cannot share dependents having the same Display Label and/or same Value. {% endhint %} ### Dynamic List This is similar to the **List** type column above where the importer will validate the column data against a list of values. However, instead of providing a static list of values, with the **Dynamic List** column type, you can now specify a list of acceptable values in real-time via an API. The API should return the list of values in the JSON format as shown below. ```json [ {"value": "uk", "display_label": "England"}, {"value": "us", "display_label": "United States"}, {"value": "au", "display_label": "Australia"} ] ``` For each list item, the `value` field is mandatory while `display_label` is optional. {% hint style="info" %} You have the option to attach the [custom user attributes](https://help.csvbox.io/getting-started/2.-install-code#referencing-the-user) as query parameters to the Dynamic List API request. **csvbox\_** prefix will be added to the custom user attribute query parameters. This will help you identify the users/environment and return back a relevant list of values. {% endhint %} For authenticating the requests you can pass the authorization headers via the [**dynamic\_list\_request\_headers** ](https://help.csvbox.io/getting-started/2.-install-code#dynamic_list_request_headers)initialization option. ### Dependent Dynamic List Sometimes, you may want to use more than one list such that the items available in a second drop-down list depend on the selection made in the first drop-down list. Let us call them dependent lists. Below is an example of a dependent list.
Conditional dependent list
You can see that the options in the City column depend on the selection made in the Country column. If you select 'USA' in Drop Down 1, then you will cities from the USA, but if you select Canada in Drop Down 1, then you see the cities from Canada in Drop Down 2. The same functionality can be achieved in CSVbox via a combination of [Dynamic List](#dynamic-list) and Dependent Dynamic List column types (or a combination of [List](#list) and [Dependent List](#dependent-list) column types). Here are the steps to create conditional lists using the example above: 1. Create a column 'Country'. Configure column type as 'Dynamic List' 2. Create a column 'City'. Configure column type as 'Dependent Dynamic List'. Select 'Country' as the Primary column.\ 3. Go back to the 'Country' column and configure the API that will fetch valid values from your application for 'Country' as well as its dependent column i.e. City column. Your API should return the list of values in the JSON format as shown below. ```json [ {"value": "USA", "display_label": "USA", "dependents": [ {"value": "ny", "display_label": "New York"}, {"value": "ch", "display_label": "Chicago"}, {"value": "se", "display_label": "Seatle"}, {"value": "mi", "display_label": "Miami"} ]}, {"value": "Canada", "display_label": "Canada", "dependents": [ {"value": "to", "display_label": "Toronto"}, {"value": "va", "display_label": "Vancouver"} ]} ] ``` Note the **`dependents`** object above. It contains the list of valid values for the dependent column based on the primary column value. {% hint style="warning" %} If you change the name of the primary column or reorder the priority of any of the columns, then you need to reassign the primary column in the Dependent Dynamic List column settings. {% endhint %} {% hint style="warning" %} The Display Label and the Value should be unique across all the items. Primary items cannot share dependents having the same Display Label and/or same Value. {% endhint %} ### Multi-select List The **Multi-select List** data type is similar to the [**List**](#list) data type, where you can specify a list of acceptable values. While the **List** data type accepts only one value per cell, the **Multi-select List** accepts multiple comma-separated values.

Multi-Select List

The importer will compare the values in the incoming data with the list of acceptable values and throw a validation error if there is a mismatch. {% hint style="info" %} **Allow Other Values** Selecting the 'Accept Other Values' option will allow the users to input values that are not found in the predefined list of acceptable values. {% endhint %} ### Dynamic Multi-select List This is similar to the **Multi-select** **List** type column above where the importer accepts multiple comma-separated values. However, instead of providing a static list of values, here, you can now specify a list of acceptable values in real time via an API. The API should return the list of values in the JSON format as shown below. ```json [ {"value": "Red"}, {"value": "Green"}, {"value": "Blue"} ] ``` {% hint style="info" %} The importer will attach the [custom user attributes](https://help.csvbox.io/getting-started/2.-install-code#referencing-the-user) as query parameters to the Dynamic List API request. **csvbox\_** prefix will be added to the custom user attribute query parameters. This will help you identify the users/environment and return back a relevant list of values. {% endhint %} For authenticating the requests you can pass the authorization headers via the [**dynamic\_list\_request\_headers** ](https://help.csvbox.io/getting-started/2.-install-code#dynamic_list_request_headers)initialization option. ## Other Validation Options ### Column Required You can check/uncheck the `Required` checkbox on the Column Settings window. The column data must be present, and not empty if the `Required` checkbox is ticked.