+ Reply to Thread
Results 1 to 9 of 9

Exporting from Excel to a Fixed Length Flat File

  1. #1
    Markus L
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    "bearie" <bearie@discussions.microsoft.com> wrote in message
    news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > Is there a way to Export from Excel to a Fixed Length Flat File?


    File > Save As... > Save as type: Formatted Text (Space delimited)
    (*.prn)

    Caution:
    - insufficient column width leads to truncated cell content
    - only the active worksheet is saved.
    Make all columns wide enough and you are safe!



  2. #2
    bearie
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    Markus,

    Thank you so much!!

    We have spent many months looking for this feature. Could not find it in
    Help.

    barb

    "Markus L" wrote:

    > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > Is there a way to Export from Excel to a Fixed Length Flat File?

    >
    > File > Save As... > Save as type: Formatted Text (Space delimited)
    > (*.prn)
    >
    > Caution:
    > - insufficient column width leads to truncated cell content
    > - only the active worksheet is saved.
    > Make all columns wide enough and you are safe!
    >
    >
    >


  3. #3
    bearie
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    We worked with this a bit, and found it works great to a point.

    We have files that are 1,200 characters that need to be saved as fixed
    length flat files.

    Out only idea here is to find a print driver that will accept really large
    paper (we tried 17X11 and was still not large enough) to make this work.

    Any other ideas?

    "Markus L" wrote:

    > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > Is there a way to Export from Excel to a Fixed Length Flat File?

    >
    > File > Save As... > Save as type: Formatted Text (Space delimited)
    > (*.prn)
    >
    > Caution:
    > - insufficient column width leads to truncated cell content
    > - only the active worksheet is saved.
    > Make all columns wide enough and you are safe!
    >
    >
    >


  4. #4
    Dave Peterson
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    Saved from a previous post:

    There's a limit of 240 characters per line when you save as .prn files.

    You have a few choices (try against a copy of your worksheet):

    I'd either concatenate the cell values into another column:

    =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

    (You'll have to modify it to match what you want.)

    Then I'd copy and paste to notepad and save from there. Once I figured out that
    ugly formula, I kept it and just unhide that column when I wanted to export the
    data.

    If that doesn't work for you, maybe you could do it with a macro.

    Here's a link that provides a macro:
    http://google.com/groups?threadm=015...280a%40phx.gbl

    =========

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    bearie wrote:
    >
    > We worked with this a bit, and found it works great to a point.
    >
    > We have files that are 1,200 characters that need to be saved as fixed
    > length flat files.
    >
    > Out only idea here is to find a print driver that will accept really large
    > paper (we tried 17X11 and was still not large enough) to make this work.
    >
    > Any other ideas?
    >
    > "Markus L" wrote:
    >
    > > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > > Is there a way to Export from Excel to a Fixed Length Flat File?

    > >
    > > File > Save As... > Save as type: Formatted Text (Space delimited)
    > > (*.prn)
    > >
    > > Caution:
    > > - insufficient column width leads to truncated cell content
    > > - only the active worksheet is saved.
    > > Make all columns wide enough and you are safe!
    > >
    > >
    > >


    --

    Dave Peterson

  5. #5
    bearie
    Guest

    Exporting from Excel to a Fixed Length Flat File

    Is there a way to Export from Excel to a Fixed Length Flat File?

    We are currently creating file in Excel and copying data in Access database.
    Then exporting from Access to a Fixed Length Flat File.

    This is very cumbersome and opens up for error.

    Thank you for your help,
    Barbara J. Miller


  6. #6
    Markus L
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    "bearie" <bearie@discussions.microsoft.com> wrote in message
    news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > Is there a way to Export from Excel to a Fixed Length Flat File?


    File > Save As... > Save as type: Formatted Text (Space delimited)
    (*.prn)

    Caution:
    - insufficient column width leads to truncated cell content
    - only the active worksheet is saved.
    Make all columns wide enough and you are safe!



  7. #7
    bearie
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    Markus,

    Thank you so much!!

    We have spent many months looking for this feature. Could not find it in
    Help.

    barb

    "Markus L" wrote:

    > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > Is there a way to Export from Excel to a Fixed Length Flat File?

    >
    > File > Save As... > Save as type: Formatted Text (Space delimited)
    > (*.prn)
    >
    > Caution:
    > - insufficient column width leads to truncated cell content
    > - only the active worksheet is saved.
    > Make all columns wide enough and you are safe!
    >
    >
    >


  8. #8
    bearie
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    We worked with this a bit, and found it works great to a point.

    We have files that are 1,200 characters that need to be saved as fixed
    length flat files.

    Out only idea here is to find a print driver that will accept really large
    paper (we tried 17X11 and was still not large enough) to make this work.

    Any other ideas?

    "Markus L" wrote:

    > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > Is there a way to Export from Excel to a Fixed Length Flat File?

    >
    > File > Save As... > Save as type: Formatted Text (Space delimited)
    > (*.prn)
    >
    > Caution:
    > - insufficient column width leads to truncated cell content
    > - only the active worksheet is saved.
    > Make all columns wide enough and you are safe!
    >
    >
    >


  9. #9
    Dave Peterson
    Guest

    Re: Exporting from Excel to a Fixed Length Flat File

    Saved from a previous post:

    There's a limit of 240 characters per line when you save as .prn files.

    You have a few choices (try against a copy of your worksheet):

    I'd either concatenate the cell values into another column:

    =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

    (You'll have to modify it to match what you want.)

    Then I'd copy and paste to notepad and save from there. Once I figured out that
    ugly formula, I kept it and just unhide that column when I wanted to export the
    data.

    If that doesn't work for you, maybe you could do it with a macro.

    Here's a link that provides a macro:
    http://google.com/groups?threadm=015...280a%40phx.gbl

    =========

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    bearie wrote:
    >
    > We worked with this a bit, and found it works great to a point.
    >
    > We have files that are 1,200 characters that need to be saved as fixed
    > length flat files.
    >
    > Out only idea here is to find a print driver that will accept really large
    > paper (we tried 17X11 and was still not large enough) to make this work.
    >
    > Any other ideas?
    >
    > "Markus L" wrote:
    >
    > > "bearie" <bearie@discussions.microsoft.com> wrote in message
    > > news:5C4A03B5-A8C7-4DE9-9DB5-C534833ACA02@microsoft.com...
    > > > Is there a way to Export from Excel to a Fixed Length Flat File?

    > >
    > > File > Save As... > Save as type: Formatted Text (Space delimited)
    > > (*.prn)
    > >
    > > Caution:
    > > - insufficient column width leads to truncated cell content
    > > - only the active worksheet is saved.
    > > Make all columns wide enough and you are safe!
    > >
    > >
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1