in C# using oledb connection string how to Convert Scientific Notation to text strings?

Question

I have a set of data that is downloaded as a Excel file using OLEDB connection string like so:

4552

3.00E+03

3.00E+22

3F45

3.00E+99

DD56677 37

Excel automatically thinks that 3E03, 3E22 and 3E99 are numbers and makes them look like above..

how to get it as a string ?

my code is

DataTable dataTable = new DataTable();

 strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
             + "Data Source=" + strFilePath + ";"
             + "Extended Properties='Excel 8.0;HDR=" + header + ";IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";

 OleDbConnection connection = new OleDbConnection(strExcelConn);
                using (OleDbCommand command = new OleDbCommand())
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                {

                    command.Connection = connection;

                    //Check if the Sheet Exists
                    connection.Open();
                    DataTable dtExcelSchema;
                    //Get the Schema of the WorkBook
                    dtExcelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    connection.Close();

                    connection.Open();
                    DataSet ds = new DataSet();
                    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    command.CommandText = "SELECT * From [" + SheetName + "]";
                    adapter.SelectCommand = command;
                    adapter.Fill(dataTable);

                    connection.Close();
                }

please can any one help me to get this colum as a string

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

Have a look at this StackOverflow question entitled How to convert a string containing an exponential number to decimal and back to string.

They use the decimal.Parse(someMoneyVar, NumberStyles.Any) method to do the conversion.

edited May 23 '17 at 12:31

Community

1
1

answered Jul 10 '14 at 10:26

Dominic Zukiewicz

8,258
8
43
61

score 0 · Answer 2 · edited Jan 09 '19 at 08:42

0

Select Excel Columns by name in your query and cast your required columns to a string using CStr(<columnName>)

edited Jan 09 '19 at 08:42

Prakash Pazhanisamy

997
1
15
25

answered Jan 09 '19 at 06:45

Fawaz Chughtai

41
6

in C# using oledb connection string how to Convert Scientific Notation to text strings?

2 Answers2