I have no control of the format of the text file. All of the files are sent to me by another agency and are all very large (1 to 3 mil records).


>The obvious solution is to either create the export file will fillers or use a delimiter within the file.

-Mike

>I am running a Bulk Insert from a fixed length text file. The following code works well as long the text file contains some data for all of the fields. The problem occurs when a row in the text file is missing some values for the last couple of fields.
>
>truncate table im_ag
>
>bulk insert im_ag
>from 'c:\temp\mydata.txt'
>with (formatfile='c:\myformatfile.fmt')
>
>This is what my text file looks like:
>
>1000031951AON20020226012112664000MM C01O20020828U 00000000
>1000031951AON20020226012112664000NA R01E20020828 20031130
>1168918987APN2002090901175946401
>1000002284AON20020412011770464010FS 01O20020805NN20021231
>1000002284AON2002041201177046401 MM S01P20020618D 00000000
>
>The third row in this file is missing data and this is where the job fails.
>
>Is there any way around this?