CSV To JSON using CSharp and TextFieldParser

Parsing Structured Text Files with TextFieldParser

The TextFieldParser class, part of the Microsoft.VisualBasic.FileIO namespace, provides methods and properties for parsing structured text files. It’s particularly useful for handling large files with delimited or fixed-width columns of text, such as log files or legacy database information.

Converting CSV to JSON

CSV to JSON is a common use case where we need to import files from an upstream legacy system into another application via an API that accepts JSON files. Converting CSV to JSON is not a tedious process, but we need to carefully handle all CSV standards in our parser.

Microsoft provides the TextFieldParser class, which is part of the Microsoft.VisualBasic.FileIO namespace. This class offers methods and properties that help us convert CSV data into a DataTable easily. The code is purely generic, written once and used multiple times.

Once the data is converted into a table, we will use the JsonConvert.SerializeObject class, which is part of the Newtonsoft.JSON namespace, to convert the data into JSON.

Code Example

Method to Convert CSV to DataTable

public static DataTable GetDataTableFromCSVFile(StringReader csvContent, string primarySeparator, string hasFieldsEnclosedInQuotes = "")
{
    DataTable csvData = new DataTable();
    try
    {
        using (TextFieldParser csvReader = new TextFieldParser(csvContent))
        {
            if (primarySeparator != null && primarySeparator.Length > 0)
                csvReader.SetDelimiters(new string[] { primarySeparator });

            csvReader.HasFieldsEnclosedInQuotes = Convert.ToBoolean(String.IsNullOrEmpty(hasFieldsEnclosedInQuotes) ? "true" : hasFieldsEnclosedInQuotes);
            
            // Read column names
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }
            
            // Read rows
            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                // Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == "")
                    {
                        fieldData[i] = null;
                    }
                }
                csvData.Rows.Add(fieldData);
            }
        }
    }
    catch (Exception ex)
    {
        throw ex.InnerException;
    }
    return csvData;
}

Convert DataTable to JSON

DataTable table = GetDataTableFromCSVFile(new StringReader(content), separator);
return JsonConvert.SerializeObject(table, Formatting.Indented);

This method will convert the CSV into a DataTable easily, and then the following code will convert it into JSON.

Submitted By Vibhuti Singh
Category csharp
Created On 22-Aug-2024