我想将日期从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屋!