qdr.fnd/qdr.fnd.core/Data/CsvHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Abstractions;
using System.Linq;
using System.Text;

namespace Quadarax.Foundation.Core.Data
{
    public static class CsvHelper
    {
        /// <summary>
        /// Read CSV file and creates DataTable with filled content, handling quoted values.
        /// </summary>
        /// <param name="fs">Used filesystem.</param>
        /// <param name="csvFile">Full path to the CSV file.</param>
        /// <param name="delimiter">The delimiter used in the CSV file.</param>
        /// <param name="hasHeaderRow">Indicates whether the CSV file has a header row.</param>
        /// <returns>New created DataTable with content.</returns>
        public static DataTable CsvToDataTable(IFileSystem fs, string csvFile, string delimiter = ",", bool hasHeaderRow = true)
        {

            if (!fs.File.Exists(csvFile)) throw new FileNotFoundException($"File '{csvFile}' not found!", csvFile);

            var dt = new DataTable();
            using (var sr = fs.File.OpenText(csvFile))
            {
                string[]? headers = null;
                if (hasHeaderRow)
                {
                    headers = ParseCsvLine(sr.ReadLine(), delimiter);
                    if (headers == null) return dt;
                    foreach (var header in headers)
                    {
                        dt.Columns.Add(header.Trim());
                    }
                }

                while (!sr.EndOfStream)
                {
                    var rows = ParseCsvLine(sr.ReadLine(), delimiter);
                    if (rows == null) continue;

                    if (headers == null)
                    {
                        headers = new string[rows.Length];
                        for (int i = 0; i < rows.Length; i++)
                        {
                            headers[i] = $"Column{i + 1}";
                            dt.Columns.Add(headers[i]);
                        }
                    }

                    var dr = dt.NewRow();
                    for (var i = 0; i < Math.Min(headers.Length, rows.Length); i++)
                    {
                        dr[i] = rows[i];
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// Parses a single CSV line, handling quoted values and delimiters within quotes.
        /// </summary>
        /// <param name="line">The CSV line to parse.</param>
        /// <param name="delimiter">The delimiter used in the CSV line.</param>
        /// <returns>An array of field values.</returns>
        private static string[]? ParseCsvLine(string? line, string delimiter)
        {
            if (string.IsNullOrEmpty(line))
                return null;

            var result = new List<string>();
            var currentField = new StringBuilder();
            bool inQuotes = false;

            for (int i = 0; i < line.Length; i++)
            {
                if (inQuotes)
                {
                    if (line[i] == '"' && (i + 1 == line.Length || line[i + 1] != '"'))
                    {
                        // End of quoted field
                        inQuotes = false;
                    }
                    else if (line[i] == '"' && i + 1 < line.Length && line[i + 1] == '"')
                    {
                        // Escaped quote
                        currentField.Append('"');
                        i++; // Skip next quote
                    }
                    else
                    {
                        currentField.Append(line[i]);
                    }
                }
                else
                {
                    if (line[i] == '"')
                    {
                        inQuotes = true;
                    }
                    else if (line.Substring(i).StartsWith(delimiter))
                    {
                        result.Add(currentField.ToString().Trim());
                        currentField.Clear();
                        i += delimiter.Length - 1; // Skip the rest of the delimiter
                    }
                    else
                    {
                        currentField.Append(line[i]);
                    }
                }
            }

            result.Add(currentField.ToString().Trim());
            return result.ToArray();
        }

        /// <summary>
        /// Writes a DataTable to a CSV file, properly handling quoting.
        /// </summary>
        /// <param name="fs">Used filesystem.</param>
        /// <param name="dataTable">The DataTable to write.</param>
        /// <param name="filePath">The full path of the output CSV file.</param>
        /// <param name="delimiter">The delimiter to use in the CSV file.</param>
        public static void DataTableToCsv(IFileSystem fs,DataTable dataTable, string filePath, string delimiter = ",")
        {
            using (var sw = fs.File.CreateText(filePath))
            {
              
                // Write headers
                sw.WriteLine(string.Join(delimiter, dataTable.Columns.Cast<DataColumn>().Select(column => QuoteValue(column.ColumnName, delimiter))));

                // Write rows
                foreach (DataRow row in dataTable.Rows)
                {
                    sw.WriteLine(string.Join(delimiter, row.ItemArray.Select(field => QuoteValue(field?.ToString(), delimiter))));
                }
            }
        }

        /// <summary>
        /// Reads a CSV file and returns it as a list of dictionaries.
        /// </summary>
        /// <param name="fs">Used filesystem.</param>
        /// <param name="csvFile">Full path to the CSV file.</param>
        /// <param name="delimiter">The delimiter used in the CSV file.</param>
        /// <returns>List of dictionaries representing the CSV data.</returns>
        public static List<Dictionary<string, string>> CsvToDictionaryList(IFileSystem fs, string csvFile, string delimiter = ",")
        {
            if (!fs.File.Exists(csvFile)) throw new FileNotFoundException($"File '{csvFile}' not found!", csvFile);


            var result = new List<Dictionary<string, string>>();
            using (var sr = new StreamReader(csvFile))
            {
                var headers = ParseCsvLine(sr.ReadLine(), delimiter);
                if (headers == null) return result;

                while (!sr.EndOfStream)
                {
                    var rows = ParseCsvLine(sr.ReadLine(), delimiter);
                    if (rows == null) continue;

                    var dict = new Dictionary<string, string>();
                    for (var i = 0; i < headers.Length; i++)
                    {
                        dict[headers[i].Trim()] = i < rows.Length ? rows[i] : string.Empty;
                    }
                    result.Add(dict);
                }
            }
            return result;
        }

        private static string QuoteValue(string? value, string delimiter)
        {
            if (string.IsNullOrEmpty(value))
                return string.Empty;

            bool needsQuoting = value.Contains(delimiter) || value.Contains("\"") || value.Contains("\r") || value.Contains("\n");
            if (needsQuoting)
            {
                return $"\"{value.Replace("\"", "\"\"")}\"";
            }
            return value;
        }
    }
}