使用可选文本限定符批量插入

Question

我正在使用批量插入将 csv 导入到数据库。 这是逗号分隔的 csv 文件。所有字段都没有文本限定符。

但某些字段可能包含逗号作为数据的一部分。 例如，ADDRESS 字段值。这些值用双引号括起来。 仅当字段值中包含逗号时，才会出现这些双引号，否则值不会用双引号引起来。 因此，在某些行中，ADDRESS 值用双引号括起来，但在其他行中则没有。 有没有办法在批量插入命令中指定文本限定符？

我尝试使用格式文件选项进行批量插入。

BULK INSERT Test_Imported FROM 'C:\test.csv' 
WITH (FIRSTROW=0,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FORMATFILE = 'C:\test.Fmt')

但我无法将双引号作为格式文件中的可选文本限定符。

附： 这个函数实际上是更大模块的一部分，它是用 C# 编写的。 从 C# 调用批量插入命令。

csv 文件是通过电子邮件从另一个自动化系统发送的。我无法控制格式 csv 文件的内容。大约有 150 列。 每个 csv 文件平均包含 12000 行。 忘记指定数据库。它是 SQL Server 2005。

Answer 1

不幸的是，您必须预处理文件以使其保持一致。
SQL 批量操作在字段分隔符上拆分字符串。

一些选项：

• 在 C# 中处理，将未用引号引起来的逗号更改为管道 (|)
• 将文件分为 2：“和非”文件。仅当同一字段具有“

您说您无法控制格式，但您拥有的内容无法使用时，这才有效......

Answer 2

Bulk Insert 语句确实很糟糕，因为它不处理可选限定符。

TextFieldParser 类可以帮助我们清理文件 (Microsoft.VisualBasic.FileIO.TextFieldParser)

我已粘贴到使用 TextFieldParser 的函数中类来清理分隔文件，以便您可以在批量插入语句中使用它。

String newDel = CleanDelimitedFile("c:\temp.csv",new String[] {","},"\t,\t");

这是一个可以清理分隔文件的函数。

    /// <summary>
    /// This function opens a delimited file and cleans up any string quantifiers
    /// </summary>
    /// <param name="FileFullPath">Full path of the delimited string</param>
    /// <param name="CurrentDelimiter">What string / character the file uses as the delimiter</param>
    /// <param name="NewDelimiter">What new delimiter string to use</param>
    /// <returns>Returns String representation of the new delimited file</returns>
    private static String CleanDelimitedFile(String FileFullPath, String[] CurrentDelimiter, String NewDelimiter) {

        //-- if the file exists stream it to host
        if (System.IO.File.Exists( FileFullPath )) {
            Microsoft.VisualBasic.FileIO.TextFieldParser cvsParser = null;
            System.Text.StringBuilder parseResults = new System.Text.StringBuilder();
            try {
                // new parser
                cvsParser = new Microsoft.VisualBasic.FileIO.TextFieldParser(FileFullPath);
                // delimited file has certain fields enclosed in quotes
                cvsParser.HasFieldsEnclosedInQuotes = true;
                // the current delimiter
                cvsParser.Delimiters = CurrentDelimiter;
                // iterate through all the lines of the file
                Boolean FirstLine = true;
                while (!cvsParser.EndOfData ) {
                    if (FirstLine) {
                        FirstLine = false;
                    }
                    else {
                      parseResults.Append("\n");  
                    }
                    Boolean FirstField = true;
                    // iterate through each field
                    foreach (String item in cvsParser.ReadFields()) {
                        if (FirstField) {
                            parseResults.Append(item);
                            FirstField = false;
                        } 
                        else {
                            parseResults.Append(NewDelimiter + item);
                        }
                    }

                }
                return parseResults.ToString();
            }
            finally {
                if (cvsParser != null) {
                    cvsParser.Close();
                    cvsParser.Dispose();
                }
            }
        }
        return String.Empty;
    }

Answer 3

遗憾的是，SQL 2005 和 2008 导入 XLS 文件比 CSV 文件顺利得多。我从来不反对微软，但除非所有数据库管理的 ANSI 标准都发生了巨大的变化，并且文本限定符的概念被放弃（我对此非常怀疑），否则这可能是微软的专有举措。 SQL 2000 可以很好地处理文本限定符（不确定 BULK 命令，因为我一直只使用导入向导）。想象一下，当我们迁移到 2005 年时，我不得不重新设计所有流程，不导入平面文件，而是导入 XLS，我会感到多么惊讶。我只花了 16 个小时（是的，两个工作日）就得出了这个结论，那周我实际上失眠了，因为我对 MS 不允许使用文本限定符感到非常沮丧（我什至走进我老板的办公室道歉）花了这么多时间在本应 10 分钟的任务上）。具有讽刺意味的是，您无法告诉 Excel 导出任何不包含双引号文本限定符（或几乎任何其他与此相关的软件导出器）的内容。咕噜咕噜。

其中最令人沮丧的部分是 SQL 2005 导入向导有一个地方可以定义文本限定符！

...我敢说，在这次经历之后，我开始理解所有反 M$ 的言论了！

Answer 4

我知道这是一个老问题，但我有一个 TSQL 方法来处理间歇性引号分隔符。它可能不太漂亮，但它可能会对在这里找到方法的人有所帮助：

1. 导入文本文件，其中每一行都在单列（一个字段）中。
2. 使用下面的更新语句将引号之间的逗号更改为一些可识别的字符串，在本例中 *&*
3. 使用另一个更新语句删除所有引号。
4. 使用 bcp 将数据导出到新的 CSV 文件中。
5. 使用新 CSV 文件中的所有字段将批量导入到原始表中：现在没有引号，字段内逗号改为 &，因此简单的逗号分隔导入将工作。
6. 使用另一个更新语句将 & 改回逗号。

更新初始表
设置批量列 =
替换（批量列，
子字符串（批量列，
CHARINDEX('"', BulkColumn, 0),
CHARINDEX('"', BulkColumn, CHARINDEX('"', BulkColumn, 0) + 1) - CHARINDEX('"', BulkColumn, 0) + 1),
代替（
子字符串（批量列，
CHARINDEX('"', BulkColumn, 0),
CHARINDEX('"', BulkColumn, CHARINDEX('"', BulkColumn, 0) + 1) - CHARINDEX('"', BulkColumn, 0) + 1),
',',
'*&*'))
WHERE BulkColumn LIKE '%"%'


Answer 5

对我有用的是改变

ROWTERMINATOR = '\n'

为：

ROWTERMINATOR = '0x0a'