[SQL Server 2005] Come fare una bulk insert di un file txt?

[mikeRam]

Ciao a tutti,

ho la necessità di dover importare in una tabella di SQL Server il contenuto di un file txt molto grande.

Aprendolo con notepad++ vedo che ogni riga finale presenta i caratteri "CR""LF".

Non riesco ad effettuare la insert bulk dal file miofile.txt che deve mappare in una tabella "miatabella" il contenuto del file. Non ci sono delimitatori dentro la riga e la posizione dei caratteri è fissata.

BULK INSERT server.dbo.miatabella    FROM  '....\miofile.TXT'  

 WITH (   FORMATFILE='......\InsertBulk.xml',
              ROWTERMINATOR='\n',
              BATCHSIZE= 20000  )


mio InsertBulk.xml

<?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" MAX_LENGTH="6"/>
  <FIELD ID="2" xsi:type="CharFixed" MAX_LENGTH="3"/>
  <FIELD ID="3" xsi:type="CharFixed" MAX_LENGTH="6"/>
  <FIELD ID="4" xsi:type="CharFixed" MAX_LENGTH="2"/>
  <FIELD ID="5" xsi:type="CharFixed" MAX_LENGTH="10"/>
  <FIELD ID="6" xsi:type="CharFixed" MAX_LENGTH="1"/>
  <FIELD ID="7" xsi:type="CharFixed" MAX_LENGTH="6"/>
  <FIELD ID="8" xsi:type="CharFixed" MAX_LENGTH="2"/>
  <FIELD ID="9" xsi:type="CharFixed" MAX_LENGTH="5"/>
  <FIELD ID="10" xsi:type="CharFixed" MAX_LENGTH="5"/>
  <FIELD ID="11" xsi:type="CharFixed" MAX_LENGTH="8"/>
  <FIELD ID="12" xsi:type="CharFixed" MAX_LENGTH="8"/>
  <FIELD ID="13" xsi:type="CharFixed" MAX_LENGTH="3"/>
  <FIELD ID="14" xsi:type="CharFixed" MAX_LENGTH="21"/>
  <FIELD ID="15" xsi:type="CharFixed" MAX_LENGTH="7"/>
  <FIELD ID="16" xsi:type="CharFixed" MAX_LENGTH="9"/>
  <FIELD ID="17" xsi:type="CharFixed" MAX_LENGTH="9"/>
  <FIELD ID="18" xsi:type="CharFixed" MAX_LENGTH="1"/>
  <FIELD ID="19" xsi:type="CharFixed" MAX_LENGTH="20"/>
  <FIELD ID="20" xsi:type="CharFixed" MAX_LENGTH="20"/>
  <FIELD ID="21" xsi:type="CharFixed" MAX_LENGTH="16"/>
  <FIELD ID="22" xsi:type="CharFixed" MAX_LENGTH="30"/>
  <FIELD ID="23" xsi:type="CharFixed" MAX_LENGTH="5"/>
  <FIELD ID="24" xsi:type="CharFixed" MAX_LENGTH="30"/>
  <FIELD ID="25" xsi:type="CharFixed" MAX_LENGTH="2"/>
  <FIELD ID="26" xsi:type="CharFixed" MAX_LENGTH="1"/>
  <FIELD ID="27" xsi:type="CharFixed" MAX_LENGTH="8"/>
  <FIELD ID="28" xsi:type="CharFixed" MAX_LENGTH="3"/>
  <FIELD ID="29" xsi:type="CharFixed" MAX_LENGTH="6"/>
  <FIELD ID="30" xsi:type="CharFixed" MAX_LENGTH="6"/>
  <FIELD ID="31" xsi:type="CharFixed" MAX_LENGTH="10"/>
  <FIELD ID="32" xsi:type="CharFixed" MAX_LENGTH="5"/>
  <FIELD ID="33" xsi:type="CharFixed" MAX_LENGTH="2"/>
  <FIELD ID="34" xsi:type="CharFixed" MAX_LENGTH="8"/>
  <FIELD ID="35" xsi:type="CharFixed" MAX_LENGTH="110"/>
  <FIELD ID="36" xsi:type="CharFixed" MAX_LENGTH="6"/>
 </RECORD>
</BCPFORMAT>

Mi dice che ho un errore di questo genere:
Line 41 ( che è l'ultima riga dell'xml) in format file "\\.........\InsertBulk.xml": unexpected info item.

Dove sbaglio?

Grazie

Mike "The Ram"

[mikeRam]

Risolto da me!

Non andavo a considerare il tag e non mettevo nel record il campo separatore

......
<FIELD ID="37" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</record>
<ROW>
   <COLUMN>....

</ROW>