与 SQL BCP 作斗争（uniqidentifier、列上的默认值...）

Question

编辑：我唯一悬而未决的问题是c）（文件上的真与假，数据库上的位，我既无法更改文件也无法更改数据库方案，有数百太字节我无法触及）。

系统接收具有某种格式的文件（实际上有数十万个）。事情是： a) 第一种类型是 uniqidentifier（稍后会详细介绍） b) 在数据库上，表的前 4 个值由数据库生成（它们与日期相关），这意味着在文件中找不到这 4 个值（所有其余值 - 并且按顺序 -，即使它是始终将其表示为文本或为空） c) 位值在文件上用 False/True 表示。

因此，1 的问题是在我收到的输入文本文件中，uniqidentifier 使用括号。当我尝试使用 bcp 命令工具生成具有 nul 选项格式的文件时，它会使其成为具有 37 个字符的 sqlchar（这对我来说没有意义，因为它要么是 36，要么是 38）。

行分隔符为“+++\r\n”，列分隔符为“©®©”。

我将如何生成格式文件？我已经被这个问题困扰了一段时间了，我以前从未使用过 bcp，而且我遇到的错误并没有说明太多（“BCP 数据文件中遇到意外的 EOF”）

我是否应该指定格式文件还是只是我想从我得到的文件中读取的文件？

谢谢！

注意：我无法提供 SQL 架构，因为它是为我工作的公司提供的。但它几乎是：smalldate、tinyinttinyinttinyint（这四个是由数据库生成的）、uniqidentifier、chars、chars、更多varchars、一些位、更多varchars、一些nvarchar。除数据库生成的值外，所有值都接受 null。

我当前的问题是跳过前 4 列。

http://msdn.microsoft.com/en- us/library/ms179250(v=SQL.105).aspx

我遵循了该指南，但不知何故它不起作用。这是更改（我只是硬更改列名称以保护项目的隐私，即使这听起来很愚蠢）

这是用 bcp 生成的（格式为 nul -c）-注意我将其作为链接，因为它是没那么短- http://pastebin.com/4UkpPp1n

第二个，应该做同样的事情，但忽略前 4 个列位于下一个粘贴箱中： http://pastebin.com/Lqj6XSbW

但它不起作用。错误是“Error = [Microsoft][SQL Native Client]为 bcp 操作提供的字段数小于服务器上的列数。”，这应该是所有这些的目的。

任何帮助将不胜感激。

Answer 1

我将创建一个新表，其中 GUID 为 CHAR(38)。将数据导入到此暂存表中，然后使用 CAST(SUBSTRING(GUID, 2, 36) AS UNIQUEIDENTIFIER) 进行转换，以将暂存数据导入到永久表中。此方法也适用于奇数格式的日期、带有货币符号的数字或通常任何类型的格式不良的输入。

BCP 格式文件有点棘手，但从根本上来说并不太复杂。如果该部分继续给您带来麻烦，一种选择是将整行导入为单个 VARCHAR(1000) 字段，然后在 SQL 中将其拆分 - 如果您熟悉 SQL 文本处理是。

或者，如果您熟悉其他编程语言（例如 Perl 或 C#），则可以创建一个脚本来将输入预处理为更友好的形式（例如制表符分隔）。如果您不熟悉其他编程语言，我建议您选择一种并开始！ SQL 是一种很棒的语言，但有时您需要不同的工具；它不适合文本处理。

如果您熟悉 C#，这里是我生成格式文件的代码。没有人会取笑我的 Whitestone 缩进 :P

private static string  CreateFormatFile(string filePath, SqlConnection connection, string tableName, string[] sourceFields, string[] destFields, string fieldDelimiter, string fieldQuote)
    {
    string         formatFilePath = filePath + ".fmt";
    StreamWriter   formatFile     = null;
    SqlDataReader  data           = null;
    try
        {
        // Load the metadata for the destination table, so we can look up fields' ordinal positions
        SqlCommand  command = new SqlCommand("SELECT TOP 0 * FROM " + tableName, connection);
                    data    = command.ExecuteReader(CommandBehavior.SchemaOnly);
        DataTable   schema  = data.GetSchemaTable();

        Dictionary<string, Tuple<int, int>>  metadataByField = new Dictionary<string, Tuple<int, int>>();
        foreach (DataRow row in schema.Rows)
            {
            string  fieldName = (string)row["ColumnName"];
            int     ordinal   = (int)row["ColumnOrdinal"] + 1;
            int     maxLength = (int)row["ColumnSize"];
            metadataByField.Add(fieldName, new Tuple<int, int>(ordinal, maxLength));
            }

        // Begin the file, including its header rows
        formatFile = File.CreateText(formatFilePath);
        formatFile.WriteLine("10.0");
        formatFile.WriteLine(sourceFields.Length);

        // Certain strings need to be escaped to use them in a format file
        string  fieldQuoteEscaped     = fieldQuote     == "\"" ? "\\\"" : fieldQuote;
        string  fieldDelimiterEscaped = fieldDelimiter == "\t" ? "\\t"  : fieldDelimiter;

        // Write a row for each source field, defining its metadata and destination field
        for (int i = 1;  i <= sourceFields.Length;  i++)
            {
            // Each line contains (separated by tabs): the line number, the source type, the prefix length, the field length, the delimiter, the destination field number, the destination field name, and the collation set
            string  prefixLen   = i != 1 || fieldQuote == null ? "0" : fieldQuote.Length.ToString();
            string  fieldLen;
            string  delimiter   = i < sourceFields.Length ? fieldQuoteEscaped + fieldDelimiterEscaped + fieldQuoteEscaped : fieldQuoteEscaped + @"\r\n";
            string  destOrdinal;
            string  destField   = destFields[i - 1];
            string  collation;
            if (destField == null)
                {
                // If a field is not being imported, use ordinal position zero and a placeholder name
                destOrdinal = "0";
                fieldLen    = "32000";
                destField   = "DUMMY";
                collation   = "\"\"";
                }
            else
                {
                Tuple<int, int>  metadata;
                if (metadataByField.TryGetValue(destField, out metadata) == false)  throw new ApplicationException("Could not find field \"" + destField + "\" in table \"" + tableName + "\".");
                destOrdinal = metadata.Item1.ToString();
                fieldLen    = metadata.Item2.ToString();
                collation   = "SQL_Latin1_General_CP1_CI_AS";
                }
            string  line = String.Join("\t", i, "SQLCHAR", prefixLen, fieldLen, '"' + delimiter + '"', destOrdinal, destField, collation);
            formatFile.WriteLine(line);
            }

        return formatFilePath;
        }
    finally
        {
        if (data       != null)  data.Close();
        if (formatFile != null)  formatFile.Close();
        }
    }

由于某种原因，我当时没有为数据读取器使用 using 块。

Answer 2

BCP 似乎不可能将 True 和 False 理解为位值。最好使用 SSIS 或首先替换文本内容（创建视图或类似的东西不是一个好主意，它的开销更大）。