python - 我可以使用 SQL 将存储为 CSV(逗号分隔值)的表列的内容拆分为新表中的各个行吗？

Question

我看到有几个相关的问题和答案，但不完全是我需要的，所以我会问一个新问题。我有这个包含数千行商店库存数据的 CSV 文件，我想将这些数据导入到 MS SQL Server 数据库中并使用 SQL 来处理它们。导入 CSV 文件后，SQL 表将具有包含 CSV 数据的三列。问题是我需要将此 CSV 数据放入单独的行中以便对其进行更多分析。我想最终得到两个表:通过导入 CSV 文件创建的原始表，以及通过拆分 CSV 文件创建的表。这是两个表的外观的表示:

/Table1 (the original CSV file).  First row is column names:
StoreID,Date,StoreName,City,State,Category1CSV,Category2CSV,Category3CSV
1051,2/16/2014,Easton,Columbus,OH,"Flour,Yeast,Baking Powder","Milk,Water,Oil","Cinnamon,Sugar"
1425,1/14/2014,Crocker Park,Westlake,OH,"Baking Powder,Yeast,Flour","Oil,Milk,Water","Rosemay,Cinnamon,Sugar"

/Table2 (after splitting the CSV column contents). First row is column names:
StoreID,Date,StoreName,City,State,ItemName,ItemRank,ItemCategory
1051,2/16/2014,Easton,Columbus,OH,Flour,1,1
1051,2/16/2014,Easton,Columbus,OH,Yeast,2,1
1051,2/16/2014,Easton,Columbus,OH,Baking Powder,3,1
1051,2/16/2014,Easton,Columbus,OH,Milk,4,2
1051,2/16/2014,Easton,Columbus,OH,Water,5,2
1051,2/16/2014,Easton,Columbus,OH,Oil,6,2
1051,2/16/2014,Easton,Columbus,OH,Cinnamon,7,3
1051,2/16/2014,Easton,Columbus,OH,Sugar,8,3
1425,1/14/2014,Crocker Park,Westlake,OH,Baking Powder,1,1
1425,1/14/2014,Crocker Park,Westlake,OH,Yeast,2,1
1425,1/14/2014,Crocker Park,Westlake,OH,Flour,3,1
1425,1/14/2014,Crocker Park,Westlake,OH,Oil,4,2
1425,1/14/2014,Crocker Park,Westlake,OH,Milk,5,2
1425,1/14/2014,Crocker Park,Westlake,OH,Water,6,2
1425,1/14/2014,Crocker Park,Westlake,OH,Rosemary,7,3
1425,1/14/2014,Crocker Park,Westlake,OH,Cinnamon,8,3
1425,1/14/2014,Crocker Park,Westlake,OH,Sugar,9,3

SQL 列数据类型是:

Table 1
StoreID - int
Date - date
StoreName - nvarchar(50)
City- nvarchar(50)
State- nvarchar(50)
Category1CSV - nvarchar(MAX)
Category2CSV - nvarchar(MAX)
Category3CSV - nvarchar(MAX)

Table2
StoreID - int
Date - date
StoreName - nvarchar(50)
City- nvarchar(50)
State - nvarchar(50)
ItemName - nvarchar(50)
ItemRank - tinyint
ItemCategory -tinyint

标有 Category1CSV、Category2CSV 和 Category3CSV 内容的表 1 列映射到表 2 列:ItemName、ItemRank、ItemCategory，其中 ItemName 是项目(例如:面粉)，ItemRank 是项目在 CSV 列表中的顺序， ItemCategory 是 1,2 或 3，具体取决于数据是来自 Category1CSV、Category2CSV 还是 Category3CSV。

这方面最重要的方面(除了拆分 CSV 列之外)是保持 CSV 列中项目的顺序。例如，StroreID 1051 的 Category1CSV 内容为“Flour,Yeast,Baking Powder”。这些将映射到 ItemName、ItemRank 和 ItemCategory 列，使得 ItemName = Flour，ItemRank = 1，ItemCategory = 1。这将是表 2 中的第一行。第二行将是 ItemName = Yeast，它是 ItemRank = 2，并且 ItemCategory = 1，依此类推，直到您得到如上表 2 所示的结果。此外，您会注意到 ItemRank 编号从 Category1CSV 列的内容开始，然后继续到 Category2CSV，最后是 Category3CSV。

