CODEWITHSUNDEEP
c#excel
Darrick
Darrick

Reputation: 1

C# Preventing excel from automtically changing my numbers to date

I am currently trying to convert XLS files into CSV files using C# without using any excel library. I have managed to convert it from XLS to CSV but I am currently facing a problem where the row which contains numbers is being automatically converted into a datetime format as the first cell above is a datetime. I cannot change the format of the original file.

Here is my code use for conversion of xls to csv:

protected static void convertExcelToCsv(string inputFile, string outputFile)
    {

        int worksheetNumber = 1;

        var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", inputFile);
        var cnn = new OleDbConnection(cnnStr);

        var dt = new DataTable();

        cnn.Open();
        var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
        string sql = String.Format("SELECT * FROM [{0}]", worksheet);
        var da = new OleDbDataAdapter(sql, cnn);



        da.Fill(dt);

        cnn.Close();

        using (var wtr = new StreamWriter(outputFile))
        {
            foreach (DataRow row in dt.Rows)
            {


                bool firstLine = true;
                foreach (DataColumn col in dt.Columns)
                {
                    if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
                    var data = row[col.ColumnName].ToString().Replace(",", "");
                    wtr.Write(data);
                }
                wtr.WriteLine();
            }
        }


    }

Upvotes: 0

Views: 672

Answers (1)

John Wu
John Wu

Reputation: 52280

The problem

In IMEX mode, the driver looks at the first few rows (8 by default) to determine the format of the column. If the column is "intermixed," it defaults to text. It sounds like the non-date data don't appear until lower down in the file.

Solution #1

You can increase the number of rows that it looks ahead by modifying the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

Solution #2

If you wind up with a DateTime and you wish to convert it to the integer value, you can use

readonly DateTime baseDate = new DateTime(1899,12,31);  //This is constant

var d = /* DateTime from Excel */
int originalValue = d.Subtract(baseDate).TotalDays;

Solution #3

Ditch the driver. Use Excel interop instead. Then you can read the underlying cell values, the formatted values, or whatever you want.

Upvotes: 1

Related Questions