将数据从Excel导入ASP.NET C#中的Sql Server 2008(导入日期时间数据) [英] Import Data from Excel to Sql Server 2008 in ASP.NET C# (import datetime data)

问题描述

Hey i am trying to import data from excel to sql server , it works fine till i am not passing date but now i want to pass the date to sql server it provides error as datatype not matches.
Anyone has logic or please suggest me what can i do to ..

我的代码是

my code is

string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
               //Save File as Temp then you can delete it if you want
               FileUpload1.SaveAs(path);


               string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

               // Create Connection to Excel Workbook
               using (OleDbConnection connection =
                            new OleDbConnection(excelConnectionString))
               {
                   OleDbCommand command = new OleDbCommand
                           ("Select * FROM [Sheet1$]", connection);

                   connection.Open();

                   // Create DbDataReader to Data Worksheet
                   using (DbDataReader dr = command.ExecuteReader())
                   {

                       // SQL Server Connection String
                       string sqlConnectionString = @conn;

                       // Bulk Copy to SQL Server
                       using (SqlBulkCopy bulkCopy =
                                  new SqlBulkCopy(sqlConnectionString))
                       {
                           bulkCopy.DestinationTableName ="Table1";
                           bulkCopy.WriteToServer(dr);
                           Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                       }
                   }
               }

直到我没有传递日期之前，这段代码才能正常工作...

this code perfectly works till i am not passing date...

推荐答案

"，连接)； connection.Open(); // 创建DbDataReader到数据工作表 使用(DbDataReader dr = command.ExecuteReader()) { // SQL Server连接字符串 字符串 sqlConnectionString = @conn; // 批量复制到SQL Server 使用(SqlBulkCopy bulkCopy = 新 SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = " ; bulkCopy.WriteToServer(dr); Label1.Text = " ; } } }

", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = @conn; // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName ="Table1"; bulkCopy.WriteToServer(dr); Label1.Text = "The Client data has been exported successfully from Excel to SQL"; } } }

直到我没有输入日期，此代码才能正常工作...

this code perfectly works till i am not passing date...

可能是Excel工作表中的列无效 date 格式.
将其更改为Date Type.
在Excel工作表中选择Column ->右键单击-> Format Cells-> Number Tab->选择Date ->选择所需的类型-> Ok
然后尝试Import ...
希望它能工作...

Might be the column in the Excel Sheet is not in a valid date format.
Change it to Date Type.
Select the Column in the Excel Sheet -> Right Click -> Format Cells -> Number Tab -> Select Date -> Choose your desired Type -> Ok
Then you try to Import...
Hope it works...

它能工作，我试图将其转换为datatble den change datatype，然后插入

It works i tried to convert it in datatble den change datatype and then insert

string sqlConnectionString = @conn;
command.CommandType = CommandType.Text;
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                DataSet objDataset1 = new DataSet();

                objAdapter1.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[0][5].ToString() != "")
                    {
                        DateTime dt1 = cf.texttodb(dt.Rows[0][5].ToString());
                        dt.Rows[i][5] = dt1;
                    }}
 using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "Tablename";
                    bulkCopy.WriteToServer(dt);
                    Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                }

在此我创建了一个函数txtdob，将我的字符串转换为日期时间格式.谢谢，我尝试了将其标记为答案的功能

in this i had created a function txtdob which converts my string to datetime format Thank you i tried it workes if u feel so mark it as answer

这篇关于将数据从Excel导入ASP.NET C#中的Sql Server 2008(导入日期时间数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！