CsvGetExcelArguments Method

Gets the appropriate method arguments for exchanging CSV data with Excel.

Definition

Namespace: FolkerKinzel.CsvTools
Assembly: FolkerKinzel.CsvTools (in FolkerKinzel.CsvTools.dll) Version: 2.0.1+2345335399184346d9b2cc992ed5c814406052c1
C#
public static (char Delimiter, IFormatProvider FormatProvider, Encoding TextEncoding) GetExcelArguments()

Return Value

ValueTupleChar, IFormatProvider, Encoding
A ValueTupleT1, T2 containing the delimiter character, the IFormatProvider and the Encoding to use when exchanging CSV data with Excel.

Remarks

Excel formats numbers and dates depending on the culture in its "Regional Settings". Also the CSV field delimiter character used by Excel, and the Encoding that Excel uses to export CSV depends on these settings.

This method uses CurrentCulture to retrieve the required informations. CurrentCulture and the "Regional Settings" in Excel have to match to exchange CSV data successfully.

When exporting CSV to Excel the TextEncoding argument doesn't need to be used because Excel accepts UTF8 as input (which is the default of this library).

When importing CSV from Excel the arguments retrieved with this method are a good guess (including TextEncoding). To be on the safe side, OpenReadAnalyzed(String, Encoding, Header, Boolean, Int32) should be used with the TextEncoding argument as defaultEncoding.

Example

  Note

In the following code examples - for easier readability - exception handling has been omitted.
C#
using System.Data;
using System.Globalization;
using System.Text;
using FolkerKinzel.CsvTools;

namespace Examples;

internal static class DataTableExample
{
    internal static void SerializingDataTablesAsCsv(string filePath)
    {
        using var table = new DataTable();
        _ = table.Columns.Add("id", typeof(int));
        _ = table.Columns.Add("name", typeof(string));
        _ = table.Columns.Add("sales", typeof(decimal));
        _ = table.Columns.Add("last_purchase", typeof(DateOnly));
        _ = table.Columns.Add("reserved", typeof(string));

        _ = table.Rows.Add(1, "Susi", 4_711m, new DateOnly(2004, 3, 14), "my comment");
        _ = table.Rows.Add(2, "Tom", 38_527.28m, new DateOnly(2006, 12, 24));
        _ = table.Rows.Add(3, "Sören", 25.8m, new DateOnly(2011, 8, 27));

        string[] csvColumns = ["name", "last_purchase", "sales"];
        table.WriteCsv(filePath, csvColumnNames: csvColumns);

        Console.WriteLine(File.ReadAllText(filePath));


        // Write a CSV file that can be imported by Excel:
        Console.WriteLine();
        Console.WriteLine("Current culture: {0}", CultureInfo.CurrentCulture);
        Console.WriteLine();

        (char delimiter,
         IFormatProvider formatProvider,
         Encoding encoding) = Csv.GetExcelArguments();
        table.WriteCsv(filePath, delimiter, formatProvider, encoding, csvColumns);

        Console.WriteLine(File.ReadAllText(filePath, encoding));
    }
}

/*
Console output:

name,last_purchase,sales
Susi,03/14/2004,4711
Tom,12/24/2006,38527.28
Sören,08/27/2011,25.8

Current culture: de-DE

name;last_purchase;sales
Susi;14.03.2004;4711
Tom;24.12.2006;38527,28
Sören;27.08.2011;25,8
*/

See Also