import pandas as pd
import sqlite3
import re
import os
from io import StringIO
# Simulated CSV file contents (replace with uploaded file content in actual
compiler)
file_data = {
"CUST_MSTR_20191112.csv": "cust_id,name\n1,Alice\n2,Bob",
"master_child_export-[Link]": "order_id,product\n101,Pencil\n102,Pen",
"H_ECOM_ORDER.csv": "ecom_id,amount\n9001,150.0\n9002,200.0"
}
# Setup in-memory SQLite DB
conn = [Link](":memory:")
cursor = [Link]()
def extract_date_from_filename(filename):
match = [Link](r'\d{8}', filename)
if match:
date_str = [Link]()
return pd.to_datetime(date_str, format='%Y%m%d')
return None
for filename, content in file_data.items():
df = pd.read_csv(StringIO(content))
if [Link]("CUST_MSTR_"):
date = extract_date_from_filename(filename)
df['date'] = [Link]('%Y-%m-%d') if date else None
df.to_sql('CUST_MSTR', conn, if_exists='replace', index=False)
print(f"Loaded {filename} into CUST_MSTR")
elif [Link]("master_child_export-"):
date = extract_date_from_filename(filename)
if date:
df['date'] = [Link]('%Y-%m-%d')
df['date_key'] = [Link]('%Y%m%d')
df.to_sql('master_child', conn, if_exists='replace', index=False)
print(f"Loaded {filename} into master_child")
elif [Link]("H_ECOM_ORDER"):
df.to_sql('H_ECOM_Orders', conn, if_exists='replace', index=False)
print(f"Loaded {filename} into H_ECOM_Orders")
else:
print(f"Skipped unrecognized file: {filename}")
# Example: Query one table to verify
print("\nSample Query from CUST_MSTR:")
print(pd.read_sql_query("SELECT * FROM CUST_MSTR", conn))
