topic Re: How to replace LF and replace with ' ' in csv UTF-16 encoded? in Data Engineering

How to replace LF and replace with ' ' in csv UTF-16 encoded?

shamly — Mon, 09 Jan 2023 19:48:27 GMT

I have tried several code and nothing worked. An extra space or line LF is going to next row in my output. All rows are ending in CRLF, but some rows end in LF and while reading the csv, it is not giving correct output. My csv have double dagger as delimitter

csv looks like this

‡‡Id‡‡,‡‡Version‡‡,‡‡Questionnaire‡‡,‡‡Date‡‡

‡‡123456‡‡,‡‡Version2‡‡,‡‡All questions have been answered accurately

and the guidance in the questionnaire was understood and followed‡‡,‡‡2010-12-16 00:01:48.020000000‡‡

I tried below code

dff = spark.read.option("header", "true") \

.option("inferSchema", "true") \

.option('encoding', 'UTF-16') \

.option("delimiter", "‡‡,‡‡") \

.option("multiLine", True) \

.csv("/mnt/path/data.csv")

dffs_headers = dff.dtypes

display(dff)

for i in dffs_headers:

columnLabel = i[0]

newColumnLabel = columnLabel.replace('‡‡','').replace('‡‡','')

dff=dff.withColumn(newColumnLabel,regexp_replace(columnLabel,'^\\‡‡|\\‡‡$',''))

if columnLabel != newColumnLabel:

dff = dff.drop(columnLabel)

display(dff)

Can I use regex replace .regexp_replace('?<!\r)\n','') but how and where ?

Please help @ArunKumar-Databricks @Gustavo Barreto @ANUJ GARG @

Re: How to replace LF and replace with ' ' in csv UTF-16 encoded?

RaghavendraY — Tue, 10 Jan 2023 12:48:28 GMT

Can you share a sample file with rows ending in CRLF, and in LF

Re: How to replace LF and replace with ' ' in csv UTF-16 encoded?

Chaitanya_Raju — Tue, 10 Jan 2023 13:59:55 GMT

Hi @shamly pt ,

Can you please share the sample file with the ***** data and also the expected output, so that we can try it at our end and let you know.

Happy Learning!!

Re: How to replace LF and replace with ' ' in csv UTF-16 encoded?

sher — Wed, 11 Jan 2023 17:37:23 GMT

import org.apache.spark.sql.SQLContext
val sqlContext = new SQLContext(sc);
 
val df = sqlContext.read.format("csv")
            .option("header", "true")
            .option("delimiter", "your delimiter")
            .option("inferSchema",true")
            .load("csv file")

can you try this. if this not work

then you need to read the file in RDD and convert to df and write back to CSV

CSV --> RDD --> DF --> FINAL_OUTPUT format

Re: How to replace LF and replace with ' ' in csv UTF-16 encoded?

sher — Wed, 11 Jan 2023 17:39:57 GMT

val df = spark.read.format("csv")
              .option("header",true)
                .option("sep","||")
                  .load("file load")
display(df)  
 
try this