Kaze,

You should be able to do variable length records using a format file. You will need to define field and record delimiters. (I think that the default are '|' and Carriage return / Line Feed)

You can bcp out from the command line to create the format file and change all the datatypes to 'c' for character to create the format file. In the example below, I used the field separator of '|' but you can use any char or string which does not occur in the data (if the field separator occurs in the data, it messes up the import.)

8.0
4
1 SQLCHAR 0 12 "|" 1 FeedIdn ""
2 SQLCHAR 0 12 "|" 2 SourceIdn ""
3 SQLCHAR 0 80 "|" 3 FeedName ""
4 SQLCHAR 0 24 "|" 4 LastUpdatedAt ""


As an alternative, you can call an external program using:

xp_cmdshell 'your command goes here'

The first solution is my favorite because it is faster and has less moving parts.

. . . Tom

Tom Zeblisky
Reuters





Hi,
I've been working on a store procedure to load a variable length record file into a table by using bulk insert with a format file. Since the record length varies, i'm having a hard time loading it in properly. I wrote a VB script to pad the file to a set record length. Does anyone know if i can call a VB script from a Store Procedure? Or does anyone know a better idea to load a variable record length file into a table?
Thanks!
Kaze







-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page