title: Using bcp to load data into sql server
body: <p>I have a fixed-length text file that I need to import into my table on sql-server-2008.</p>
  
  <p>I am using this script to upload the file:</p>
  
  <pre><code>insert into imsdata
  
  select *
  from openrowset (bulk 'e:\imsdata.txt',
  formatfile='e:\myformatfile.xml'
  ) as t1;
  </code></pre>
  
  <p>The importing is working; however, it is not working correctly.</p>
  
  <p>Here is what the format file looks like:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;RECORD&gt;
  &lt;FIELD ID="1" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="2" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="3" xsi:type="NativeFixed" LENGTH="16"/&gt;
  &lt;FIELD ID="4" xsi:type="NativeFixed" LENGTH="12"/&gt;
  &lt;FIELD ID="5" xsi:type="NativeFixed" LENGTH="16"/&gt;
  &lt;FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/&gt;
  &lt;FIELD ID="7" xsi:type="NativeFixed" LENGTH="15"/&gt;
  &lt;FIELD ID="8" xsi:type="NativeFixed" LENGTH="25"/&gt;
  &lt;FIELD ID="9" xsi:type="NativeFixed" LENGTH="20"/&gt;
  &lt;FIELD ID="10" xsi:type="NativeFixed" LENGTH="25"/&gt;
  &lt;FIELD ID="11" xsi:type="NativeFixed" LENGTH="5"/&gt;
  &lt;FIELD ID="12" xsi:type="NativeFixed" LENGTH="3"/&gt;
  &lt;FIELD ID="13" xsi:type="NativeFixed" LENGTH="3"/&gt;
  &lt;FIELD ID="14" xsi:type="NativeFixed" LENGTH="3"/&gt;
  &lt;FIELD ID="15" xsi:type="NativeFixed" LENGTH="12"/&gt;
  &lt;FIELD ID="16" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="17" xsi:type="NativeFixed" LENGTH="15"/&gt;
  &lt;FIELD ID="18" xsi:type="NativeFixed" LENGTH="30"/&gt;
  &lt;FIELD ID="19" xsi:type="NativeFixed" LENGTH="15"/&gt;
  &lt;FIELD ID="20" xsi:type="NativeFixed" LENGTH="4"/&gt;
  &lt;FIELD ID="21" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="22" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="23" xsi:type="NativeFixed" LENGTH="1"/&gt;
  &lt;FIELD ID="24" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="25" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="26" xsi:type="NativeFixed" LENGTH="20"/&gt;
  &lt;FIELD ID="27" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="28" xsi:type="NativeFixed" LENGTH="4"/&gt;
  &lt;FIELD ID="29" xsi:type="NativeFixed" LENGTH="100"/&gt;
  &lt;FIELD ID="30" xsi:type="NativeFixed" LENGTH="20"/&gt;
  &lt;FIELD ID="31" xsi:type="NativeFixed" LENGTH="12"/&gt;
  &lt;FIELD ID="32" xsi:type="NativeFixed" LENGTH="2"/&gt;
  &lt;FIELD ID="33" xsi:type="NativeFixed" LENGTH="1"/&gt;
  &lt;FIELD ID="34" xsi:type="NativeFixed" LENGTH="50"/&gt;
  &lt;FIELD ID="35" xsi:type="NativeFixed" LENGTH="50"/&gt;
  &lt;FIELD ID="36" xsi:type="NativeFixed" LENGTH="50"/&gt;
  &lt;FIELD ID="37" xsi:type="NativeFixed" LENGTH="50"/&gt;
  &lt;FIELD ID="38" xsi:type="NativeFixed" LENGTH="30"/&gt;
  &lt;FIELD ID="39" xsi:type="NativeFixed" LENGTH="15"/&gt;
  &lt;FIELD ID="40" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="41" xsi:type="NativeFixed" LENGTH="20"/&gt;
  &lt;FIELD ID="42" xsi:type="NativeFixed" LENGTH="9"/&gt;
  &lt;FIELD ID="43" xsi:type="NativeFixed" LENGTH="7"/&gt;
  &lt;FIELD ID="44" xsi:type="NativeFixed" LENGTH="3"/&gt;
  &lt;FIELD ID="45" xsi:type="NativeFixed" LENGTH="1"/&gt;
  &lt;FIELD ID="46" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="47" xsi:type="NativeFixed" LENGTH="1"/&gt;
  &lt;FIELD ID="48" xsi:type="NativeFixed" LENGTH="15"/&gt;
  &lt;FIELD ID="49" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="50" xsi:type="NativeFixed" LENGTH="5"/&gt;
  &lt;FIELD ID="51" xsi:type="NativeFixed" LENGTH="20"/&gt;
  &lt;FIELD ID="52" xsi:type="NativeFixed" LENGTH="5"/&gt;
  &lt;FIELD ID="53" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="54" xsi:type="NativeFixed" LENGTH="5"/&gt;
  &lt;FIELD ID="55" xsi:type="NativeFixed" LENGTH="10"/&gt;
  &lt;FIELD ID="56" xsi:type="NativeFixed" LENGTH="46"/&gt;
  
  &lt;FIELD ID="57" xsi:type="CharTerm" TERMINATOR="\r\n" /&gt; 
  
  &lt;/RECORD&gt;
  &lt;ROW&gt;&lt;COLUMN SOURCE="1" NAME="TransactionCode" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="2" NAME="TransmissionDate" xsi:type="SQLDATE" LENGTH="10" /&gt;
  &lt;COLUMN SOURCE="3" NAME="ProfAcctID" xsi:type="SQLCHAR" LENGTH="16"/&gt;
  &lt;COLUMN SOURCE="4" NAME="TerritoryID" xsi:type="SQLCHAR" LENGTH="12"/&gt;
  &lt;COLUMN SOURCE="5" NAME="OrgID" xsi:type="SQLCHAR" LENGTH="16"/&gt;
  &lt;COLUMN SOURCE="6" NAME="UserID" xsi:type="SQLCHAR" LENGTH="8"/&gt;
  &lt;COLUMN SOURCE="7" NAME="Prefix" xsi:type="SQLCHAR" LENGTH="15"/&gt;
  &lt;COLUMN SOURCE="8" NAME="FirstName" xsi:type="SQLCHAR" LENGTH="25"/&gt;
  &lt;COLUMN SOURCE="9" NAME="MiddleName" xsi:type="SQLCHAR" LENGTH="20"/&gt;
  &lt;COLUMN SOURCE="10" NAME="LastName" xsi:type="SQLCHAR" LENGTH="25"/&gt;
  &lt;COLUMN SOURCE="11" NAME="Suffix" xsi:type="SQLCHAR" LENGTH="5"/&gt;
  &lt;COLUMN SOURCE="12" NAME="SpecialtyPrimaryCode" xsi:type="SQLCHAR" LENGTH="3"/&gt;
  &lt;COLUMN SOURCE="13" NAME="SpecialtySecondaryCode" xsi:type="SQLCHAR" LENGTH="3"/&gt;
  &lt;COLUMN SOURCE="14" NAME="SpecialtyTertiaryCode" xsi:type="SQLCHAR" LENGTH="3"/&gt;
  &lt;COLUMN SOURCE="15" NAME="ProfessionalID1" xsi:type="SQLCHAR" LENGTH="12"/&gt;
  &lt;COLUMN SOURCE="16" NAME="ProfessionalID1Source" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="17" NAME="CertificationCode" xsi:type="SQLCHAR" LENGTH="15"/&gt;
  &lt;COLUMN SOURCE="18" NAME="AcctSubType" xsi:type="SQLCHAR" LENGTH="30"/&gt;
  &lt;COLUMN SOURCE="19" NAME="ProfSubType" xsi:type="SQLCHAR" LENGTH="15"/&gt;
  &lt;COLUMN SOURCE="20" NAME="BirthYear" xsi:type="SQLCHAR"/&gt;
  &lt;COLUMN SOURCE="21" NAME="BirthMonth" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="22" NAME="BirthDay" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="23" NAME="Gender" xsi:type="SQLCHAR" LENGTH="1"/&gt;
  &lt;COLUMN SOURCE="24" NAME="LastCallDate" xsi:type="SQLDATE"/&gt;
  &lt;COLUMN SOURCE="25" NAME="Status" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="26" NAME="StateLicenseNumber" xsi:type="SQLCHAR" LENGTH="20"/&gt;
  &lt;COLUMN SOURCE="27" NAME="StateLicenseNumberExp" xsi:type="SQLDATE"/&gt;
  &lt;COLUMN SOURCE="28" NAME="ActualYTDCalls" xsi:type="SQLCHAR" LENGTH="4"/&gt;
  &lt;COLUMN SOURCE="29" NAME="Email" xsi:type="SQLCHAR" LENGTH="100"/&gt;
  &lt;COLUMN SOURCE="30" NAME="FaxNumber" xsi:type="SQLCHAR" LENGTH="20"/&gt;
  &lt;COLUMN SOURCE="31" NAME="ProfessionalID2" xsi:type="SQLCHAR" LENGTH="12"/&gt;
  &lt;COLUMN SOURCE="32" NAME="ProfessionalID2Source" xsi:type="SQLCHAR" LENGTH="2"/&gt;
  &lt;COLUMN SOURCE="33" NAME="CallLocationTypeCode" xsi:type="SQLCHAR" LENGTH="1"/&gt;
  &lt;COLUMN SOURCE="34" NAME="Address1" xsi:type="SQLCHAR" LENGTH="50"/&gt;
  &lt;COLUMN SOURCE="35" NAME="Address2" xsi:type="SQLCHAR" LENGTH="50"/&gt;
  &lt;COLUMN SOURCE="36" NAME="Address3" xsi:type="SQLCHAR" LENGTH="50"/&gt;
  &lt;COLUMN SOURCE="37" NAME="Address4" xsi:type="SQLCHAR" LENGTH="50"/&gt;
  &lt;COLUMN SOURCE="38" NAME="City" xsi:type="SQLCHAR" LENGTH="30"/&gt;
  &lt;COLUMN SOURCE="39" NAME="StateorProvinceCode" xsi:type="SQLCHAR" LENGTH="15"/&gt;
  &lt;COLUMN SOURCE="40" NAME="ZipCodeorPostalCode" xsi:type="SQLCHAR" LENGTH="10"/&gt;
  &lt;COLUMN SOURCE="41" NAME="PhoneNumber" xsi:type="SQLCHAR" LENGTH="20"/&gt;
  &lt;COLUMN SOURCE="42" NAME="DEANumber" xsi:type="SQLCHAR" LENGTH="9"/&gt;
  &lt;COLUMN SOURCE="43" NAME="IMSPrescriberID" xsi:type="SQLCHAR" LENGTH="7"/&gt;
  &lt;COLUMN SOURCE="44" NAME="IMSLocation" xsi:type="SQLCHAR" LENGTH="3"/&gt;
  &lt;COLUMN SOURCE="45" NAME="DEAStateLicenseValidation" xsi:type="SQLCHAR" LENGTH="1"/&gt;
  &lt;COLUMN SOURCE="46" NAME="Client" xsi:type="SQLCHAR" LENGTH="10"/&gt;
  &lt;COLUMN SOURCE="47" NAME="TransactionType" xsi:type="SQLCHAR" LENGTH="1"/&gt;
  &lt;COLUMN SOURCE="48" NAME="Service" xsi:type="SQLCHAR" LENGTH="15"/&gt;
  &lt;COLUMN SOURCE="49" NAME="DEAExpirationDate" xsi:type="SQLCHAR" LENGTH="10"/&gt;
  &lt;COLUMN SOURCE="50" NAME="PrescriberStatus" xsi:type="SQLCHAR" LENGTH="5"/&gt;
  &lt;COLUMN SOURCE="51" NAME="DEAScheduleClass" xsi:type="SQLCHAR" LENGTH="20"/&gt;
  &lt;COLUMN SOURCE="52" NAME="DEAAuthorizationStatus" xsi:type="SQLCHAR" LENGTH="5"/&gt;
  &lt;COLUMN SOURCE="53" NAME="DEAAuthorizationStatusDate" xsi:type="SQLCHAR" LENGTH="10"/&gt;
  &lt;COLUMN SOURCE="54" NAME="StateLicenseAuthorizationStatusCode" xsi:type="SQLCHAR" LENGTH="5"/&gt;
  &lt;COLUMN SOURCE="55" NAME="StateLicenseAuthorizationStatusDate" xsi:type="SQLCHAR" LENGTH="10"/&gt;
  &lt;COLUMN SOURCE="56" NAME="Expansion" xsi:type="SQLCHAR" LENGTH="46"/&gt;
  
  &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  
  <p>I am clearly doing something wrong with the format file because when the data gets imported, it gets shifted over to the right. </p>
  
  <p><strong>Question</strong>:</p>
  
  <p>What should the xml format file look like if I want to import a fixed-length (fixed-column-length) text file?</p>