ExcelDataReader · appel1 · Apr 19, 2025 · Jan 22, 2025 · Jan 22, 2025 · Jan 22, 2025
diff --git a/README.md b/README.md
@@ -124,6 +124,10 @@ var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguratio
     // (CSV only)
     AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
 
+    // Gets or sets a value indicating whether to trim white space values for CSV (Default 'true').
+    // (CSV only)
+    TrimWhiteSpace = true,
+
     // Gets or sets a value indicating whether to leave the stream open after
     // the IExcelDataReader object is disposed. Default: false
     LeaveOpen = false,

diff --git a/src/ExcelDataReader.Tests/ExcelCsvReaderTest.cs b/src/ExcelDataReader.Tests/ExcelCsvReaderTest.cs
@@ -553,4 +553,28 @@ public void GitIssue580_ReadCsvWithCustomQuoteChar()
             Assert.That(row4, Is.EqualTo(new object[] { "Test3", "IJK", "ZZ", "10,143.27", "0.00" }));
         });
     }
+
+    [Test]
+    public void GitIssue566_ParseCsvWithoutTrimmingWhiteSpace()
+    {
+        var keepSpaceConfig = new ExcelReaderConfiguration
+        {
+            TrimWhiteSpace = false,
+        };
+
+        using var excelReader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("csv\\test_issue_566.csv"), keepSpaceConfig);
+
+        var ds = excelReader.AsDataSet();
+        Assert.That(ds.Tables[0].Rows[0][0], Is.EqualTo("c1"));
+        Assert.That(ds.Tables[0].Rows[0][1], Is.EqualTo("c2 "));
+        Assert.That(ds.Tables[0].Rows[0][2], Is.EqualTo("c3"));
+        Assert.That(ds.Tables[0].Rows[0][3], Is.EqualTo(" c4"));
+        Assert.That(ds.Tables[0].Rows[0][4], Is.EqualTo("c5"));
+
+        Assert.That(ds.Tables[0].Rows[1][0], Is.EqualTo("11 "));
+        Assert.That(ds.Tables[0].Rows[1][1], Is.EqualTo("12"));
+        Assert.That(ds.Tables[0].Rows[1][2], Is.EqualTo("\t"));
+        Assert.That(ds.Tables[0].Rows[1][3], Is.EqualTo(" "));
+        Assert.That(ds.Tables[0].Rows[1][4], Is.EqualTo("15"));
+    }
 }
diff --git a/src/ExcelDataReader/Core/CsvFormat/CsvAnalyzer.cs b/src/ExcelDataReader/Core/CsvFormat/CsvAnalyzer.cs
@@ -9,7 +9,7 @@ internal static class CsvAnalyzer
     /// Uses fallbackEncoding if there is no BOM. Throws DecoderFallbackException if there are invalid characters in the stream.
     /// Returns the separator whose average field count is closest to its max field count.
     /// </summary>
