将数据导入SQL Server [英] Importing data into SQL server
问题描述
我正在从CSV文件将数据导入sql server现在我遇到了这个问题
i am importing data into sql server from CSV file now i am getting this issue
String was not recognized as a valid DateTime.Couldn't store <20180526> in Date Column. Expected type is DateTime.
我尝试过：
代码添加回复;
What I have tried:
Code added from replies;
here is my complete code
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
namespace BindGridviewFromCSVFile
{
public partial class BindGridview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnFileUpload_Click(object sender, EventArgs e)
{
//Creating object of datatable
//Upload and save the file.
//getting full file path of Uploaded file
string CSVFilePath = Server.MapPath(FileUpload1.PostedFile.FileName);
//Reading All text
string ReadCSV = File.ReadAllText(CSVFilePath);
DataTable dt = new DataTable("HR");
//DataColumn dateColumn = new DataColumn();
//dateColumn.ColumnName = "Date";
//dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Empcode", typeof(int)),
//new DataColumn("Days", typeof(int)),
// new DataColumn("Date", typeof(int)),
//DateTime Date = Convert.ToDateTime("Date");
// table.Columns.Add("SaleDate", typeof(DateTime));
// new DataColumn("Time", typeof(int)),
// new DataColumn("IN/OUT", typeof(int)),});
// Create Column 1: SaleDate
DataColumn DaysColumn = new DataColumn();
// dateColumn.DataType = Type.GetType("System.int");
DaysColumn.ColumnName = "Days";
DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");
dateColumn.ColumnName = "Date";
// Create Column 2: ProductName
DataColumn timeNameColumn = new DataColumn();
timeNameColumn.ColumnName = "Time";
// Create Column 3: TotalSales
DataColumn INOUTColumn = new DataColumn();
// totalSalesColumn.DataType = Type.GetType("System.int");
INOUTColumn.ColumnName = "IN/OUT";
// EmpcodeColumn.DataType = Type.GetType("int");
DataColumn EmpcodeColumn = new DataColumn();
EmpcodeColumn.ColumnName = "Empcode";
// Add the columns to the ProductSalesData DataTable
dt.Columns.Add(DaysColumn);
dt.Columns.Add(dateColumn);
dt.Columns.Add(timeNameColumn);
dt.Columns.Add(INOUTColumn);
dt.Columns.Add(EmpcodeColumn);
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!
// Create a new row
//DataRow dailyProductSalesRow = dt.NewRow();
//dailyProductSalesRow["Empcode"] = "";
//dailyProductSalesRow["Days"] = "";
//dailyProductSalesRow["Date"] = DateTime.Today;
//dailyProductSalesRow["Time"] = "";
//dailyProductSalesRow["IN/OUT"] = "";
string csvData = File.ReadAllText(CSVFilePath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Institute;Integrated Security=True");
// con.Open();
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName =
推荐答案
您可能需要使用 DateTime.Parse将字符串值转换为DateTime （）。
请参阅此处的示例： https://www.dotnetperls.com/datetime -parse [ ^ ]
You probably need to convert the string value to a DateTime with DateTime.Parse().
See examples here: https://www.dotnetperls.com/datetime-parse[^]
感谢您使用代码更新您的问题。
查看你的代码，你有以下这一行：
Thank you for updating your question with the code.
Looking at your code, you have this line below:
dt.Rows[dt.Rows.Count - 1][i] = cell;
看起来您将所有值视为 string 键入，但您的 DataTable 包含 System.DateTime 列。您需要识别具有非字符串类型的列并在分配值之前执行转换。
基于代码的快速而肮脏的例子是做这样的事情：
It looks like you are treating all values as string type but your DataTable contains a System.DateTime column. You need to identify the Column that has non-string type and perform conversion before assigning the value.
A quick and dirty example basing on your code is to do something like this:
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
//means you are looking for the 2nd Column which of type DateTime
//note that index starts at 0, so we use 1 for locating 2nd column
if(i = 1){
//convert string to datetime
DateTime documentDate = DateTime.MinValue;
if(DateTime.TryParse(cell, out documentDate)){
dt.Rows[dt.Rows.Count - 1][i] = documentDate;
}
}
else{
dt.Rows[dt.Rows.Count - 1][i] = cell;
}
i++;
}
}
}
请注意的是我们使用 DateTime.TryParse 以确保我们将有效的日期时间字符串值传递给列。
您可能还想要看看这篇使用 schema.ini 将CSV导入SQL数据库的文章： ASP.NET WebForms：上传CSV文件并将其导入SQL Server [ ^ ]
The thing to keep a note there is we use DateTime.TryParse to ensure that we are passing a valid datetime string value to the column.
You may also want to look at this article that uses schema.ini to import CSV to SQL database: ASP.NET WebForms: Uploading and Importing CSV File to SQL Server[^]
这篇关于将数据导入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
