Facing an issue while fetching records from CSV file with multiple character(~|*) delimiter.

Hi,

We are fetching data from CSV file(without header) using HXTT driver.

It is properly fetching records with the single charecter  delimiter but not working with multiple charector delimiter.

We are having delimited csv file without having headers  which contains "*|~" charector as a delimiter.
We have written below schema file for it.

Schema file

========

[target.csv]
ColNameHeader=False
Format=Delimited("~|*")
CharacterSet=ANSI

COL1=first_name varchar
COL2=last_name varchar
COL3=city varchar

 

Data

====

Sachin~|*Tendulkar~|*Mumbai

Saurav~|*Ganguly~|*Kolkata

 

so when we perform select operation on it "|*"  character is appended to result.

 

Result of select query

===============

 

 

Datasource in standalone-teiid.xml

=======================

 

<datasource jndi-name="java:/FileDS20200713211347079" pool-name="FileDS20200713211347079">
<connection-url>jdbc:text:sftp://abc:efg@10.10.10.10:22/home/abc/target.csv?odbcSchemaFile=target.sch;fileExtension=csv;delayedClose=0;refreshInterval=0</connection-url>
<driver>textfile</driver>
<pool>
<max-pool-size>20</max-pool-size>
</pool>
</datasource>

 

 

 

As I read in some blogs, they suggested to change the "CharacterSet" from ANSI to UTF-8 in schema file(.sch file).

So I tried to change it to UTF-8 as well as 65001 still getting same result

refered link - https://www.ibm.com/support/pages/special-characters-data-imported-through-odbc-import-using-microsoft-text-driver-are-not-displayed-correctly-admin-client

we also tried to add";charSet=UTF-8" in datasource URL but still its giving same kind of result.

Could you please suggest some solution for this issue.