Dear Friends,
I have a csv and it 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‡‡
There is an extra ENTER line "and the guidance in the questionnaire was understood and followed" this part is coming as a new line in the csv. Source file encoding is UTF-16 LE BOM.
At the end of every line, I have CRLF and at the end of every ENTER extra line, I have LF
I should mention in my code something like lineSep \r\n ,but how?
I wrote below code to read this csv
dff = spark.read.option("header", "true") \
.option("inferSchema", "true") \
.option('multiline', 'true') \
.option('encoding', 'UTF-16') \
.option("delimiter", "‡‡,‡‡") \
.csv("/mnt/path/data.csv")
dffs_headers = dff.dtypes
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)
But, in the result is not correct as for the given Id, Questionnaire column data is breaking after "All questions have been answered accurately" and displayed in the next row. I want the entire textbetween the doubledagger "‡‡,‡‡" to be read as one row, even if there is any extra ENTER line.
Please help friends @Aviral Bhardwaj
@DataBricksHelp232 @Rahul@Databricks @Uma Dacharla @Uma Maheswara Rao Desula