Excel to xml conversion not working for columns with different datatype values starts from number to string.

Question

I am facing a problem while reading the excel and writing them to XML using LINQ to XML.

I am having a column name as PinCode which has values like

9999 12 "123" "20" "999" 3 While converting the excel to dataset I have the values 9999,12,3. I am not able to retrieve the values in string format.

I dont want to change the formats in excel sheets. Is it possible to change the connectiion string to read all the data in a column irrespective of the datatype of the column

Note:- I tried using IMEX=1 in extended properties it is not working.

Reading the data like this

private static OleDbDataReader GetDatafromExcel(OleDbConnection oledbConn)
        {
            OleDbDataReader OledBReader = null;
            try
            {
                string query = "SELECT * FROM [Sheet1$]";
                OleDbCommand oledbCommand = new OleDbCommand(query, oledbConn);
                OledBReader = oledbCommand.ExecuteReader();

            }
            catch (Exception ex)
            {

            }
            return OledBReader;
        }

Answer 1

Caution - known hack -

For the first record in the Excel spread sheet make it '9999 instead of 9999. This should treat the values as a string instead of a number.