How to get BCP to generate a Format File for importing fixed-width data into a SQL Server table?

Question

The bcp command that I am using:

bcp TableName format nul -c -f c:\folder\TargetFile.xml -x -S ServerName -T -q

I think I just need the fields to have a type of xsi:type="CharFixed" rather then xsi:type="CharTerm".

The xml that it creates which doesn't work for me:

<?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="\t" MAX_LENGTH="24" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="150" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="150" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="UID" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="2" NAME="FNAME" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="LNAME" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="4" NAME="PHONE" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="5" NAME="Target" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>

What I actually need: (xsi:type="CharFixed")

<?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="CharFixed" LENGTH="3"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="3"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLCHAR" LENGTH="3"/>
  <COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLCHAR" LENGTH="3"/>
</ROW>
</BCPFORMAT>

Solution

Here is the method I created to help me resolve my issue...

private XmlDocument CreateFormatFile()
    {
        const string xsiURI = "http://www.w3.org/2001/XMLSchema-instance";
        var ff = new XmlDocument();
        var dec = ff.CreateXmlDeclaration("1.0", null, null);
        ff.AppendChild(dec);
        var bcpFormat = ff.CreateElement("BCPFORMAT");
        bcpFormat.SetAttribute("xmlns", "http://schemas.microsoft.com/sqlserver/2004/bulkload/format");
        bcpFormat.SetAttribute("xmlns:xsi", xsiURI);
        var record = ff.CreateElement("RECORD");
        var row = ff.CreateElement("ROW");
        for (var x = 0; x < Columns.Count; x++)
        {
            var col = Columns[x];
            var id = (col.Index + 1).ToString();
            var length = col.Length.ToString();
            var column = ff.CreateElement("COLUMN");
            column.SetAttribute("SOURCE", id);
            column.SetAttribute("NAME", col.Name);
            column.SetAttribute("type", xsiURI, "SQLCHAR");
            column.SetAttribute("LENGTH", length);


            var field = ff.CreateElement("FIELD");
            field.SetAttribute("ID", id);
            if (x != Columns.Count - 1)
            {
                field.SetAttribute("type", xsiURI, "CharFixed");
                field.SetAttribute("LENGTH", length);
            }
            else
            {
                field.SetAttribute("type", xsiURI, "CharTerm");
                field.SetAttribute("TERMINATOR", @"\r\n");
            }

            record.AppendChild(field);
            row.AppendChild(column);
        }
        bcpFormat.AppendChild(record);
        bcpFormat.AppendChild(row);
        ff.AppendChild(bcpFormat);
        return ff;
    }

Comments

[kdelmonte]

Ok so I tried your version and I get: xsi:type="CharPrefix" instead of (xsi:type="CharFixed") I also get this error in SQLSERVER but I know that the fields have the exact length that the XMP depicts... Msg 4866, Level 16, State 7, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly. any ideas?