+ Reply to Thread
Results 1 to 8 of 8

How to Export as text file with NO delimiters or modifications?

  1. #1
    Registered User
    Join Date
    08-08-2006
    Posts
    3

    How to Export as text file with NO delimiters or modifications?

    hello all,

    I am simply trying to save a sheet as a text file with the column contents saved as a simple long string of data, regardless of content.

    Ex. If my columns were this: (underscores represents columns, the pipes and ampersands are actual cell content).

    a213_____|_____text1_____&_____14.44000_____|_____endtext

    I would want the text file to read:

    a213|text1&14.44000|endtext

    None of the "Save As" choices allow this. All end up with at least tab's separating the original cell contents. Also tried changing the delimiter in the control panel, but NULL is not an allowed choice. And opening the csv in any text editor still has tabs as separators anyway.

    Thanks
    Scott
    Last edited by MojoNixon; 08-08-2006 at 06:24 PM.

  2. #2
    Pete_UK
    Guest

    Re: How to Export as text file with NO delimiters or modifications?

    Try saving the file as a .dbf file - these are fixed width with no
    delimeters.

    Hope this helps.

    Pete

    MojoNixon wrote:
    > hello all,
    >
    > I am simply trying to save a sheet as a text file with the column
    > contents saved as a simple long string of data, regardless of content.
    >
    > Ex. If my columns were this: (underscores represents columns, the pipes
    > and ampersands are actual cell content).
    >
    > a213_____|_____text1_____&_____14.44000_____|_____endtext
    >
    > I would want the text file to read:
    >
    > a213|text1&14.44000|endtext
    >
    > None of the "Save As" choices allow this. All end up with at least
    > tab's separating the original cell contents. Also tried changing the
    > delimiter in the control panel, but NULL is not an allowed choice. And
    > opening the csv in any text editor still has tabs as separators
    > anyway.
    >
    > Thanks
    > Scott
    >
    >
    > --
    > MojoNixon
    > ------------------------------------------------------------------------
    > MojoNixon's Profile: http://www.excelforum.com/member.php...o&userid=37251
    > View this thread: http://www.excelforum.com/showthread...hreadid=569672



  3. #3
    LenB
    Guest

    Re: How to Export as text file with NO delimiters or modifications?

    Hi Scott.
    If I understand you right, this macro will do it. Put your own filename
    and path in place of c:\temp\output100.txt. I also made the assumption
    that there are no blank cells in column A, and that all rows end with a
    cell containing "endtext". The macro can be modified if this isn't
    true. As written, it will do all rows starting at 1 until it gets to a
    blank cell in column A. I put in an arbitrary limit of 100 columns too,
    in case there isn't an endtext cell. If you need help entering or
    running the macro, post back and I will help with it.
    Len


    Sub MakeTextFile()
    Dim strTemp As String
    Dim strCellText As String
    Dim strFileName As String
    Dim intOutFile As Integer
    Dim intColumn As Integer

    intOutFile = FreeFile
    strFileName = "c:\temp\output100.txt" 'or whereever/whatever
    Open strFileName For Output As intOutFile
    Range("A1").Activate 'change A1 to where you want to start
    strTemp = ActiveCell.Value
    Do While Len(strTemp) > 0
    intColumn = 1
    strCellText = "" 'initialize
    'loop thru the columns until endtext (or max columns)
    Do While UCase(strCellText) <> "ENDTEXT" And intColumn < 100
    strCellText = Cells(ActiveCell.Row, intColumn).Value
    Print #intOutFile, strCellText; 'semicolon prevents new line
    intColumn = intColumn + 1
    Loop
    'go to a new line in the output file
    Print #intOutFile,

    ActiveCell.Offset(1, 0).Activate 'go down a row
    strTemp = ActiveCell.Value
    Loop
    Close intOutFile
    End Sub


    MojoNixon wrote:
    > hello all,
    >
    > I am simply trying to save a sheet as a text file with the column
    > contents saved as a simple long string of data, regardless of content.
    >
    > Ex. If my columns were this: (underscores represents columns, the pipes
    > and ampersands are actual cell content).
    >
    > a213_____|_____text1_____&_____14.44000_____|_____endtext
    >
    > I would want the text file to read:
    >
    > a213|text1&14.44000|endtext
    >
    > None of the "Save As" choices allow this. All end up with at least
    > tab's separating the original cell contents. Also tried changing the
    > delimiter in the control panel, but NULL is not an allowed choice. And
    > opening the csv in any text editor still has tabs as separators
    > anyway.
    >
    > Thanks
    > Scott
    >
    >


  4. #4
    Registered User
    Join Date
    08-08-2006
    Posts
    3
    LenB

    Thanks for your help. Yes, please I do need help in creating this macro if you can give a quick how-to. Also, if there is no "end text", but there is a fixed amount of column's, can that be used to in place of the end text?

    thanks again,

    Scott
    Last edited by MojoNixon; 08-09-2006 at 10:09 AM.

  5. #5
    LenB
    Guest

    Re: How to Export as text file with NO delimiters or modifications?


    Okay, here's a new version for a fixed number of columns.

    The line: For intColumn = 1 To 7 is the loop for getting the value from
    each column. Change the 7 to the highest column you are using. If you
    didn't start in column A or Row 1, change the 1 to the start column, and
    change Range("A1").Activate to activate the correct starting cell.
    To create the macro, I suggest starting with a copy of your xls file.

    In Tools - Options, on the Security Tab (in Excel 2002, maybe different
    in other versions) click macro security and set it to medium. If it is
    high, you can't run macros. Medium will ask every time you open a file
    containing macros. If low, it never asks. Maybe risky for some. I
    like medium.

    Press Alt+F11 to get into the Visual Basic Editor (VBE).
    There should be a project tree visible, probably on the left. If not
    visible, press ctrl+R, or View -> Project Explorer.

    Right click on the line "VBAProject (your file name)", click insert,
    click module. That should create a module1 at the bottom of the tree
    and open a code window. This is where you paste the macro. Everything
    including the End Sub line.

    This is a good time to save! Saving in the VBE saves the xls file, just
    like in the Excel window.

    Make sure the output file name and path are ok. I used
    c:\temp\output100.txt. It can also get the file name from a cell if you
    like. If the file name is in K1, Use something like strFileName =
    Range("K1").value, or if you named a cell, use the cell name in place of K1.
    To run it right thru, press F5 while in the VBE. You can also step thru
    the code and watch it work using F8 instead of F5. I usually have the
    VBE window unmaximized and off to the right with the excel sheet visible
    to the left. Be careful about moving the active cell in Excel while
    running a macro. Until you are familiar with the macro, it is best not
    to click in the Excel window. Back in the VBE, every press of F8 runs
    one line. The yellow highlighted line is the next line to be executed.
    You can hover the mouse cursor on a variable name (strTextOut is a
    good one here) and a tooltip will show the value of the variable. If
    you step thru the inner For-Next loop while hovering on strTextOut, you
    will see how strTextOut is created one cell at a time.
    Press F5 anytime while stepping thru to run to the end. Also right
    click on a line gives you some options. A useful one is "Run To
    Cursor". That will stop at the line where the cursor is. "Set Next
    Statement" makes that line the next one to be run. Pressing F9 makes a
    breakpoint on the line with the cursor. The macro will always stop on
    that line waiting for an F5 or F8.
    Hope this is clear enough and that I didn't miss anything. If you have
    to run it often, you can create a toolbar button, or a Ctrl-Shift-letter
    combination, or just press Alt + F8 and run the macro from there. No
    need to have the VBE open.

    Len


    Sub MakeTextFile()
    Dim strTemp As String
    Dim strTextOut As String
    Dim strFileName As String
    Dim intOutFile As Integer
    Dim intColumn As Integer

    intOutFile = FreeFile 'get an available file number
    strFileName = "c:\temp\output100.txt" 'or whereever/whatever
    Open strFileName For Output As intOutFile
    Range("A1").Activate 'start at the top
    strTemp = ActiveCell.Value
    Do While Len(strTemp) > 0 'goes until a blank cell in column A
    strTextOut = "" 'initialize
    'loop thru the columns. Change 7 to the
    'highest column number used
    For intColumn = 1 To 7
    strTextOut = strTextOut & Cells(ActiveCell.Row, intColumn).Value
    Next
    Print #intOutFile, strTextOut 'send it to the output file
    ActiveCell.Offset(1, 0).Activate 'go down a row
    strTemp = ActiveCell.Value
    Loop
    Close intOutFile
    End Sub



    MojoNixon wrote:
    > LenB
    >
    > Thanks for your help. Yes, please I do need help in creating this macro
    > if you can give a quick hot-to. Also, if there is no "end text", but
    > there is a fixed amount of column's, can that be used to in place of
    > the end text?
    >
    > thanks again,
    >
    > Scott
    >
    >


  6. #6
    Registered User
    Join Date
    08-08-2006
    Posts
    3
    LenB

    Thanks! This was perfect. Just changed the column count and it gave me exactly what I needed.

    Rgds

  7. #7
    Registered User
    Join Date
    07-31-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: How to Export as text file with NO delimiters or modifications?

    Hi,

    code works fine for my requirement as well,but my excel data has date and time

    the format of it changes in text file

    in a colum i have date as 17 feb 2014 it is changed to 17-02-2014

    and in another column i have time in hrs it is converted to secs.

    how can i get it in the same format

  8. #8
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: How to Export as text file with NO delimiters or modifications?

    This concatenates all column amounts until it runs into a blank column... then writes it to a .txt file.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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