LaVOZs

The World’s Largest Online Community for Developers

'; c# - Importing Excel to SQL stores dates as numeric values in ASP.NET - LavOzs.Com

I have a snippet that loads an Excel file (user input using asp.net) to a SQL table, i'm stuck trying to keep dates as 'DD/MM/YYYY' intead of '438xx'. i tried using .NumberFormat but it gave no solution since datatable doesn't allow anything but ToString() wich also stores the value as a int value. Any way to transform that data?

if (subeArchivo.HasFile)
            {
                string path = string.Concat(Server.MapPath("~/" + subeArchivo.FileName));
                subeArchivo.SaveAs(path);

                Microsoft.Office.Interop.Excel.Application appExcel;
                Microsoft.Office.Interop.Excel.Workbook workbook;
                Microsoft.Office.Interop.Excel.Range range;
                Microsoft.Office.Interop.Excel._Worksheet worksheet;

                appExcel = new Microsoft.Office.Interop.Excel.Application();
                workbook = appExcel.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1];
                range = worksheet.UsedRange;

                int rowCount = range.Rows.Count;
                int colCount = range.Columns.Count;

                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("ID_USU_CAR");
                dt.Columns.Add("RUT_DCT");
                dt.Columns.Add("TIP_CON");
                dt.Columns.Add("ID_CON");
                dt.Columns.Add("INI_BEN"); //date values as int
                dt.Columns.Add("FIN_BEN"); //date values as int
                dt.Columns.Add("MON_CLP");

                for (int Rnum = 3; Rnum <= rowCount; Rnum++)
                {
                    DataRow dr = dt.NewRow();
                    //Reading Each Column value From sheet to datatable Colunms                  
                    for (int Cnum = 1; Cnum <= colCount; Cnum++)
                    {
                        dr[Cnum - 1] = (range.Cells[Rnum, Cnum]).Value2.ToString();
                    }
                    dt.Rows.Add(dr); // adding Row into DataTable
                    dt.AcceptChanges();
                }

                workbook.Close(true);
                appExcel.Quit();

                try
                {

                string cadenaConn = ConfigurationManager.ConnectionStrings["cadenaConn"].ConnectionString.ToString();

                    SqlBulkCopy bulkcopy = new SqlBulkCopy(cadenaConn);

                    SqlBulkCopyColumnMapping ID_USU_CAR = new SqlBulkCopyColumnMapping("ID_USU_CAR", "ID_USU_CAR");
                    bulkcopy.ColumnMappings.Add(ID_USU_CAR);
                    SqlBulkCopyColumnMapping RUT_DCT = new SqlBulkCopyColumnMapping("RUT_DCT", "RUT_DCT");
                    bulkcopy.ColumnMappings.Add(RUT_DCT);
                    SqlBulkCopyColumnMapping TIP_CON = new SqlBulkCopyColumnMapping("TIP_CON", "TIP_CON");
                    bulkcopy.ColumnMappings.Add(TIP_CON);
                    SqlBulkCopyColumnMapping ID_CON = new SqlBulkCopyColumnMapping("ID_CON", "ID_CON");
                    bulkcopy.ColumnMappings.Add(ID_CON);
                    SqlBulkCopyColumnMapping INI_BEN = new SqlBulkCopyColumnMapping("INI_BEN", "INI_BEN");
                    bulkcopy.ColumnMappings.Add(INI_BEN);
                    SqlBulkCopyColumnMapping FIN_BEN = new SqlBulkCopyColumnMapping("FIN_BEN", "FIN_BEN");
                    bulkcopy.ColumnMappings.Add(FIN_BEN);
                    SqlBulkCopyColumnMapping MON_CLP = new SqlBulkCopyColumnMapping("MON_CLP", "MON_CLP");
                    bulkcopy.ColumnMappings.Add(MON_CLP);

                    bulkcopy.DestinationTableName = "TBL";
                    bulkcopy.WriteToServer(dt);

                    lblConfirma.Text = "CARGA EXITOSA";
                }
                catch (Exception ex)
                {
                    lblConfirma.Text = ex.Message.ToString();
                }


            }

Excel stores dates as a double, counting the days since a constant epoc. You can call DateTime.FromOADate to convert it.

Related
Stop Excel from automatically converting certain text values to dates
How to update the value stored in Dictionary in C#?
How do I fetch a set of rows from data table
Excel Date to String conversion
Add leading zeroes/0's to existing Excel values to certain length
How to pass an array into a SQL Server stored procedure
Excel Import Error in ASP.net
How do I format my export to excel workbook in microsoft.office.interop.excel?
Import Excel Sheet in Asp.Net