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屋！