Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: 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,

    Shab

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Wouldn'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.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •