Azure Blob双CSV文件C#解析：基于点符号映射生成嵌套JSON

Alright, let's tackle this problem step by step. You've got two CSVs in Azure Blob Storage—one small mapping file, one large data file—and you need to stream the large one to build nested JSON based on the mappings. Here's a practical, memory-efficient approach using C#:

Step 1: Install Required NuGet Packages #

First, grab the packages we'll need for Blob access, CSV parsing, and JSON handling:

• Azure.Storage.Blobs (to interact with Azure Blob Storage)
• CsvHelper (for easy CSV parsing, even with streams)
• Newtonsoft.Json (to dynamically build nested JSON objects)

You can install them via the Package Manager Console:

Install-Package Azure.Storage.Blobs
Install-Package CsvHelper
Install-Package Newtonsoft.Json

Step 2: Read the Mapping CSV #

First, we'll load the small mapping file into a dictionary, where each key is a CSV field name, and the value is the corresponding nested API path (like apiField1.subfield1).

using Azure.Storage.Blobs;
using CsvHelper;
using Newtonsoft.Json.Linq;
using System.Globalization;
using System.IO;

// Initialize Blob Service Client with your connection string
var blobServiceClient = new BlobServiceClient("your-azure-blob-connection-string");
var containerClient = blobServiceClient.GetBlobContainerClient("your-container-name");

// Load mapping CSV into a dictionary
Dictionary<string, string> csvToApiMapping = new();
var mappingBlobClient = containerClient.GetBlobClient("mapping.csv");

using (var blobStream = await mappingBlobClient.OpenReadAsync())
using (var streamReader = new StreamReader(blobStream))
using (var csvReader = new CsvReader(streamReader, CultureInfo.InvariantCulture))
{
    // The mapping CSV has no header—just pairs of csvField, apiPath per line
    while (await csvReader.ReadAsync())
    {
        string csvField = csvReader.GetField(0);
        string apiPath = csvReader.GetField(1);
        csvToApiMapping[csvField] = apiPath;
    }
}

Step 3: Stream the Large CSV & Build Nested JSON #

For the large CSV, we'll use streaming to avoid loading the entire file into memory. We'll process each row one by one, use the mapping to build the nested JSON structure, and handle it (e.g., send to an API, write to a file) as we go.

var largeCsvBlobClient = containerClient.GetBlobClient("large-data.csv");

using (var blobStream = await largeCsvBlobClient.OpenReadAsync())
using (var streamReader = new StreamReader(blobStream))
using (var csvReader = new CsvReader(streamReader, CultureInfo.InvariantCulture))
{
    // Read the CSV header first
    await csvReader.ReadAsync();
    csvReader.ReadHeader();

    // Process each row sequentially
    while (await csvReader.ReadAsync())
    {
        JObject jsonOutput = new JObject();

        foreach (string csvHeader in csvReader.HeaderRecord)
        {
            // Skip CSV fields that aren't in our mapping
            if (!csvToApiMapping.TryGetValue(csvHeader, out string apiPath))
                continue;

            string fieldValue = csvReader.GetField(csvHeader);
            string[] pathSegments = apiPath.Split('.');
            JToken currentNode = jsonOutput;

            // Traverse or build the nested JSON structure
            for (int i = 0; i < pathSegments.Length; i++)
            {
                string segment = pathSegments[i];
                if (i == pathSegments.Length - 1)
                {
                    // Set the value at the final segment
                    currentNode[segment] = string.IsNullOrEmpty(fieldValue) ? null : fieldValue;
                }
                else
                {
                    // Create a nested object if it doesn't exist yet
                    if (currentNode[segment] == null)
                        currentNode[segment] = new JObject();
                    
                    currentNode = currentNode[segment];
                }
            }
        }

        // Do something with the generated JSON (e.g., send to API, write to file)
        Console.WriteLine(jsonOutput.ToString());
        // await SendToApiAsync(jsonOutput);
    }
}

Key Notes & Optimizations #

• Memory Efficiency: By streaming the large CSV, we only keep one row in memory at a time—perfect for huge files that would crash your app if loaded entirely.
• Handling Missing Fields: If a CSV field from the mapping doesn't exist in the large CSV's header, it's automatically skipped. For fields like apiField5 mapped to csvField3 (which isn't in your header example), that JSON property won't be added unless csvField3 exists in the large CSV.
• Null Handling: The code checks for empty CSV values and sets the JSON property to null—adjust this logic if you need to omit empty fields entirely.
• Batch Processing: If you need to send JSON in batches (instead of one at a time), collect a list of JObjects and process them once you hit a batch size threshold.

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