SSIS从csv文件清理日期 [英] SSIS clean up date from csv file
问题描述
我想将数据从一个大的csv文件复制到数据库表(使用SSIS),但我有一个问题,列SubmittedDate在csv文件的客户端投入它在(19.janv.50或08-AUG-69 )所以它不能复制到db表(SubmittedDate是日期)
如何清理日期?
我在SSIS中看到派生列,
I want to copy data from a large csv files to DB table(with SSIS), but I have a problem with column SubmittedDate in csv file the client put mess in it (19.janv.50 or 08-AUG-69 ) so It cannot be copied into db table (SubmittedDate is date) How can I clean up the date ? I saw derived column in SSIS,
这是我最好的解决方案吗?
is this my best solution ? and how can I configure it ?
推荐答案
因为每个记录的格式可能不同,我不相信这是一个很好的例子。派生列。我将使用TSQL作为一个更新,其中原始数据加载到称为原始日期的字段,并且更新在名为SubmittedDate的列上运行。或者如果您使用这种方法,则在从分段到live表的插入期间运行。
Because the format can differ for each record I do not believe this is a good case for derived column. I would use TSQL either as an update where the original data is loaded to a field called original date and an update is run on a column called SubmittedDate. Or have this run during the insert from staging to 'live' table if you are using this approach.
我将使用select语句开始分析,并使用case确定要使用的解析。继续,直到提交(解析)日期不再为空/空。希望这可以让你开始:
I would start with analysis using a select statement and use a case to determine the parsing you want to use. Keep going until Submitted (parsed) date is no longer null / blank. Hopefully this can get you started:
Select
-- 19.janv.50
Case When CharIndex('.', OriginalDate) = 3
And CharIndex('.', OriginalDate, 4) = 8 Then --Substring code goes here.
-- 08-AUG-69
Case When CharIndex('-', OriginalDate) = 3
And CharIndex('-', OriginalDate, 4) = 7 Then --Substring code goes here.
这篇关于SSIS从csv文件清理日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!