automated import from fixed width flat file

I'm not aware of a format file ever being XML. All the format files I have used are straight text files.

If you look up format files in books online, and choose the first entry, you'll find this example of a format file:


8.0
9
1  SQLCHAR  0  11 ","      1  au_id       SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 ","      2  au_lname    SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  20 ","      3  au_fname    SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  12 ","      4  phone       SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  0  ""       5  address     SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  20 ","      6  city        SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  2  ","      7  state       SQL_Latin1_General_Cp437_BIN
8  SQLCHAR  0  0  ""       8  zip         SQL_Latin1_General_Cp437_BIN
9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN



Thats the format you are aiming for. The format file tells BULK INSERT or BCP.EXE where each fixed width field sits, and what data type it is.

This is in SQL 2000 right?



BULK INSERT and BCP are almost exactly the same.

However BULK INSERT is a T-SQL command - you submit it over a database connection

BCP.EXE is an EXE, run from the command line.


They both use exactly the same kind of format file though.


Once you have successfuly manually imported one of your files using BULK INSERT, you can either append them all together and insert in one go, or use the link I provided for a SP that will loop through a folder and BULK INSERT the files.