SQL Server中基于CSV新旧物料映射更新新物料库存数量

Let's work through this material replacement task step by step. First, let's recap the clear requirements we have, plus a note on the ambiguous rule, then I'll share a practical Python implementation that's easy to adjust as you finalize additional requirements.

Known Requirements #

• We have a CSV file with two columns: OldItem (integer IDs of legacy materials) and NewItem (integer IDs of replacement materials), with ~1000 rows. Example data:

  OldItem	NewItem
  1021669	1167467
  1021680	1167468
  1021712	1167466
  1049043	1000062

• Stock assignment rules (current explicit ones):
  • Assign the current stock quantity of the first OldItem to the first NewItem
  • Assign the current stock quantity of the second OldItem to the third OldItem (note: this rule seems potentially mistyped—if it was meant to target a NewItem instead, the code below can be adjusted easily)

Practical Implementation (Python) #

Assuming you have a way to fetch current stock (e.g., a separate CSV, a database query result stored as a dictionary), here's how to process the assignment:

import csv

# Step 1: Load current inventory (replace this with your actual data source)
# Example: key = material ID, value = current stock quantity
current_inventory = {
    1021669: 45,
    1021680: 28,
    1021712: 15,
    1049043: 70,
    1167467: 0,
    1167468: 0,
    1167466: 0,
    1000062: 0
}

# Step 2: Read the replacement CSV
replacement_pairs = []
with open('material_replacement.csv', 'r', newline='') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        # Convert IDs to integers
        replacement_pairs.append({
            'OldItem': int(row['OldItem']),
            'NewItem': int(row['NewItem'])
        })

# Step 3: Apply the explicit stock assignment rules
if len(replacement_pairs) >= 1:
    # Rule 1: First OldItem's stock -> First NewItem
    first_old = replacement_pairs[0]['OldItem']
    first_new = replacement_pairs[0]['NewItem']
    current_inventory[first_new] = current_inventory.get(first_old, 0)
    print(f"Assigned {current_inventory[first_old]} stock from {first_old} to {first_new}")

if len(replacement_pairs) >= 3:
    # Rule 2: Second OldItem's stock -> Third OldItem (adjust target if needed)
    second_old = replacement_pairs[1]['OldItem']
    third_old = replacement_pairs[2]['OldItem']
    current_inventory[third_old] = current_inventory.get(second_old, 0)
    print(f"Assigned {current_inventory[second_old]} stock from {second_old} to {third_old}")

# Step 4: Extend with additional rules here as you finalize requirements
# Example: For remaining rows, assign OldItem stock to corresponding NewItem
# for pair in replacement_pairs[3:]:
#     current_inventory[pair['NewItem']] = current_inventory.get(pair['OldItem'], 0)

# Step 5: Save updated inventory (optional)
with open('updated_inventory.csv', 'w', newline='') as csvfile:
    fieldnames = ['MaterialID', 'StockQuantity']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    for mat_id, stock in current_inventory.items():
        writer.writerow({'MaterialID': mat_id, 'StockQuantity': stock})

Key Notes #

• The code uses Python's built-in csv module, so no extra dependencies are needed.
• The current_inventory dictionary is just an example—replace it with your actual inventory data source (e.g., reading from another CSV or querying a database).
• If the second rule was a typo (e.g., meant to assign to the second NewItem or third NewItem), just swap the target variable in the code (e.g., use replacement_pairs[1]['NewItem'] or replacement_pairs[2]['NewItem'] instead of third_old).
• Once you have the full set of assignment rules, you can add them to the loop in Step 4 to process all 1000 rows efficiently.

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