C# bcp to SQL Server

Question

I have written a C# console program that reads a tab delimited file and does

bcp tblName + " in " + outputfilename + " -c -T -S servername\instancename 
       -U readonly -P readonly -F2";

The first column is EmpID which is an int and second column is EMPName which is Varchar2(100), EMPLOYER VARCHAR(200).

The outputfilename sometimes has errors, while data entry people forget to enter empID in the 1st column or sometimes while entering Employee Name they go to next line.

EMPID    EMPNAME     EMPLOYER
100      Ann Taylor  A Corporation
Brian Tao B Corporation
200      Cindy
Smith   C Corporation
400      Daryl John   "D Corporation

1st row is correct.

2nd row since there is no EMPID, I want to get the row number and log it as error.

Row #3, although Smith is the last name for Cindy there is no way of knowing and so only 200 CIndy gets saved, but log should report error since Smith is not preceded by a number

Row #4, missing a double quote, report error in log file.

Can this be done?

Thanks MR

Answer 1

You can try pre-process input file in your C# application. Read it line by line, try to parse, detect all errors, report them and output result to another file which you then pass to bcp.

Answer 2

AFAIK, There is no easy way to do this with BCP. BCP is generally very intolerant of irregularities in the column/row formatting of the input file.

Since you are in C# already, my suggestion would be to consider using the SQLBulkCopy API instead. It is almost as fast as BCP, and leaves all of the input file handling up to your program. It's a bit more work than BCP initially, but once you've got a working instance, offers a lot more flexibility amd options to the programmer.

This MSDN thread has an example by sanka060707 of how to use it from c# http://social.msdn.microsoft.com/Forums/en-US/4929a0a8-0137-45f6-86e8-d11e220048c3/creating-a-new-table-in-sql-server-from-adonet-datatable?forum=adodotnetdataproviders (it's about the fifth reply to the question). Here is a copy of it (long)

public class SqlTableCreator
{
    #region Instance Variables
    private SqlConnection _connection;
    public SqlConnection Connection {
        get { return _connection; }
        set { _connection = value; }
    }

    private SqlTransaction _transaction;
    public SqlTransaction Transaction {
        get { return _transaction; }
        set { _transaction = value; }
    }

    private string _tableName;
    public string DestinationTableName {
        get { return _tableName; }
        set { _tableName = value; }
    }
    #endregion

    #region Constructor
    public SqlTableCreator() { }
    public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
    public SqlTableCreator(SqlConnection connection, SqlTransaction transaction) {
        _connection = connection;
        _transaction = transaction;
    }
    #endregion

    #region Instance Methods
    public object Create(DataTable schema) {
        return Create(schema, null);
    }
    public object Create(DataTable schema, int numKeys) {
        int[] primaryKeys = new int[numKeys];
        for (int i = 0; i < numKeys; i++) {
            primaryKeysIdea = i;
        }
        return Create(schema, primaryKeys);
    }
    public object Create(DataTable schema, int[] primaryKeys) {
        string sql = GetCreateSQL(_tableName, schema, primaryKeys);

        SqlCommand cmd;
        if (_transaction != null && _transaction.Connection != null)
            cmd = new SqlCommand(sql, _connection, _transaction);
        else
            cmd = new SqlCommand(sql, _connection);

        return cmd.ExecuteNonQuery();
    }

    public object CreateFromDataTable(DataTable table) {
        string sql = GetCreateFromDataTableSQL(_tableName, table);

        SqlCommand cmd;
        if (_transaction != null && _transaction.Connection != null)
            cmd = new SqlCommand(sql, _connection, _transaction);
        else
            cmd = new SqlCommand(sql, _connection);

        return cmd.ExecuteNonQuery();
    }
    #endregion

    #region Static Methods

    public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys) {
        string sql = "CREATE TABLE " + tableName + " (\n";

        // columns
        foreach (DataRow column in schema.Rows) {
            if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))
                sql += column["ColumnName"].ToString() + " " + SQLGetType(column) + ",\n";
        }
        sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";

        // primary keys
        string pk = "CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
        bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
        if (hasKeys) {
            // user defined keys
            foreach (int key in primaryKeys) {
                pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
            }
        }
        else {
            // check schema for keys
            string keys = string.Join(", ", GetPrimaryKeys(schema));
            pk += keys;
            hasKeys = keys.Length > 0;
        }
        pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
        if (hasKeys) sql += pk;
        sql += ")";

        return sql;
    }

    public static string GetCreateFromDataTableSQL(string tableName, DataTable table) {
        string sql = "CREATE TABLE [" + tableName + "] (\n";
        // columns
        foreach (DataColumn column in table.Columns) {
            sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
        }
        sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
        // primary keys
        if (table.PrimaryKey.Length > 0) {
            sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
            foreach (DataColumn column in table.PrimaryKey) {
                sql += "[" + column.ColumnName + "],";
            }
            sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
        }

        return sql;
    }

    public static string[] GetPrimaryKeys(DataTable schema) {
        List<string> keys = new List<string>();

        foreach (DataRow column in schema.Rows) {
            if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
                keys.Add(column["ColumnName"].ToString());
        }

        return keys.ToArray();
    }

    // Return T-SQL data type definition, based on schema definition for a column
    public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale) {
        switch (type.ToString()) {
            case "System.String":
                return "VARCHAR(" + ((columnSize == -1) ? 255 : columnSize) + ")";

            case "System.Decimal":
                if (numericScale > 0)
                    return "REAL";
                else if (numericPrecision > 10)
                    return "BIGINT";
                else
                    return "INT";

            case "System.Double":
            case "System.Single":
                return "REAL";

            case "System.Int64":
                return "BIGINT";

            case "System.Int16":
            case "System.Int32":
                return "INT";

            case "System.DateTime":
                return "DATETIME";

            default:
                throw new Exception(type.ToString() + " not implemented.");
        }
    }

    // Overload based on row from schema table
    public static string SQLGetType(DataRow schemaRow) {
        return SQLGetType(schemaRow["DataType"],
                            int.Parse(schemaRow["ColumnSize"].ToString()),
                            int.Parse(schemaRow["NumericPrecision"].ToString()),
                            int.Parse(schemaRow["NumericScale"].ToString()));
    }
    // Overload based on DataColumn from DataTable type
    public static string SQLGetType(DataColumn column) {
        return SQLGetType(column.DataType, column.MaxLength, 10, 2);
    }
    #endregion
}