在那个冗长的解释之后，是否有可能有一些 SQL 语句会为我从表 1 创建表 2？如果是这样，那会是什么样子？我打算使用 MS SQL Server Express 2012。

或者...正如有人向我建议的那样，最好在 Excel 或 Python 脚本中使用一些 VBA(可能与 Notepad++ 结合使用？)来完成此操作，然后只导入最终数据？我不在乎这两种方式，我只是不能继续手动编辑 CSV 文件，因为它非常乏味且耗时。

Answer 1

我会使用 Split 函数来拆分附加值。

我使用的 Split 函数使用一个 Numbers(一个包含数字 1 到 1,000,000 的表格)以促进拆分过程。

一旦 Numbers 表和 Split 函数就位，我将使用 CROSS APPLY 函数将 Split 应用于 CSV 列。拆分 CSV 列的代码如下所示(这只是基于您提供的数据的几个测试行)。

DECLARE @Table TABLE (val1 VARCHAR(50), val2 VARCHAR(50), val3 VARCHAR(50), csv1 VARCHAR(100), csv2 VARCHAR(100), csv3 VARCHAR(100))
INSERT INTO @Table
VALUES ('Easton', 'Columbus', 'OH', 'Flour,Yeast,Baking Powder','Milk,Water,Oil','Cinnamon,Sugar')
 , ('Crocker Park', 'Westlake', 'OH', 'Baking Powder,Yeast,Flour','Oil,Milk,Water','Rosemary,Cinnamon,Sugar')

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv1, ',')
   ) apl

UNION ALL

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv2, ',')
   ) apl

UNION ALL

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv3, ',')
   ) apl
 ORDER BY val1

根据样本日期，其输出将如下所示。

Crocker Park    Westlake    OH  Baking Powder
Crocker Park    Westlake    OH  Cinnamon
Crocker Park    Westlake    OH  Flour
Crocker Park    Westlake    OH  Milk
Crocker Park    Westlake    OH  Oil
Crocker Park    Westlake    OH  Rosemary
Crocker Park    Westlake    OH  Sugar
Crocker Park    Westlake    OH  Water
Crocker Park    Westlake    OH  Yeast
Easton          Columbus    OH  Baking Powder
Easton          Columbus    OH  Cinnamon
Easton          Columbus    OH  Flour
Easton          Columbus    OH  Milk
Easton          Columbus    OH  Oil
Easton          Columbus    OH  Sugar
Easton          Columbus    OH  Water
Easton          Columbus    OH  Yeast

下面是创建 Numbers 表的代码

DECLARE @tbl TABLE (n INT)
INSERT INTO @tbl (n)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

; WITH Num AS(
SELECT one.n
  FROM @tbl one
 CROSS JOIN @tbl two
 CROSS JOIN @tbl three
 CROSS JOIN @tbl four
 CROSS JOIN @tbl five
 CROSS JOIN @tbl six
)
SELECT ROW_NUMBER() OVER(ORDER BY n) AS n
  INTO dbo.Numbers
  FROM Num

ALTER TABLE dbo.Numbers
ALTER COLUMN n INT NOT NULL

ALTER TABLE dbo.Numbers 
ADD PRIMARY KEY (n)
GO

最后，这里是创建 Split 函数的代码。

CREATE FUNCTION dbo.Split
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN
(
SELECT val = SUBSTRING(@List, n, CHARINDEX(@Delimiter, @List + @Delimiter, n) - n)
  FROM dbo.Numbers
 WHERE n <= CONVERT(INT, LEN(@List)) 
   AND SUBSTRING(@Delimiter + @List, n, LEN(@Delimiter)) = @Delimiter
 );
GO