topic need to remove doubledagger delimiter from a csv using databricks in Data Engineering

need to remove doubledagger delimiter from a csv using databricks

shamly — Tue, 29 Nov 2022 19:41:24 GMT

My csv data looks like this

‡‡companyId‡‡,‡‡empId‡‡,‡‡regionId‡‡,‡‡companyVersion‡‡,‡‡Question‡‡

I tried this code

dff = spark.read.option("header", "true").option("inferSchema", "true").option("delimiter", "‡,").csv(f"/mnt/data/path/datafile.csv")

But I am getting space after each characters in between my results

�� ! ! c o m p a n y I d ! ! , ! ! e m p I d ! ! , ! ! r e g i o n I d ! ! , ! !

please help

Re: need to remove doubledagger delimiter from a csv using databricks

Hubert-Dudek — Tue, 29 Nov 2022 20:04:18 GMT

You can include a sample of your CSV as an attachment in CSV format would be easier.

Maybe it is enough to add space in delimiter .option("delimiter", "‡, ").

Another option is to use external applications to purge chars from CSV.

We can also load the whole line into a dataframe and split it using spark SQL string functions https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/functions.html#string-functions

Re: need to remove doubledagger delimiter from a csv using databricks

UmaMahesh1 — Tue, 29 Nov 2022 20:30:49 GMT

Hi @shamly pt

I took a bit another approach since I guess no one would be sure of the the encoding of the data you showed.

Sample data I took :

‡‡companyId‡‡,‡‡empId‡‡,‡‡regionId‡‡,‡‡companyVersion‡‡,‡‡Question‡‡

‡‡1‡‡,‡‡121212‡‡,‡‡R‡‡,‡‡1.0A‡‡,‡‡NA‡‡

My approach:

First reading that data as is turning off header and sep as ','. Just renamed _c0 to col1 for visual purpose. Then created a split column, separated the values and regex replaced the data. Finally filtered out the row which contained header as I already aliased the dataframe.

dff = spark.read.option("header", "false").option("inferSchema", "true").option("sep",",").csv("/FileStore/tables/Book1.csv").withColumnRenamed("_c0", "col1")
 
split_col = pyspark.sql.functions.split(dff['col1'], ',')
 
df2 = dff.select(regexp_replace(split_col.getItem(0), "[^0-9a-zA-Z_\-]+", "").alias('companyId'),\
                 regexp_replace(split_col.getItem(1), "[^0-9a-zA-Z_\-]+", "").alias('empId'), \
                regexp_replace(split_col.getItem(2), "[^0-9a-zA-Z_\-]+", "").alias('regionId'), \
                regexp_replace(split_col.getItem(3), "[^0-9a-zA-Z_\-]+", "").alias('companyVersion'), \
                regexp_replace(split_col.getItem(4), "[^0-9a-zA-Z_\-]+", "").alias('Question')) \
 
output = df2.where(df2.companyId != 'companyId')

My output:

Hope this helps...Cheers...

Re: need to remove doubledagger delimiter from a csv using databricks

shamly — Wed, 30 Nov 2022 06:48:23 GMT

Dear @Uma Maheswara Rao Desula Thanks for your help. I am able to split into columns.I have almost 90 columns. So, is there any way that I can automate?

I tried below code, but it is not giving clean seperated columns as ur code.

dff = spark.read.option("header", "true").option("inferSchema", "true").option("delimiter", "‡‡,‡‡").csv(filepath)

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)

Re: need to remove doubledagger delimiter from a csv using databricks

UmaMahesh1 — Wed, 30 Nov 2022 09:19:11 GMT

Hi @shamly pt

May I know what was the data mismatch issue you were facing using the previous code ?

Re: need to remove doubledagger delimiter from a csv using databricks

shamly — Wed, 30 Nov 2022 10:23:41 GMT

Hi @Uma Maheswara Rao Desula There was no data match issues. It was my mistake in validating data. Data is coming perfectly. The problem is I have 90 columns. So is there any way to reduce manual effort something like below ?

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)

Re: need to remove doubledagger delimiter from a csv using databricks

UmaMahesh1 — Wed, 30 Nov 2022 11:04:04 GMT

Hi @shamly pt ,

As an automated way if you don't know the schema beforehand would look something like this...

dff_1 = spark.read.option("header", "false").option("inferSchema", "true").option("sep",",").csv("/FileStore/tables/Book1.csv").withColumnRenamed("_c0", "col1")
 
split_col = pyspark.sql.functions.split(dff_1['col1'], ',')
 
# Building the header field names
 
header_uncleaned = (spark.read.option("header", "true").option("inferSchema", "true").option("sep",",").csv("/FileStore/tables/Book1.csv").columns[0]).split(",")
header = []
for i in header_uncleaned:
  header.append(''.join(e for e in i if e.isalnum()))
 
# Looping over the column names and populating data
dff_1 = df1
for i in range(len(header)):
  print(i)
  df1 = df1.withColumn(header[i], regexp_replace(split_col.getItem(i), "[^0-9a-zA-Z_\-]+", ""))
df1 = df1.drop("col1").filter(col("companyId") != "companyId")
 
display(df1)

Re: need to remove doubledagger delimiter from a csv using databricks

shamly — Thu, 01 Dec 2022 17:57:46 GMT

Hi @Uma Maheswara Rao Desula

I have written this code, as I have many files in many folders in same location and everything is UTF-16. This is giving me proper result as below

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

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

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

.option('multiline', 'true') \

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

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

display(dff)

‡‡CompanyId Companyname CountryId‡‡

‡‡1234 abc cn‡‡

‡‡2345 def us‡‡

‡‡3457 ghi sy‡‡

‡‡7564 lmn uk‡‡

Now, I want to remove the start and end double daggers and I wrote below code and it is giving me error "IndentationError: expected an indented block"

from pyspark.sql.functions import regexp_replace

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)

error "IndentationError: expected an indented block"

Re: need to remove doubledagger delimiter from a csv using databricks

UmaMahesh1 — Thu, 01 Dec 2022 18:04:06 GMT

Hi @shamly pt

It's a indentation error. Check if you are following proper indentation.

I guess your error is at if columnLabel != newColumnLabel:

dff = dff.drop(columnLabel).

Just hit a Tab or give indentation spaces before dff = dff.drop(columnLabel)

cheers..

Re: need to remove doubledagger delimiter from a csv using databricks

shamly — Thu, 01 Dec 2022 18:56:44 GMT

Thankyou it worked 🙂