时间戳

首页 > 解决方案 > 如何从 csv 文件创建虚拟 SQL Server 表

sql-server - 如何从 csv 文件创建虚拟 SQL Server 表

问题描述

我有多个用例，我需要从 csv 源有效地创建一个虚拟 sql 服务器表，并且我需要最好在运行中使用纯 sql 来完成它 - 即不使用过程。为了提供一些上下文，我需要将此代码连接到第三方报告引擎中；除了其他多个数据源（如 oracle 等）外，它还与 sql server 通信。

这可以在 POSTGRE 中使用一个简单的函数 split_part 来完成，因为该函数包括在 csv 连接字符串中搜索字段分隔符位置的能力；例如 ',' 逗号分隔符。不幸的是，sql server 有一个类似的函数 STRING_SPLIT (string, separator) 但请注意它没有 POSTGre 中提供的位置方面 SPLIT_PART(string, delimiter, position)。

示例源 csv：第 1 行 a、b、c、d 第 2 行 e、f、g、h 等

输出 - 使用 POSTGre db，但对 sql server 要求相同

select split_part(p.Lines, ',',1) As Col1,
split_part(p.Lines, ',',2) As Col2,
split_part(p.Lines, ',',3) As Col3,
split_part(p.Lines, ',',4) As Col4
from ( select unnest(string_to_array(:csvdata, chr(10))) as Lines)p

有什么解决办法吗？

标签： sql-server

解决方案

SQL Server 允许将 *.csv 文件作为文件系统上的虚拟数据库表进行查询。

这是一个最小的可重现示例。

SQL

SELECT *
FROM  OPENROWSET(BULK 'e:\Temp\Quark.csv'
   , FORMATFILE = 'e:\Temp\Quark.xml'  
   , ERRORFILE = 'e:\Temp\Quark.err'
   , FIRSTROW = 2 -- real data starts on the 2nd row
   , MAXERRORS = 100
   ) AS tbl;

夸克.csv

"ID"|"Name"|"Color"|"LogDate"|"Unknown"
41|Orange|Orange|2018-09-09 16:41:02.000|
42|Cherry, Banana|Red,Yellow||
43|Apple|Yellow|2017-09-09 16:41:02.000|

夸克.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <RECORD>
      <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70"/>
      <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="5" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
   </RECORD>
   <ROW>
      <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="3" NAME="Color" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="4" NAME="LogDate" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="5" NAME="Unknown" xsi:type="SQLVARYCHAR"/>
   </ROW>
</BCPFORMAT>

输出

+----+----------------+------------+-------------------------+---------+
| ID |      Name      |   Color    |         LogDate         | Unknown |
+----+----------------+------------+-------------------------+---------+
| 41 | Orange         | Orange     | 2018-09-09 16:41:02.000 | NULL    |
| 42 | Cherry, Banana | Red,Yellow | NULL                    | NULL    |
| 43 | Apple          | Yellow     | 2017-09-09 16:41:02.000 | NULL    |
+----+----------------+------------+-------------------------+---------+

推荐阅读