Project 1 — Beginner

Excel Data Cleaner Bot

Sales team roz 12 Excel files bhejti hai — mismatched headers, extra spaces, missing phone numbers. 2 ghante manual cleaning. Aaj is bot ko banayenge jo ye sab 2 minute me kar dega.

Project kya karega?

Step-by-step build

Studio me naya Sequential Workflow banao. Activity tree aisa hogi:

Main.xaml (Sequence)
 |
 +-- Try Catch
 |    |
 |    +-- Sequence: Process Files
 |    |    +-- Assign  in_Folder = "C:\Input\"
 |    |    +-- Assign  out_File  = "C:\Output\Consolidated_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"
 |    |    +-- For Each File: in_Folder + "*.xlsx"  ->  currentFile
 |    |    |    +-- Excel Application Scope (currentFile)
 |    |    |    |    +-- Read Range  "Sheet1"  ->  dtBranch
 |    |    |    |    +-- For Each Row  dtBranch
 |    |    |    |         +-- Assign  row("Name") = row("Name").ToString.Trim()
 |    |    |    |         +-- If  String.IsNullOrWhiteSpace(row("Phone"))
 |    |    |    |              +-- Assign  row("Phone") = "MISSING"
 |    |    |    +-- Merge Data Table: dtAll <- dtBranch
 |    |    +-- Write Range dtAll to out_File (Add Header)
 |    |    +-- Excel Application Scope (out_File)
 |    |         +-- Invoke Code: summary formulas (total rows, MISSING count)
 |    +-- Catch (Exception)
 |         +-- Log Message  LogLevel.Error  ex.Message
 |         +-- Message Box  "Processing failed - check logs"

Har file ek iteration me process hoti hai. Merge Data Table sab branches ko ek DataTable me jodta hai — pehli file ke headers hi master maan lo.

Config & variables

Hardcoded paths kabhi mat likho. Variables panel me ye types rakho:

Variable          Type          Scope
------          ----          -----
in_Folder       String        Sequence
out_File        String        Sequence
currentFile     FileInfo      For Each File
dtBranch        DataTable     For Each File
dtAll           DataTable     Process Files
row             DataRow       For Each Row

# Config.xlsx (pehle hi banao - baad me sab bots me chahiye)
Key            Value
INPUT_FOLDER   C:\Input\
OUTPUT_FOLDER  C:\Output\
MAX_ROWS       50000

Variables ka naam prefix rakho — in_ (input), out_ (output), io_ (both). Enterprise projects me naming convention review me check hota hai.

Error handling

Try Catch ke alawa ek zaroori check — header mismatch. Agar kisi file me "Phone" column hi nahi, to row("Phone") pe exception aayega:

If  dtBranch.Columns.Contains("Phone") = False
     +-- Log Message  LogLevel.Warning  "Phone column missing: " + currentFile.Name
     +-- Continue For Each  (skip this file, process next)

• Corrupt Excel: Excel Application Scope throw karega — Catch me file naam log karo.
• File already open hai: Windows lock exception — retry mat karo, log karke skip.
• Dispose: Excel Application Scope apne aap instance band karta hai — "Kill Process" activity ka use tab karo jab scope hang ho jaye.

Enterprise tips

• Orchestrator Asset me INPUT_FOLDER rakho — bot ko bina redeploy folder change kar sakte ho.
• Input folder me file aane ka pattern rakho: files "Processed" subfolder me move karo — same file dobara process nahi hogi.
• Summary report me timestamp rakho — audit trail me kaunse din ki report hai dikhega.
• Naming: Prefix_Scope_Description — jaise FIN_ExcelCleaner_Main. Har company ka prefix alag hota hai.

Exercise

Common mistakes

• Hardcoded paths: "C:\Users\Rahul\Input\" — bot kisi dusre machine pe toot jayega. Config me rakho.
• Excel instance leak: Scope ke andar exception aaye to Excel peeche reh jata hai — Headless mode on rakho ya Finally block me Kill Process.
• Row-by-row activity use: 50k rows pe 50k Type Into = 2 ghante. DataTable operations (For Each Row + Assign) seconds me.
• No logging: Bot chal gaya lekin kya kiya pata nahi. Har step pe Log Message — production me Insights se debug hoga.