topic Re: How to read file in pyspark with “]|[” delimiter in Data Engineering

How to read file in pyspark with “]|[” delimiter

lambarc — Wed, 18 Jan 2017 21:14:50 GMT

The data looks like this:

pageId]|[page]|[Position]|[sysId]|[carId 0005]|[bmw]|[south]|[AD6]|[OP4

There are atleast 50 columns and millions of rows.

I did try to use below code to read:

dff = sqlContext.read.format("com.databricks.spark.csv").option("header", "true").option("inferSchema", "true").option("delimiter", "]|[").load(trainingdata+"part-00000")

it gives me following error:

IllegalArgumentException: u'Delimiter cannot be more than one character: ]|['

Re: How to read file in pyspark with “]|[” delimiter

SamKlingner — Thu, 19 Jan 2017 17:25:33 GMT

Sorry if this notifies everyone again - struggling with the text editor here!

I'm not sure of a workaround for directly splitting with more than one character. 2 ways around this that I can see:

The first approach would be to split using "|" and then replace the angle brackets:

dff = sqlContext.read.format("com.databricks.spark.csv").option("header", "true").option("inferSchema", "true").option("delimiter", "|").load(trainingdata+"part-00000")

from pyspark.sql.functions import regexp_replace dff = dff.withColumn('pageId', regexp_replace('[pageId]','[[]]',''))....other columns....

Given that you have 50 columns you might want to loop through this:

dffs_headers = dff.dtypes
for i in dffs_headers: 
    newColumnLabel = i[0].replace('[','').replace(']','') 
    dff = dff.withColumn(newColumnLabel, regexp_replace(i[0],'[[]]','')).drop(i[0])

You'll still need to build a function or add cases to this loop to correctly cast each column though. The second way would be go via RDD:

dff = sc.textfile(trainingdata+"part-00000").map(lambda x: x.replace('[','').replace(']','').split('|')).toDF()

But that still leaves you with the casting problem Hope that helps!

Re: How to read file in pyspark with “]|[” delimiter

SamKlingner — Thu, 19 Jan 2017 17:26:21 GMT

The code block button just isn't playing nice, sorry.

Re: How to read file in pyspark with “]|[” delimiter

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

val df = spark.read.format("csv")

.option("header",true)

.option("sep","||")

.load("file load")

display(df)

try this

Re: How to read file in pyspark with “]|[” delimiter

rohit199912 — Wed, 01 Feb 2023 06:59:58 GMT

you might also try the blow option.

1). Use a different file format: You can try using a different file format that supports multi-character delimiters, such as text JSON.

2). Use a custom Row class: You can write a custom Row class to parse the multi-character delimiter yourself, and then use the spark.read.text API to read the file as text. You will then need to apply the custom Row class to each line in the text file to extract the values

Re: How to read file in pyspark with “]|[” delimiter

Rajeev_Basu — Fri, 03 Feb 2023 11:39:00 GMT

this one works. thanks.

Re: How to read file in pyspark with “]|[” delimiter

Meghala — Sat, 04 Feb 2023 12:37:14 GMT

Might be usefull

Re: How to read file in pyspark with “]|[” delimiter

Manoj12421 — Sat, 04 Feb 2023 14:12:59 GMT

Yes this one is useful but what if we need to use it in CSV format only than is there any other query if you can share. @ROHIT AGARWAL