topic Re: Skip number of rows when reading CSV files in Data Engineering

Skip number of rows when reading CSV files

THIAM_HUATTAN — Thu, 16 May 2019 08:49:40 GMT

staticDataFrame = spark.read.format("csv")\ .option("header", "true").option("inferSchema", "true").load("/FileStore/tables/Consumption_2019/*.csv")

when above, I need an option to skip say first 4 lines on each CSV file, How do I do that?

Re: Skip number of rows when reading CSV files

mathan_pillai — Thu, 16 May 2019 12:15:48 GMT

Hi @THIAM HUAT TAN

I don't think there is a way to specify that when reading it. However, after reading it, you can create monotonically increasing id (new column), and then filter for those ids that are greater than 4.

Alternatively you can apply take(4) and create rdd out of it. Then apply subtract transformation between the original rdd and the small rdd.

please let us know whether it works for you

Thanks

Re: Skip number of rows when reading CSV files

THIAM_HUATTAN — Fri, 17 May 2019 03:47:26 GMT

databricks-data.png

My sample data is as above, and I need the data from Row 6 onwards, with Row 6 as the header. Row 1 to Row 5 are redundant. Not sure how to implement your suggestion. Thanks.

Re: Skip number of rows when reading CSV files

AnkitDwivedi — Mon, 20 Apr 2020 11:39:25 GMT

I also have same issue. Is it resolved ?

What is the resolution ?

Please advise. Thanks

Re: Skip number of rows when reading CSV files

tony — Wed, 03 Jun 2020 16:26:21 GMT

any resolution?

Re: Skip number of rows when reading CSV files

FabioKfouri — Sun, 21 Jun 2020 15:59:21 GMT

I resolved it using the function monotonically_increasing_id and a little logic to set the Column Name.

To do this is necessary Java 1.8, because raise a error on function 'collect()' in Java 11.

df = df.withColumn('index', F.monotonically_increasing_id())
cols = df.columns
values = df.filter('index = 0').collect()  # here define the skipped lines
for i in range(len(cols)):
    if cols[i] != 'index':
        df = df.select(df.columns).withColumnRenamed(cols[i], values[0][i])

Re: Skip number of rows when reading CSV files

mstuder — Tue, 11 May 2021 12:23:47 GMT

According to the docs of

spark.read.csv(...)

the

path

argument can be an RDD of strings:

path : str or list
     string, or list of strings, for input path(s), or RDD of Strings storing CSV rows.

With that, you may use

spark.sparkContext.textFile(...)

in combination with

zipWithIndex(...)

to perform the necessary row filtering. Putting things together this may look as follows:

n_skip_rows = ?
row_rdd = spark.sparkContext
    .textFile(your_csv_file) \
    .zipWithIndex() \
    .filter(lambda row: row[1] >= n_skip_rows) \
    .map(lambda row: row[0])
df = spark_session.read.csv(row_rdd, ...)

Hope that helps.

Re: Skip number of rows when reading CSV files

User16844409535 — Mon, 28 Nov 2022 15:54:48 GMT

You can provide the `skipRows` option while reading.

spark.read.format("csv").option("skipRows", 4).load("<filepath>")

Re: Skip number of rows when reading CSV files

Michael_Appiah — Thu, 21 Sep 2023 07:48:58 GMT

The option...

.option("skipRows", <number of rows to skip>)

...works for me as well. However, I am surprised that the official Spark doc does not list it as a CSV Data Source Option: https://spark.apache.org/docs/latest/sql-data-sources-csv.html#data-source-option

@User16844409535 Did you find documentation on that somewhere else?