杂物箱

• 下载二进制文件 - 2.5 MB
• 下载源代码 - 733.8 KB

引言

分析师：“有没有什么东西可以自动将文件导入数据库？”

程序员：“没有。你得用数据导入向导。”

数据分析师叹了口气，回忆起那个向导。

使用向导将文本文件导入 SQL Server 的过程大致是这样的

• 安装 SQL Server Management Studio。
• 找到“任务”，然后选择“导入数据”。
• 选择“平面文件源”。
• 浏览文件。
• 预览数据。
• 指定分隔符。
• 指定第一行是否为列名。
• （再次）预览数据。
• 转到每个列，选择正确的数据类型，或使用“建议类型”功能。
• 选择是否保存 SSIS 包以供将来使用。
• 执行它

程序员：“向导可以帮助你导入任何类型的文件。”

分析师：“那太好了。但如果有一个程序能自动帮我处理就好了。”

程序员：“抱歉。你得用向导。如果出现错误消息，修复问题后再试。”

分析师：“我有很多不同的文件。使用向导太麻烦了。这浪费我的时间。”

听到这话，程序员大喊一声汇编语言，然后把数据分析师锁进了“锁喉”状态。他握紧拳头，用指关节狠狠地按压分析师的头部，并来回摩擦。

程序员：“你带着一张没买票的票来我的地盘，还抱怨你的时间被浪费了？”

---

可悲的是，这种情况在 IT 办公室里经常发生。最近，我在教一个员工学习 SQL 时，他问道：

员工：“SQL 很棒，但我怎么把这些文件导入数据库？”

我首先想到了导入向导，但觉得不合适。如果他发现每次都要运行向导，而且很可能要处理错误消息，这会成为他学习的绊脚石。而且，我知道这样的谈话很可能像上面描绘的那样，以“锁喉”告终。

所以，我没有给他“一顿毒打”，而是决定创建一个程序，让导入 Excel 或文本文件到数据库变得更容易。

入门

杂物抽屉将文件称为“垃圾”，数据库称为“抽屉”。文件是输入，数据库是输出。两者都是连接。

要配置连接，请打开“杂物抽屉”的默认配置文件 default.xml。

<jd>
  <connections>
    <add name="input" 
         provider="file" 
         file="*.*" />
    <add name="output" 
         provider="sqlserver" 
         server="localhost" 
         database="Junk" />
  </connections>
</jd>

输入设置为文件。文件 *.* 在运行时会被更改为您指定的文件。输出设置为名为 Junk 的本地 SQL Server 数据库。对于 SQL Server，默认数据库连接使用受信任的安全机制。如果您使用的是原生帐户，可以添加 user 和 password 属性。您也可以使用 connection-string 属性。

获取文件

文件必须是 Excel（例如 .xls、.xlsx）或分隔符文本文件（例如 .csv、.txt）。

我在 Google 上搜索了 filetype:csv colors，找到了 colors.csv。使用 filetype 关键字，您可以在 Google 上找到一些非常不错的东西。这是 colors.csv 的一个样本。

Code,Name,Hex,Red,Green,Blue
air_force_blue_raf,"Air Force Blue (Raf)",#5d8aa8,93,138,168
air_force_blue_usaf,"Air Force Blue (Usaf)",#00308f,0,48,143
air_superiority_blue,"Air Superiority Blue",#72a0c1,114,160,193
alabama_crimson,"Alabama Crimson",#a32638,163,38,56
alice_blue,"Alice Blue",#f0f8ff,240,248,255
alizarin_crimson,"Alizarin Crimson",#e32636,227,38,54
alloy_orange,"Alloy Orange",#c46210,196,98,16
almond,"Almond",#efdecd,239,222,205

杂物抽屉（jd.exe）从命令行导入它，如下所示：

jd.exe -f c:\temp\colors.csv

现在可以查询了。

USE Junk;

SELECT TOP 10 Code, Name, Hex, Red, Green, Blue
FROM colors;

Code                  Name                   Hex     Red Green Blue
--------------------- ---------------------- ------- --- ----- ----
air_force_blue_raf    Air Force Blue (Raf)   #5d8aa8 93  138   168
air_force_blue_usaf   Air Force Blue (Usaf)  #00308f 0   48    143
air_superiority_blue  Air Superiority Blue   #72a0c1 114 160   193
alabama_crimson       Alabama Crimson        #a32638 163 38    56
alice_blue            Alice Blue             #f0f8ff 240 248   255
alizarin_crimson      Alizarin Crimson       #e32636 227 38    54
alloy_orange          Alloy Orange           #c46210 196 98    16
almond                Almond                 #efdecd 239 222   205
amaranth              Amaranth               #e52b50 229 43    80
amber                 Amber                  #ffbf00 255 191   0

生成的数据结构大致如下：

CREATE TABLE colors(
    Code NVARCHAR(40),
    Name NVARCHAR(42),
    Hex NVARCHAR(8),
    Red TINYINT,
    Green TINYINT,
    Blue TINYINT
);

它是如何工作的？

当我们查看上面的 colors.csv 时，很容易看出第一行是标题，随后的行是记录。

