我想将日期从excel保存到SQL [英] I want to save date from excel to SQL

问题描述

将excel文件数据保存到sql表中... excel数据也是ontains date

获取错误：'转换失败日期/或来自字符串的时间'



我尝试过：



我的Excel日期格式为 10/11/17 或 10/11/2017

在sql列类型是datetime



我的sp片段 -

  MERGE   INTO  EmployeeDetails  AS  ED 
 使用  @ tvp_EmployeeDetails   AS  tED 
  ON  ED.EmpCode = tED.EmpCode 
  AND  isnull（ED.EmpCode，' '）<> ' ' 
 和 ED .EmpCode  in （选择 EmpCode 来自 dbo .UserDetails）
  WHEN   MATCHED  那么 
 更新  SET  
 ED.EmpCode = tED.EmpCode，
 ED.DOJ = tED.DOJ，
 ED.PermantAddress = tED.PermantAddress，
 ED.PrimaryMissionID = tED.PrimaryMissionID，
 ED.SecondaryMissionID = tED.SecondaryMissionID，
 ED.AltitudeId = tED.AltitudeId，
 ED.Extension = tED.Extension，
 ED.ApplicationId_V1 = tED.ApplicationId_V1，
 ED.ApplicationId_V2 = tED.ApplicationId_V2，
 ED。 ApplicationId_Others = tED.ApplicationId_Other s，
 ED.TrainingStartDate = tED.TrainingStartDate，
 ED.TrainingEndDate = tED.TrainingEndDate，
 ED.CertificationDate = tED.CertificationDate，
 ED.CertificationAttempt = tED.CertificationAttempt， 
 ED.OnBoardDate = tED.OnBoardDate，
 ED.SeparationDate =  case  tED.SeparationDate  when  ' ' 然后  null   else  tED.SeparationDate  end ，
 ED.SeparationReason = tED.SeparationReason，
 ED.DisciplinaryAction = tED.DisciplinaryAction，
 ED.EmpImage = tED.EmpImage，
 ED.UpdatedBy = @ UserID，
 ED。 UpdatedOn = GETDATE（）
  WHEN   NOT   MATCHED   TH EN  
  INSERT （[EmpCode]，[DOJ]，[PermantAddress]，[PrimaryMissionID]，[SecondaryMissionID]，[AltitudeId]，[扩展名]，[ApplicationId_V1] 
，[ApplicationId_V2]，[ApplicationId_Others]，[TrainingStartDate]，[TrainingEndDate]，[CertificationDate]，[CertificationAttempt] 
，[OnBoardDate]，[SeparationDate]，[SeparationReason] ，[DisciplinaryAction]，[EmpImage]，[CreatedBy]，[CreatedOn]）
  values （tED.EmpCode， case  当 isdate（tED.DOJ）= 1 然后 tED.DOJ  else   null   end ，
 tED.PermantAddress ，tED.PrimaryMissionID，tED.SecondaryMissionID，tED.AltitudeId，tED.Extension，tED.ApplicationId_V1，
 tED.ApplicationId_V2，tED.ApplicationId_Others， case   when  isdate（tED.TrainingStartDate）= 1 然后 tED.TrainingStartDate  else   null   end ，
  case  当 isdate（tED.TrainingEndDate）= 1 然后 tED.TrainingEndDate  else   null   end ， case  当 isdate（tED.CertificationDate）= 1 然后 tED.CertificationDate  else   null   end ，tED.CertificationAttempt，
  case  当 isdate（tED.OnBoardDate）= 1 然后 tED.OnBoard日期其他  null  结束，
  case  当 isdate（tED.SeparationDate）= 1 然后 tED.SeparationDate  else   null   end ，tED.SeparationReason，tED.DisciplinaryAction，tED.EmpImage， @ UserID ，GETDATE（））; 

解决方案

在插入sql之前，你必须将该字符串日期转换为有效的日期时间，例如

使用System; 
使用System.Globalization; 
 
公共课程
 {
 public static void Main（）
 {
 string dateString =10/12/2017; 
 string format1 =d / M / yy; 
 string format2 =d / M / yyyy; 
 DateTime dateTime; 
 if（DateTime.TryParseExact（dateString，format1，CultureInfo.InvariantCulture，DateTimeStyles.None，out dateTime））
 {
 Console.WriteLine（dateTime）; 
} 
 else if（DateTime.TryParseExact（dateString，format2，CultureInfo.InvariantCulture，DateTimeStyles.None，out dateTime））
 {
 Console.WriteLine（dateTime）; 
} 
 else //无效
 {
 Console.WriteLine（不是有效的约会时间，做点什么！）; 
} 
} 
} 

参考：

1. DateTime.TryParseExact Method（System） [ ^ ]

2. 自定义日期和时间格式字符串 [ ^ ]

< blockquote>首先看看你的SQL服务器使用的日期格式（MM-DD-YY或YYYY-MM-DD或YYYY / MM / DD ....等）。这是基于 sql排序规则。



假设你的sql server存储了date&这种格式的时间 YYYY-MM-DD HH：MI：SS



你可以做的最简单的事情是



1.将excel中的所有数据复制到记事本中。它将删除所有格式的excel定义的数据。



2.打开一个新的Excel文件并按CTRL + A（全选）。



3.右键单击并选择 格式化单元格



4.在格式化单元格窗口中选择文本作为格式



5.转到您复制的笔记本数据并按下CTRL + A



6.现在将这些数据从Note Pad复制并粘贴到Excel中。



