In load data, CSV row contains special char at start and end of value but insert as a NULL

[Harmeet Singh]

Hey Team,

I am trying to load the data in CSV with special char. But if some rows contains some special char at start and end, the whole value is ignore and inserted Null value. I am expecting this values insert as a string if the start and end char are not match with quote char. Following are the details:

CSV file: 

name, description, salary, age, dob
tammy,$my name$,$900000$,22,19/10/2019
tammy1,$delhi$,$32345$,22,19/10/2019
tammy2,$banglore$,$543$,%22%,19/10/2019

Create Table:

create table one (name string, description string, salary double, age int, dob timestamp) stored by 'carbondata';

load data:

load data local inpath 'hdfs://localhost:54310/home/harmeet/dollarquote1.csv' into table one OPTIONS('QUOTECHAR'="$");

Actual Results:

+---------+--------------+-------+-----------+-------+--+
|  name   | description  |  dob  |  salary   |  age  |
+---------+--------------+-------+-----------+-------+--+
| tammy   | my name      | NULL  | 900000.0  | 22    |
| tammy1  | delhi        | NULL  | 32345.0   | 22    |
| tammy2  | banglore     | NULL  | 543.0     | NULL  |
+---------+--------------+-------+-----------+-------+--+
5 rows selected (0.247 seconds)

Expected Results:

+---------+--------------+-------+-----------+-------+--+
|  name   | description  |  dob  |  salary   |  age  |
+---------+--------------+-------+-----------+-------+--+
| tammy   | my name      | NULL  | 900000.0  | 22    |
| tammy1  | delhi        | NULL  | 32345.0   | 22    |
| tammy2  | banglore     | NULL  | 543.0     | %22%  |
+---------+--------------+-------+-----------+-------+--+

[ravipesala]

Here age is int data type so it cannot insert special characters. Please try with string type and verify.

[Harmeet Singh]

Thanks Ravi, This is working fine.