Examples: Input File Name

Purpose: Have extra column with values of original file paths

Extra column with original file path

from pyspark.sql.functions import input_file_name

ROOT="/<please-change>/to/your-path"

df = spark.read.format("excel") \
   .option("header", True) \
   .option("inferSchema", True) \
   .load(f"{ROOT}/src/test/resources/spreadsheets/ca_dataset/2019/*/*.xlsx") \
   .withColumn("file_name", input_file_name())
df.show(10)

Result

+---+-----+-----------+-----------------+----------------+--------------+--------------+--------------+--------------+--------------------+
|Day|Month|Customer ID|    Customer Name|Standard Package|Extra Option 1|Extra Option 2|Extra Option 3|         Staff|           file_name|
+---+-----+-----------+-----------------+----------------+--------------+--------------+--------------+--------------+--------------------+
|  1|   11|      CA767|   Vũ Phương Thảo|           14000|          null|          null|          null|   Teresa Teng|file:///home/quan...|
|  2|   11|      CA768|      Lê Thị Trâm|            null|          null|          2000|          null|Marilyn Monroe|file:///home/quan...|
|  2|   11|      CA769|         Lê Trung|            null|          null|          1200|          null|   Teresa Teng|file:///home/quan...|
|  3|   11|      CA770| Nguyễn Thảo Hiền|            null|          null|          1700|          null|   Teresa Teng|file:///home/quan...|
|  3|   11|      CA771|   Nguyễn Thu Huệ|            null|          null|          1800|          null|   Teresa Teng|file:///home/quan...|
|  3|   11|      CA772|      Vũ Cẩm Linh|            null|          null|           200|          null|   Teresa Teng|file:///home/quan...|
|  3|   11|      CA773|    Phạm Kim Ngân|           12000|          null|          2000|          null|Marilyn Monroe|file:///home/quan...|
|  4|   11|      CA774|Hoàng Thj Mai Anh|            5000|          null|          null|          null|Marilyn Monroe|file:///home/quan...|
|  4|   11|      CA775|   Đỗ Phương Anh |            null|          null|           800|          null|Marilyn Monroe|file:///home/quan...|
|  5|   11|      CA776|      Ngô Mai Quý|            null|          null|          1500|          null|   Teresa Teng|file:///home/quan...|
+---+-----+-----------+-----------------+----------------+--------------+--------------+--------------+--------------+--------------------+
only showing top 10 rows

Extract part of original file path with regular expression

from pyspark.sql.functions import regexp_extract

df.withColumn("file_name", regexp_extract('file_name', '.*/(.*)\.xlsx$', 1)) \
   .show(5)

Got

+---+-----+-----------+----------------+----------------+--------------+--------------+--------------+--------------+---------+
|Day|Month|Customer ID|   Customer Name|Standard Package|Extra Option 1|Extra Option 2|Extra Option 3|         Staff|file_name|
+---+-----+-----------+----------------+----------------+--------------+--------------+--------------+--------------+---------+
|  1|   11|      CA767|  Vũ Phương Thảo|           14000|          null|          null|          null|   Teresa Teng|    ca_11|
|  2|   11|      CA768|     Lê Thị Trâm|            null|          null|          2000|          null|Marilyn Monroe|    ca_11|
|  2|   11|      CA769|        Lê Trung|            null|          null|          1200|          null|   Teresa Teng|    ca_11|
|  3|   11|      CA770|Nguyễn Thảo Hiền|            null|          null|          1700|          null|   Teresa Teng|    ca_11|
|  3|   11|      CA771|  Nguyễn Thu Huệ|            null|          null|          1800|          null|   Teresa Teng|    ca_11|
+---+-----+-----------+----------------+----------------+--------------+--------------+--------------+--------------+---------+
only showing top 5 rows

References

• Pyspark: input_file_name
• Pyspark: regexp_extract
• #52 input_file_name returns empty string