how remove the BOM() characters from a UTF 8 encoded csv?

Question

I need to parse a utf8 encoded csv. After conversion i just saw that the problem is with the BOM () character at the beginging. I cannot create a csv avoiding the BOM with utf8 encoding as i need to parse it even if it is utf8 encoded.

Any one please tell me how can i remove the BOM () character from a csv using c#.net..

Update : I have added my code to read the csv headers since im getting the BOM at the beginning of the file.

 string CSVConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + ConfigurationSettings.AppSettings["CSVFolder"].ToString() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;";

        using (OdbcConnection Connection = new OdbcConnection(CSVConnectionString))
        {
            List<string> CSVHeaders = new List<string>();

            string SelectQuery = string.Format(@"SELECT TOP 1 * FROM [{0}]", CSVFileName);

            OdbcCommand Command = new OdbcCommand(SelectQuery, Connection);

            Connection.Open();

            OdbcDataReader Reader = Command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            int ColumnCount = Reader.FieldCount;

            for (int column = 0; column < ColumnCount; column++)
            {
                CSVHeaders.Add(Reader.GetName(column));
            }

            return CSVHeaders;
        }

Answer 1

Actually, C# can read UTF-8 encoded files containing a BOM just fine. It's the broken CSV text driver you're using that's actually causing the problem. I'd recommend one of the other CSV reading solutions from this answer.

Answer 2

Here is a function that does this:

    public static void SaveAsUTF8WithoutByteOrderMark(string fileName)
    {
        SaveAsUTF8WithoutByteOrderMark(fileName, null);
    }

    public static void SaveAsUTF8WithoutByteOrderMark(string fileName, Encoding encoding)
    {
        if (fileName == null)
            throw new ArgumentNullException("fileName");

        if (encoding == null)
        {
            encoding = Encoding.Default;
        }

        File.WriteAllText(fileName, File.ReadAllText(fileName, encoding), new UTF8Encoding(false));
    }

Answer 3

Instead of changing horses (use another .csv driver) or help the given horse by pulling the wagon yourself (change the encoding), you should tell the horse (the standard ODBC Text driver) what it needs to know to do the job by adding a schema.ini file:

[withbomgood.txt]
Format=TabDelimited
ColNameHeader=True
CharacterSet=65001
Col1=FrsColümn CHAR

to define the format of withbomgood.txt:

FrsColümn
whätever

which is an exact copy of withbombad.txt; both files have a BOM:

FrsColümn
whätever

If you now call a slightly modified copy

static void Harun00(string CSVFileName)
{
    string CSVFilePath = @"E:\trials\SoTrials\answers\6260911\data";
    string CSVConnectionString = 
        "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + 
        CSVFilePath +
        ";Extensions=asc,csv,tab,txt;Persist Security Info=False;";

    using (OdbcConnection Connection = new OdbcConnection(CSVConnectionString))
    {
        List<string> CSVHeaders = new List<string>();

        string SelectQuery = string.Format(@"SELECT TOP 1 * FROM [{0}]", CSVFileName);

        OdbcCommand Command = new OdbcCommand(SelectQuery, Connection);

        Connection.Open();

        OdbcDataReader Reader = Command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

        int ColumnCount = Reader.FieldCount;

        for (int column = 0; column < ColumnCount; column++)
        {
            CSVHeaders.Add(Reader.GetName(column));
        }

        Console.WriteLine(CSVHeaders[0]);
    }
}

of your code twice:

static void Main(string[] args)
{
    Harun00("withbombad.txt");
    Harun00("withbomgood.txt");
}

you get:

FrsColümn
FrsColümn
Press any key to continue . . .

which proves that the driver will read an UTF8 with BOM file correctly and without any further ADO if you follow the rule: define your csv tables in a schema.ini file.