Forum Discussion

Theo86's avatar
Theo86
Frequent Visitor
7 months ago
Solved

Lakehouse - promote csv files to Tables using For Loop

I'm pretty new to Notebooks ... I trying to promote the 5 csv files in the Lakehouse file directory Lakehouse/Files/Imported to Tables in the Lakehouse   Ideally I would like to use a For Loop (t...
notebook
  • stoic-harsh's avatar
    stoic-harsh
    7 months ago

    Hi Theo86 ,

    You can do like this:

    #Read Files inside Directory
files = mssparkutils.fs.ls("Files/Imported/")

# Each item contains file name and path
for file in files:
   # Skip folders inside
   if not file.isDir:
      table_name = file.name.replace(".csv","")
      df = spark.read.option("header", "true").option("inferSchema", "true").csv(file.path)
      df.write.mode("overwrite").option("overwriteSchema", "true").saveAsTable(f"<schema>.{table_name}")

    You can add a few print statement to keep track of the flow while the code runs. Also you can add try, except block for error-handling.

deborshi_nag's avatar
deborshi_nag
Icon for Super User rankSuper User
7 months ago

Hello Theo86 

 

You don't need a For Loop to load all the csv files, you can use wildcards. Please see a code snippet below. 

 

from pyspark.sql.functions import input_file_name, current_date, lit

# 1) Read multiple CSVs with wildcard
df = (spark.read
      .option("header", "true")
      .option("delimiter", ",")
      .option("quote", '"')
      .option("escape", '"')
      .option("inferSchema", "true")
      .csv("Files/Imported/sen_secondary_need_*.csv"))

# 2) Add Bronze lineage/metadata columns
df_bronze = (df
             .withColumn("ingest_date", current_date())
             .withColumn("source_file", input_file_name()))

# 3) Write to Lakehouse Tables (Delta)
spark.sql("CREATE DATABASE IF NOT EXISTS bronze")
(df_bronze
 .write
 .format("delta")
 .mode("append")
 .option("mergeSchema", "true")  # tolerates new columns over time
 .partitionBy("ingest_date")     # optional but recommended
 .saveAsTable("bronze.sen_secondary_need"))

This is a code centric approach. 

 

You could also follow a no-code approach using the Lakeouse Explorer. 

Right-click the Imported folder > Create table (auto-detect schema) > writes to /Tables.