CSV首行元数据提取、新列创建及动态文件名生成技术需求

Got it, let's break down how to build this data flow step by step to handle your CSV file with that specific Metadata structure.

Step 1: Extract Metadata from the CSV #

First, your CSV has Metadata locked in the first cell of the first row—so we need to grab that value separately before parsing the actual data table (headers start at row 2, data at row 3).

For example, if using Python/Pandas or pipeline tools like Azure Data Factory:

• Read the very first row as a standalone string to capture the Metadata.
• Then load the rest of the CSV starting from row 2 to get your main data table.

Your Metadata format is:

String1 - String2 - INT1/INT2/INT3 / String3 - String4 - String5 (String6) String7

Step 2: Split Metadata into Target Fields #

We’ll split this string using delimiters /, -, (, and ) to pull out String3, String6, and String7—the values we need for new columns and the filename.

Here's the split logic, with a Python example for clarity:

1. First split the full Metadata string by /, then take the third segment (after trimming spaces) — that’s the part containing our target values.
2. Split that segment by - to isolate String3 (first part) and the chunk holding String6/String7 (third part).
3. Split the final chunk using ( and ) to extract String6 and String7, trimming any leftover spaces.

# Example Metadata string
metadata = "Sample1 - Sample2 - 100/200/300 - RegionX - DeptY - ProdZ (EU) 2024Q3"

# Step 1: Split by / and get the relevant section
split_slash = [s.strip() for s in metadata.split('/')]
relevant_section = split_slash[2]  # Target segment is index 2

# Step 2: Split by " - " to get String3
split_dash = [s.strip() for s in relevant_section.split(' - ')]
string3 = split_dash[0]

# Step 3: Extract String6 and String7
final_chunk = split_dash[2]
pre_string6, post_string6 = final_chunk.split('(')
string6, string7 = post_string6.split(')')
string6 = string6.strip()
string7 = string7.strip()

# Result: string3 = "RegionX", string6 = "EU", string7 = "2024Q3"

Step 3: Add Derived Columns to the Main Data #

Once you have string3, string6, string7, add these as new columns to your main data table. Since Metadata applies to the entire CSV, these values will be static across all rows.

In Pandas, this is simple:

# Assume main_df is your loaded data table from rows 2+
main_df['String3'] = string3
main_df['String6'] = string6
main_df['String7'] = string7

In pipeline tools (like ADF or NiFi), use a "Derived Column" transformation to inject these static values as new columns.

Step 4: Generate Dynamic Sink Filename #

Build the required filename using the extracted values: String6.String7_String3.csv.

In Python:

sink_filename = f"{string6}.{string7}_{string3}.csv"
# Example output: EU.2024Q3_RegionX.csv

For pipeline tools, use dynamic expressions to concatenate the fields in this format when configuring your sink.

Step 5: Final Output #

Your end CSV will include:

• Original headers from row 2 of the input
• All data rows starting from row 3
• The three new columns: String3, String6, String7
• Saved with the dynamic filename we generated

内容的提问来源于stack exchange，提问作者DerKamiKatze