7.现在您拥有文本格式的所有列。因此，将日期（如果需要）编辑为您喜欢的日期格式。在我的情况下格式是 YYYY-MM-DD



（注意：不要担心时间部分sql将自己添加）



8.现在尝试将新创建的Excel工作表中的数据导入到您的sql

i m tring to save excel file data into sql table...excel data also ontains date
getting error: 'conversion fail date/or time from character string'

What I have tried:

my excel date format is 10/11/17 or 10/11/2017
in sql column type is datetime

my sp snippet -

MERGE INTO EmployeeDetails AS ED        
 USING @tvp_EmployeeDetails AS tED        
 ON ED.EmpCode = tED.EmpCode   
 AND isnull(ED.EmpCode,'') <> ''  
 and ED.EmpCode in (select EmpCode from dbo.UserDetails)  
 WHEN MATCHED THEN        
 UPDATE SET         
 ED.EmpCode = tED.EmpCode,        
 ED.DOJ = tED.DOJ,         
 ED.PermantAddress = tED.PermantAddress,        
 ED.PrimaryMissionID = tED.PrimaryMissionID,        
 ED.SecondaryMissionID = tED.SecondaryMissionID,        
 ED.AltitudeId = tED.AltitudeId,        
 ED.Extension = tED.Extension,        
 ED.ApplicationId_V1 = tED.ApplicationId_V1,        
 ED.ApplicationId_V2 = tED.ApplicationId_V2, 
 ED.ApplicationId_Others = tED.ApplicationId_Others, 
 ED.TrainingStartDate = tED.TrainingStartDate,  
 ED.TrainingEndDate = tED.TrainingEndDate,   
 ED.CertificationDate = tED.CertificationDate,   
 ED.CertificationAttempt = tED.CertificationAttempt,   
 ED.OnBoardDate = tED.OnBoardDate,
 ED.SeparationDate = case tED.SeparationDate when '' then null else  tED.SeparationDate end,
 ED.SeparationReason = tED.SeparationReason,
 ED.DisciplinaryAction = tED.DisciplinaryAction, 
 ED.EmpImage = tED.EmpImage,   
 ED.UpdatedBy =@UserID,     
 ED.UpdatedOn = GETDATE()      
 WHEN NOT MATCHED THEN      
INSERT ([EmpCode],[DOJ] ,[PermantAddress] ,[PrimaryMissionID] ,[SecondaryMissionID] ,[AltitudeId]  ,[Extension],[ApplicationId_V1]
       ,[ApplicationId_V2]  ,[ApplicationId_Others] ,[TrainingStartDate],[TrainingEndDate],[CertificationDate] ,[CertificationAttempt]
       ,[OnBoardDate] ,[SeparationDate],[SeparationReason],[DisciplinaryAction],[EmpImage] ,[CreatedBy],[CreatedOn])
 values (tED.EmpCode, case when isdate(tED.DOJ)=1 then tED.DOJ else null end ,
		tED.PermantAddress,tED.PrimaryMissionID,tED.SecondaryMissionID,tED.AltitudeId,tED.Extension,tED.ApplicationId_V1,
		tED.ApplicationId_V2, tED.ApplicationId_Others,	 case when isdate(tED.TrainingStartDate)=1 then tED.TrainingStartDate else null end,
		case when isdate(tED.TrainingEndDate)=1 then tED.TrainingEndDate else null end, case when isdate(tED.CertificationDate)=1 then tED.CertificationDate else null end,tED.CertificationAttempt,
		case when isdate(tED.OnBoardDate)=1 then tED.OnBoardDate else null end,
		case when isdate(tED.SeparationDate)=1 then tED.SeparationDate else null end ,tED.SeparationReason,tED.DisciplinaryAction,tED.EmpImage, @UserID, GETDATE()) ;

解决方案

You have to convert that string date to a valid datetime before inserting to sql, e.g.

using System;
using System.Globalization;

public class Program
{
	public static void Main()
	{
		string dateString = "10/12/2017";
		string format1 = "d/M/yy";
		string format2 = "d/M/yyyy";
		DateTime dateTime;
		if (DateTime.TryParseExact(dateString, format1, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
		{
			Console.WriteLine(dateTime);
		}
		else if (DateTime.TryParseExact(dateString, format2, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
		{
			Console.WriteLine(dateTime);
		}
		else // not valid
		{
			Console.WriteLine("Not a valid date time, do something!");
		}
	}
}

Reference:
1. DateTime.TryParseExact Method (System)[^]
2. Custom Date and Time Format Strings[^]

First of all see what is the date format(MM-DD-YY or YYYY-MM-DD or YYYY/MM/DD....etc) of your SQL server use. This is based on the sql collation.

Let's say your sql server stored date & time in this format YYYY-MM-DD HH:MI:SS

Most simple thing you can do is

1. Copy all the data from your excel into a Note Pad. It will remove all the formats of excel define for your data.

2. Open a new excel file and press CTRL + A (select all).

3. Right click and select Format Cells

4. In the Format Cells window select Text as format

5. Go to Note Pad where you copy the data and press CTRL + A

6. Now copy and paste those data from Note Pad to Excel.

7. Now you have all the columns in text format. So edit the date (if needed) into the date format which your sql prefer. In my case format is YYYY-MM-DD

(Note: Don't worry about time part sql will add that itself)

8. Now try to import data from newly created excel sheet to your sql

这篇关于我想将日期从excel保存到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！