How to bulk insert a file with fixed row length and no row terminator?

Hi All,

I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is.
In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in.
I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths?

Thanks,

ShabWouldn't know how to do this other than using a format-file. An handy way is to use 'bcp' from the command prompt and dump the file into the table. You can have it create a format-file for you.|||Try this:

create table testimport(col1 varchar(5))

BULK INSERT testimport FROM 'c:\test\test.txt'
WITH (
FORMATFILE = 'c:\test\test.fmt'
)

test.fmt:

8.0
1
1 SQLCHAR 0 5 "" 1 col1 Latin1_General_BIN|||You need a format file...

Do you have books online?

Look up BULK INSERT or bcp and you'll find it...

What version are you running?

Wait, I'll go look for a sample...