-    public static void Analyze(Stream stream, char[] separators, Encoding fallbackEncoding, int analyzeInitialCsvRows, char? quoteChar, out int fieldCount, out char autodetectSeparator, out Encoding autodetectEncoding, out int bomLength, out int rowCount)
+    public static void Analyze(Stream stream, char[] separators, Encoding fallbackEncoding, int analyzeInitialCsvRows, char? quoteChar, bool trimWhiteSpace, out int fieldCount, out char autodetectSeparator, out Encoding autodetectEncoding, out int bomLength, out int rowCount)
     {
         var bufferSize = 1024;
         var probeSize = 16;
@@ -29,7 +29,7 @@ public static void Analyze(Stream stream, char[] separators, Encoding fallbackEn
         {
             separatorInfos[i] = new SeparatorInfo
             {
-                Buffer = new CsvParser(separators[i], autodetectEncoding, quoteChar)
+                Buffer = new CsvParser(separators[i], autodetectEncoding, quoteChar, trimWhiteSpace)
             };
         }
 

diff --git a/src/ExcelDataReader/Core/CsvFormat/CsvParser.cs b/src/ExcelDataReader/Core/CsvFormat/CsvParser.cs
@@ -7,7 +7,7 @@ namespace ExcelDataReader.Core.CsvFormat;
 /// </summary>
 internal sealed class CsvParser
 {
-    public CsvParser(char separator, Encoding encoding, char? quoteChar = null)
+    public CsvParser(char separator, Encoding encoding, char? quoteChar = null, bool trimWhiteSpace = true)
     {
         Separator = separator;
         QuoteChar = quoteChar;
@@ -19,6 +19,8 @@ public CsvParser(char separator, Encoding encoding, char? quoteChar = null)
         CharBuffer = new char[bufferSize];
 
         State = CsvState.PreValue;
+
+        TrimWhiteSpace = trimWhiteSpace;
     }
 
     private enum CsvState
@@ -52,6 +54,8 @@ private enum CsvState
 
     private List<List<string>> RowsResult { get; set; } = [];
 
+    private bool TrimWhiteSpace { get; }
+
     public void ParseBuffer(byte[] bytes, int offset, int count, out List<List<string>> rows)
     {
         while (count > 0)
@@ -122,7 +126,7 @@ private void ParseChar(char c, int bytesUsed)
 
     private bool ReadPreValue(char c, int bytesUsed)
     {
-        if (IsWhitespace(c))
+        if (IsWhitespace(c) && TrimWhiteSpace)
         {
             return true;
         }
@@ -172,7 +176,7 @@ private bool ReadValue(char c, int bytesUsed)
         }
         else
         {
-            if (IsWhitespace(c))
+            if (IsWhitespace(c) && TrimWhiteSpace)
             {
                 TrailingWhitespaceCount++;
             }

diff --git a/src/ExcelDataReader/Core/CsvFormat/CsvWorkbook.cs b/src/ExcelDataReader/Core/CsvFormat/CsvWorkbook.cs
@@ -3,7 +3,7 @@
 
 namespace ExcelDataReader.Core.CsvFormat;
 
-internal sealed class CsvWorkbook(Stream stream, Encoding encoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null) : IWorkbook<CsvWorksheet>
+internal sealed class CsvWorkbook(Stream stream, Encoding encoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null, bool trimWhiteSpace = true) : IWorkbook<CsvWorksheet>
 {
     public int ResultsCount => 1;
 
@@ -19,9 +19,11 @@ internal sealed class CsvWorkbook(Stream stream, Encoding encoding, char[] autod
 
     public int AnalyzeInitialCsvRows { get; } = analyzeInitialCsvRows;
 
+    public bool TrimWhiteSpace { get; } = trimWhiteSpace;
+
     public IEnumerable<CsvWorksheet> ReadWorksheets()
     {
-        yield return new CsvWorksheet(Stream, Encoding, AutodetectSeparators, AnalyzeInitialCsvRows, QuoteChar);
+        yield return new CsvWorksheet(Stream, Encoding, AutodetectSeparators, AnalyzeInitialCsvRows, QuoteChar, TrimWhiteSpace);
     }
 
     public NumberFormatString GetNumberFormatString(int index)

diff --git a/src/ExcelDataReader/Core/CsvFormat/CsvWorksheet.cs b/src/ExcelDataReader/Core/CsvFormat/CsvWorksheet.cs
@@ -4,15 +4,16 @@ namespace ExcelDataReader.Core.CsvFormat;
 
 internal sealed class CsvWorksheet : IWorksheet
 {
-    public CsvWorksheet(Stream stream, Encoding fallbackEncoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null)
+    public CsvWorksheet(Stream stream, Encoding fallbackEncoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null, bool trimWhiteSpace = true)
     {
         Stream = stream;
         QuoteChar = quoteChar;
+        TrimWhiteSpace = trimWhiteSpace;
         Stream.Seek(0, SeekOrigin.Begin);
         try
         {
             // Try as UTF-8 first, or use BOM if present
-            CsvAnalyzer.Analyze(Stream, autodetectSeparators, Encoding.UTF8, analyzeInitialCsvRows, quoteChar, out var fieldCount, out var separator, out var encoding, out var bomLength, out var rowCount);
+            CsvAnalyzer.Analyze(Stream, autodetectSeparators, Encoding.UTF8, analyzeInitialCsvRows, quoteChar, trimWhiteSpace, out var fieldCount, out var separator, out var encoding, out var bomLength, out var rowCount);
             FieldCount = fieldCount;
             AnalyzedRowCount = rowCount;
             AnalyzedPartial = analyzeInitialCsvRows > 0;
@@ -25,7 +26,7 @@ public CsvWorksheet(Stream stream, Encoding fallbackEncoding, char[] autodetectS
             // If cannot parse as UTF-8, try fallback encoding
             Stream.Seek(0, SeekOrigin.Begin);
 
-            CsvAnalyzer.Analyze(Stream, autodetectSeparators, fallbackEncoding, analyzeInitialCsvRows, quoteChar, out var fieldCount, out var separator, out var encoding, out var bomLength, out var rowCount);
+            CsvAnalyzer.Analyze(Stream, autodetectSeparators, fallbackEncoding, analyzeInitialCsvRows, quoteChar, trimWhiteSpace, out var fieldCount, out var separator, out var encoding, out var bomLength, out var rowCount);
             FieldCount = fieldCount;
             AnalyzedRowCount = rowCount;
             AnalyzedPartial = analyzeInitialCsvRows > 0;
@@ -74,6 +75,8 @@ public int RowCount
 
     public Column[] ColumnWidths => null;
 
+    public bool TrimWhiteSpace { get; }
+
     private int BomLength { get; set; }
 
     private bool AnalyzedPartial { get; }
@@ -85,7 +88,7 @@ public IEnumerable<Row> ReadRows()
         var bufferSize = 1024;
         var buffer = new byte[bufferSize];
         var rowIndex = 0;
-        var csv = new CsvParser(Separator, Encoding, QuoteChar);
+        var csv = new CsvParser(Separator, Encoding, QuoteChar, TrimWhiteSpace);
         var skipBomBytes = BomLength;
 
         Stream.Seek(0, SeekOrigin.Begin);

diff --git a/src/ExcelDataReader/ExcelCsvReader.cs b/src/ExcelDataReader/ExcelCsvReader.cs
@@ -5,9 +5,9 @@ namespace ExcelDataReader;
 
 internal sealed class ExcelCsvReader : ExcelDataReader<CsvWorkbook, CsvWorksheet>
 {
-    public ExcelCsvReader(Stream stream, Encoding fallbackEncoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null)
+    public ExcelCsvReader(Stream stream, Encoding fallbackEncoding, char[] autodetectSeparators, int analyzeInitialCsvRows, char? quoteChar = null, bool trimWhiteSpace = true)
     {
-        Workbook = new CsvWorkbook(stream, fallbackEncoding, autodetectSeparators, analyzeInitialCsvRows, quoteChar);
+        Workbook = new CsvWorkbook(stream, fallbackEncoding, autodetectSeparators, analyzeInitialCsvRows, quoteChar, trimWhiteSpace);
 
         // By default, the data reader is positioned on the first result.
         Reset();

diff --git a/src/ExcelDataReader/ExcelReaderConfiguration.cs b/src/ExcelDataReader/ExcelReaderConfiguration.cs
@@ -28,6 +28,11 @@ public class ExcelReaderConfiguration
     /// </summary>
     public char? QuoteChar { get; set; } = '"';
 
+    /// <summary>
+    /// Gets or sets a value indicating whether to trim white space values for CSV (Default 'true').
+    /// </summary>
+    public bool TrimWhiteSpace { get; set; } = true;
+
     /// <summary>
     /// Gets or sets a value indicating whether to leave the stream open after the IExcelDataReader object is disposed. Default: false.
     /// </summary>

diff --git a/src/ExcelDataReader/ExcelReaderFactory.cs b/src/ExcelDataReader/ExcelReaderFactory.cs
@@ -165,7 +165,7 @@ public static IExcelDataReader CreateCsvReader(Stream fileStream, ExcelReaderCon
             fileStream = new LeaveOpenStream(fileStream);
         }
 
-        return new ExcelCsvReader(fileStream, configuration.FallbackEncoding, configuration.AutodetectSeparators, configuration.AnalyzeInitialCsvRows, configuration.QuoteChar);
+        return new ExcelCsvReader(fileStream, configuration.FallbackEncoding, configuration.AutodetectSeparators, configuration.AnalyzeInitialCsvRows, configuration.QuoteChar, configuration.TrimWhiteSpace);
     }
 
     private static bool TryGetWorkbook(Stream fileStream, CompoundDocument document, out Stream stream)

diff --git a/src/TestData/csv/test_issue_566.csv b/src/TestData/csv/test_issue_566.csv
@@ -0,0 +1,2 @@
+c1,c2 ,c3, c4,c5
+11 ,12,	, ,15