此外，我们看到值由逗号分隔。我们还识别出字段的模式。我们看到 Code、Name 和 Hex 是文本，而 Red、Green 和 Blue 是数字。

杂物抽屉必须能看到和我们一样的东西：

1. 分隔符
2. 列名（如果可用）
3. 列数据类型

查找分隔符

检查 100 行以查找分隔符。如果找到分隔符，则计算每行的平均数和标准差。

然后，具有最低变异系数的分隔符被宣布为获胜者。这为我们提供了前 100 条记录中最一致的分隔符。

默认搜索的分隔符是逗号、管道符、制表符和分号。如果您想控制分隔符，可以在输入连接中进行配置，如下所示：

<add name="input" provider="file" file="*.*">
    <delimiters>
        <add name="comma" character=","/>
        <add name="pipe" character="|"/>
        <add name="tab" character="&#009;"/>
        <add name="semicolon" character=";"/>
    </delimiters>
</add>

列名

第一行会根据获胜的分隔符进行拆分，并进行以下测试：

• 重复项
• 空值
• 空白值
• 数字
• 日期

如果存在以上任何一种情况，则第一行不适合用作列名。如果需要，将生成类似 Excel 的列名（即 A、B、C）。在 colors.csv 中，第一行没有重复项、空值、空格值、数字或日期，因此被用作列名。

数据类型

最初，每个字段都被视为 string。通常，在导入文件以进行临时查询时，string 类型是可以接受的。但是，如果您想进行类型检查，请像这样在输入连接中添加类型：

<add name="input" provider="file" file="*.*">
    <types>
        <add type="bool"/>
        <add type="byte"/>
        <add type="short"/>
        <add type="int"/>
        <add type="long"/>
        <add type="single"/>
        <add type="double"/>
        <add type="decimal"/>
        <add type="datetime"/>
    </types>
</add>

当前，类型按出现的顺序进行检查。因此，要选择最有效的类型，请先添加更严格的类型。例如，如果您先测试 short（-32,768-32,767），然后再测试 byte（0-255），那么任何“本应是 byte”的值都会变成 short。

字段中的每个值都将针对类型的限制进行检查。将使用第一个兼容的类型。如果没有类型允许所有值，则使用 string。

string 将会被测试长度。字段的长度默认为文件中最长值（+1）。如果您想控制 string 的长度，可以在连接中添加 min-length 和/或 max-length。

<add name="input" 
     provider="file" 
     file="*.*"
     min-length="64"
     max-length="4000" />

一旦值通过类型和/或长度检查，杂物抽屉就会尝试导入文件。

在代码中

JunkDrawer 可以在代码中这样使用：

JunkResponse response;
var request = new JunkRequest(@"c:\temp\colors.csv", "default.xml");
using (var scope = new AutofacJunkBootstrapper(request)) {
    response = scope.Resolve<JunkImporter>().Import();  
}

与 jd.exe 可执行文件一样，JunkRequest 需要您要导入的文件名以及一个配置。

在上面的示例中，我使用了 Autofac 来连接 JunkImporter 的依赖项。解决方案中包含了一个 JunkDrawer.Autofac 项目，以演示 JunkImporter 的组成方式。

选项

表名

默认情况下，杂物抽屉会创建一个以您的文件名（不带扩展名）命名的视图。例如，colors.csv 被命名为 colors。如果您想为您的视图命名其他名称，请在 JunkRequest 中设置 TableName 属性。

配置

如果您不提供配置，将使用 default.xml。

配置是基于文件的。您可以创建任意数量的配置。例如，要将数据导入 SQLite 而不是 SQL Server，请创建如下配置：

<jd>
    <connections>
        <add name="input" provider="file" file="*.*" />
        <add name="output"
             provider="sqlite"
             file="c:\temp\junk.sqlite3" />
    </connections>
</jd>

将其保存为 sqlite.xml。然后导入 colors.csv 到 SQLite：

jd.exe -f c:\temp\colors.csv -c sqlite.xml

MySql 和 PostgreSql 也受支持。

注意事项

杂物覆盖

如果您将同一个文件导入杂物抽屉两次，它会覆盖先前的表。不过不用担心；这只是您的“垃圾”。

杂物溢出

我称之为“杂物抽屉”，因为它允许人们将文件直接导入数据库，可能会造成混乱。您可能需要留意它，或者将您的“垃圾”数据库放在隔离的测试服务器上，以免造成损害。

结论

一旦就位，杂物抽屉就可以让您信任的朋友将他们的数据导入“杂物”数据库，并随意运行临时查询。

当然，有些文件确实非常混乱，杂物抽屉也无法处理。在这种情况下，您将不得不求助于大喊大叫、锁喉和拧麻花（或使用导入向导）。

致谢

杂物抽屉的实现离不开

• Microsoft .NET
• AutoFac - MIT
• Dapper - Apache 2
• FileHelpers - MIT
• ExcelDataReader - MIT
• SharpZipLib - GNU
• Nlog - BSD
• Cfg-Net - Apache 2
• Command Line Parser - MIT
• System.Data.SQLite
• Npgsql
• MySql.Data - GPL 2