topic Re: Unicode field separator to create unamanged table in databricks for csv file in Data Engineering

Unicode field separator to create unamanged table in databricks for csv file

RajibRajib_Mand — Thu, 24 Mar 2022 07:18:40 GMT

We are getting

\u318a (ㆊ)

separated csv file. We want to create unmanaged table in databricks, Here is the table creation script.

create table IF NOT EXISTS db_test_raw.t_data_otc_poc

(`caseidt` String,

`worktype` String,

`doctyp` String,

`brand` String,

`reqemailid` String,

`subprocess` String,

`accountname` String,

`location` String,

`lineitems` String,

`emailsubject` String,

`createddate` string,

`process` String,

`archivalbatchid` String,

`createddt` String,

`customername` String,

`invoicetype` String,

`month` String,

`payernumber` String,

`sapaccountnumber` String,SOURCE_BUSINESS_DATE Date ) USING

CSV OPTIONS (header 'true',encoding 'UTF-8',quote '"', escape '"',delimiter '\u318a', path

'abfss://xxxx@yyyyy.dfs.core.windows.net/Raw/OPERATIONS/BUSINESSSERVICES/***/xx_DATA_OTC')

PARTITIONED BY (SOURCE_BUSINESS_DATE )

The table created successfully in databricks.

While checking (

describe table extended db_test_raw.t_data_otc_poc

), we found storage properties as [encoding=UTF-8, quote=", escape=", header=true, delimiter=?] .The delimiter got changed.

Can you please let us know what went wrong here?

Data is also loaded into first columns and value for the rest of the column is null

Re: Unicode field separator to create unamanged table in databricks for csv file

RajibRajib_Mand — Thu, 24 Mar 2022 07:21:06 GMT

Also all data loaded into single coumn.Value of the other column stored as null

Re: Unicode field separator to create unamanged table in databricks for csv file

Hubert-Dudek — Thu, 24 Mar 2022 11:02:59 GMT

sep "\u318a"

delimeter " \x318a"

sep " \x318a"

Try to use sep instead or/and x instead.

Re: Unicode field separator to create unamanged table in databricks for csv file

RajibRajib_Mand — Thu, 24 Mar 2022 16:50:55 GMT

Thanks @Hubert Dudek for your response. I tried with these options. Unfortunately it did not work

Re: Unicode field separator to create unamanged table in databricks for csv file

jose_gonzalez — Mon, 25 Apr 2022 21:33:25 GMT

Have you try to use "multiline" ? also try to read it using CSV to validate, then you can create the table, after you validate the data is correct.

For example:

df = spark.read

.option("header",true)

.option("multiLine",true)

.option("escape","_especial_value_")

.csv("path_to_CSV_data")

Re: Unicode field separator to create unamanged table in databricks for csv file

jose_gonzalez — Tue, 10 May 2022 17:17:20 GMT

Hi @Rajib Rajib Mandal ,

Just a friendly follow-up. Do you still need help with this questions or not anymore? did any of our responses help to resolved the issue? if yes, please mark it as best.

Re: Unicode field separator to create unamanged table in databricks for csv file

RajibRajib_Mand — Wed, 11 May 2022 03:29:09 GMT

Hi @Jose Gonzalez ,

Yes .I still need help .No one response yet.

Regards,

Rajib

Re: Unicode field separator to create unamanged table in databricks for csv file

jose_gonzalez — Mon, 13 Jun 2022 21:16:40 GMT

Hi @Rajib Rajib Mandal,

What have you tried so far? It will hep us to narrow down the scope. Please share as much details as possible.