不打开文件，从CSV文件的单个列中删除分隔符。

Question

我有一个csv文件，其中包含以下内容

这是CSV文件的内容:

Date_Added|this_flag|Name|DOB|SSN|ID

2015年5月1日|Y|Jingle|heimerscmidt|19901002|123456789|3

May 1st, 2015|N|Jingleheimerscmidt|19901002|123456789|3
May 5th, 2015|Y|Jon|19901001|012345678|1
May 1st, 2015|N|Jon|19901002|012345678|1
May 1st, 2015|Y|Jacob|19901001|234567890|2
May 5th, 2015|N|Jingleheimerscmidt|19901001|123456789|3
May 1st, 2015|Y|Jingleheimerscmidt|19901001|123456789|3

如您所见，粗体和斜体内容中除了使用管道符号分隔的列外，文本中还有一个管道运算符。我想在不打开csv文件的情况下从文本中删除该管道运算符。是否有一种通过编写代码或其他方法来解决此问题的方式？

- kpmandani

---

不打开文件？你的意思是不用手动在文本编辑器中打开它？要读取文件内容必须打开它。您可以将其作为单个VARCHAR值输入，并根据自己的规则切割字符串...例如，将每个列拆分成多个行，并确定何时存在太多行，然后将行连接起来并将其旋转回表格格式。 - Matt

如果您可以控制生成CSV文件的过程，您可以添加文本限定符，例如引号，以便在摄取应用程序（或您自己的数据清理代码）中不会将文本内的管道符与分隔符混淆。 - Jayvee

@Missy你能详细说明一下如何通过代码打开它并获得所需的结果吗？ - kpmandani

即使您可以在不“打开”文件的情况下打开它，您仍然有另一个问题。正如您所说明的那样，一些但并非所有的“记录”可能会有额外的管道符号。您将如何识别它们？是每个“记录”必须恰好有六个管道符号，如果有六个，则删除第三个吗？还是其他规则？ - user5683823

这是我从别人那里收到的文件。因此，生成csv文件不在我的控制之下。当我收到它时，每个都带有管道符号。 - kpmandani

Answer 1

如果这只是你需要处理的一个情况，而且你不想修改文件，又需要在Informatica中完成，你可以将此源限定器的输入类型会话属性从文件更改为命令，并使用sed进行替换，例如：

cat $$FileName | sed -e 's/Jingle|heimerscmidt/Jingleheimerscmidt/g'

这不是一个很好的解决方案，因此它不是通用的。但也许这可以做到或者至少给你一些想法。

- Maciejg

Answer 2

好的，我知道你标记了Oracle，所以也许你自己或另一个Oracle专家可以将这个解决方案从SQL Server迁移到Oracle。我知道Oracle能够执行每个操作。

通常我会说你需要一种快速/高级的方法来拆分字符串，但在这种情况下，你需要维护定界符之间字符串的序数位置。所以我想到了一种你可以做到这一点的方法。

1）首先将CSV导入临时表中作为所有1列。如果您的CRLF也在Name列中找到，那么这将是一个问题...但我们将假设它没有因为您没有指定它。

2）在该表上构建一个行号，用作虚拟主键，并确定是否有比应该有的更多的定界符。

3）使用递归CTE将字符串拆分为行，并通过连接后的原始字符串中的子字符串的序数位置来维护其序数位置。

4）通过修改OrdinalPostion并生成基于它的DENSE_RANK()来确定要分组的行

5）条件聚合使用OrdinalGroup作为列号，然后使用连接方法将所有OrdginalGroup 3行组合起来。

DECLARE @CSV as TABLE (LumpedColumns NVARCHAR(MAX))
INSERT INTO @CSV VALUES
('May 1st, 2015|Y|Jingle|he|imerscmidt|19901002|123456789|3')
,('May 1st, 2015|N|Jingleheimerscmidt|19901002|123456789|3')
,('May 5th, 2015|Y|Jon|19901001|012345678|1')
,('May 1st, 2015|N|Jon|19901002|012345678|1')
,('May 1st, 2015|Y|Jacob|19901001|234567890|2')
,('May 5th, 2015|N|Jingleheimerscmidt|19901001|123456789|3')
,('May 1st, 2015|Y|Jingleheimerscmidt|19901001|123456789|3')

;WITH cteFakePrimaryKey AS (
    SELECT
       LumpedColumns
       ,CASE WHEN LEN(LumpedColumns) - LEN(REPLACE(LumpedColumns,'|','')) > 5 THEN
          LEN(LumpedColumns) - LEN(REPLACE(LumpedColumns,'|','')) - 5 ELSE 0 END as MergeXPositions
       ,ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as PK
    FROM
       @CSV
)


, cteRecursive AS (
    SELECT
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,LEFT(LumpedColumns,CHARINDEX('|',LumpedColumns)-1) as ColValue
       ,RIGHT(LumpedColumns,LEN(LumpedColumns) - CHARINDEX('|',LumpedColumns)) as Remaining
       ,1 As OrdinalPosition
    FROM
       cteFakePrimaryKey

    UNION ALL

    SELECT
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,LEFT(Remaining,CHARINDEX('|',Remaining)-1)
       ,RIGHT(Remaining,LEN(Remaining) - CHARINDEX('|',Remaining))
       ,OrdinalPosition + 1
    FROM
       cteRecursive
    WHERE
       Remaining IS NOT NULL AND CHARINDEX('|',Remaining) > 0

    UNION ALL

    SELECT 
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,Remaining
       ,NULL
       ,OrdinalPosition + 1
    FROM
       cteRecursive
    WHERE Remaining IS NOT NULL AND CHARINDEX('|',Remaining) = 0
)

, cteOrdinalGroup AS (
    SELECT
       PK
       ,LumpedColumns
       ,ColValue
       ,OrdinalPosition
       ,DENSE_RANK() OVER (PARTITION BY PK ORDER BY
          CASE
             WHEN OrdinalPosition < 3 THEN OrdinalPosition
             WHEN OrdinalPosition > (3 + MergeXPositions) THEN OrdinalPosition
          ELSE 3 END ) as OrdinalGRoup

    FROM
       cteRecursive
)

SELECT
    PK
    ,LumpedColumns
    ,MAX(CASE WHEN OrdinalGRoup = 1 THEN ColValue END) as Date_Added
    ,MAX(CASE WHEN OrdinalGRoup = 2 THEN ColValue END) as this_flag
    ,STUFF(
        (SELECT '|' + ColValue
        FROM
            cteOrdinalGroup g2
        WHERE
             g1.PK = g2.PK
             AND g2.OrdinalGroup = 3
        ORDER BY
          g2.OrdinalPosition
        FOR XML PATH(''))
        ,1,1,'') as name
    ,MAX(CASE WHEN OrdinalGRoup = 4 THEN ColValue END) as DOB
    ,MAX(CASE WHEN OrdinalGRoup = 5 THEN ColValue END) as SSN
    ,MAX(CASE WHEN OrdinalGRoup = 6 THEN ColValue END) as ID
FROM
    cteOrdinalGroup g1
GROUP BY
    PK
    ,LumpedColumns
ORDER BY
    PK

- Matt