automated import from fixed width flat file

.... oh just realised that SP is for importing CSV files. It will need to be changed to import fixed width.

This looks great, but yes, I need it for a flat file. If you can give me code that deals with fixed width that would be great.  Once again, they will all have the same layout and I have a list with the field lengths for all 731 fields.

Thanks.

Doug

you will need to have a format file, which you can use along with the BULK INSERT, which specifies the columns and their lengths

"To import data from data files with fixed-length or fixed-width fields, you must use a format file"

Preparing Data for Bulk Export or Import
http://msdn2.microsoft.com/en-us/library/ms188609.aspx

Thanks.  I am feeling very slow.  I would love a basic description of how to set this up.  Am I creating a couple of stored procedures, and is the bulk insert a command for that.?  I see what I think is a format for defining the fixed width tables (copied below).  

I could use a short, sample code on this, like an actual SP.  I'm not a SQL Server person, but rather a general developer that uses SQL Server for programming db apps.  

Thanks.


<?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="10"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />
    <COLUMN SOURCE="2" NAME="C2" xsi:type="SQLINT" />
  </ROW>
</BCPFORMAT>

you need to create a .fmt Format file and reference that file in the BULK INSERT statement thus:

BULK INSERT myTbl
FROM 'c:\myFile.txt'
WITH (FORMATFILE='c:\FormatFile.fmt')

see these links for info on creating a fmt file:

Using the bcp Format File
http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm

Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx

Ok, just to be clear, since I'm confused about the difference between bcp and bulk insert.  The "creating a format file" directions is about creating a bcp format file (see below).  But I can creat an xml type file as below and name it fileformat.fmt and that will be the file I use for your simple bulk insert command you have above?  

Sorry for my slowness on this......

<?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="12"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
      MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
      MAX_LENGTH="30"
      COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

Thanks nmcdermaid for the detailed explanation.

I did finally do more reading on it and figured out to use the text format.  The MS documentation really is not as robust as I would like.  I had one head banging moment where I couldn't get it to work and it turned out that I didn't put a CR at the end of the last line of the text file.  Somebody on some user group mentioned that and that was the problem.

I'm still having issues getting it in correctly.  It's hard because they are HUGE files with 731 fields and a total of 130 million records.  I'm going to break it up though.

Thanks again.