Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Export Text File

    Using SQL 2005. Need to create fixed lenght text file from table. Was able to create the file, but all the data was in one big line. Selected fixed length with field names. How can I get my text file to have field names, fixed lenght with each record on it's only line. Thank you. David

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You have to provide a sample of what you're looking for

    Are you looking for a header row?

    How about something like this

    http://weblogs.sqlteam.com/brettk/ar...4/13/4395.aspx
    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.

  3. #3
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Export Text File

    I am getting the headers in the text file, but when the users import the text file in Access it imports all the data as one long line of 100,000 + char. There are no {CR} or {LF} in the file to show each records.

    ID Name Recv Amount
    001 John 10.00
    002 Bill 20.00
    003 Eric 25.00

    instead the data looks like this:
    ID Name Recv Amount001 John 10.00002 Bill 20.0003 Eric....

    I have over 100,000 records in the files so I should get 100,000 records plus the headers. All I get is an error saying that there are too many char in the one records it trying to import.

    I am using the Task / Export Data on the SQL Server Manger to export to Flat File Destination; Fixed length with Column Header box checked.
    David

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just tried it in sql 2k and I don't have that problem...did you select the row delimiter option in the dialog?

    In any case, what version are you using?

    Also, if it were me, I'd look into automating the process

    but WHY are you putting dfata into Access when Access can just link to SQL Server? I mean, why move the data?
    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.

  5. #5
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    I am using SQL 2005 and checked all the options for Fixedl lenght. The file is going to a print vendor to print bills. User sent file to vendor and vendor got back and said there were no breaks in records. User was using Access to check the data. David

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do this

    SELECT TOP 100 * INTO TestTable FROM yourTable

    Then do the same thing and export that table, and open it in notepad and see what it look like

    And what about you, did you try importing that very same file into to Access?

    Trust no one

    and why may I ask, if they are importing it into access does it need to be fixed width and not tab delimited?

    Unless the final destination is the mainframe, which if that's the case, why would they nmeed a header row?

    So many questions

    Try the sample test table and export and let us know
    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
  •