CODEWITHSUNDEEP
sqlsql-serverxmlbcp
kdelmonte
kdelmonte

Reputation: 680

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

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>

Upvotes: 2

Views: 13156

Answers (2)

kdelmonte
kdelmonte

Reputation: 680

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;
    }

Upvotes: 3

Bilal
Bilal

Reputation: 922

Try using the bcp Native Format Option:

How bcp Handles Data in Native Format

...

char or varchar data

At the beginning of each char or varchar field, bcp adds the prefix length.

You'd use the "-n" option instead of "-c":

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

Upvotes: 0

Related Questions