Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Converting Excel data to .txt

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to convert some data I have in .xls format into .txt format. I also have to format it a certain way for a mail program that I have.

    Here is how the data needs to be formatted:

    [firstname],[lastname],[email]
    "Alan","Smith","alan@aol.com"
    "Tom","Jones","tomj@aol.com"
    etc.

    Does anyone know how to export this data and format it this way automatically?

    Thanks,

    Alan

    [ This Message was edited by: alanf on 2002-03-27 14:49 ]

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does the file have to be .txt? Could it have another extension like ".csv"? If it can then stick your data in columns A B and C then go to File|Save As and change the file type to Comma Separated Values (*.csv).



  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yep. Unfortunately it has to be .txt format. You would think there would be a way to select export and then tell Excell what kind of seperators you want. I can't figure it out though.

    Alan

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 14:25, alanf wrote:
    I need to convert some data I have in .xls format into .txt format. I also have to format it a certain way for a new mail program that I have.

    Here is how the data needs to be formatted:

    [firstname],[lastname],[email]
    "Alan","Smith","alan@aol.com"
    "Tom","Jones","tomj@aol.com"
    etc.

    Does anyone know how to export this data and format it this way automatically?

    Thanks,

    Alan
    Hi alanf:
    Continuing with Mark's suggestion with saving data in your Excel worksheet as a CSV (comma delimited) format ...
    after you have saved this file with the CSV extension, you can rename it with TXT extension.
    I took your sample data, saved the data in CSV format and opened it in Notepad ... here is what I got:

    first,last,email
    Alan,Smith,alan@aol.com
    Tom,Jones,tomj@aol.com

    If you need to enclose the field headers in [] and you need to have each piece of data in double quotes, you may have some additional formatting to do.

    HTH

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks HTH.

    That is exactly what I need to do. Any idea of how to do the additional formatting? I don't necessarily have to add the [ ] around the headers. I can do that manually. It is going to be a pain to manually add " " around each item in the list...got any ideas?

    Maybe i could user some formula or something to add the quotes around each item then convert it to .csv and then rename it to .txt? Anyone know a way to add " " around the text in each cell in an Excell document?

    Alan

    [ This Message was edited by: alanf on 2002-03-27 14:55 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might want to try the following:

    Open "C:FileName.txt" For Output As #1
    For i = 1 To 10
    Print #1, """" & Range("A1").Offset(i - 1, 0).Value & """" & "," & _
    """" & Range("A1").Offset(i - 1, 1).Value & """" & "," & _
    """" & Range("A1").Offset(i - 1, 2).Value & """"
    Next i
    Close #1

    The above code is assuming your column A is the first name, column B is surname, and column C is the E-mail Address.

    HTH

    PS. Yogi is too quick for me. But saving as .csv, and rename as .txt, the data in the file don't have the quotation marks.

    [ This Message was edited by: BabyTiger on 2002-03-27 14:57 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do I do with the code? Sorry I am a novice...don't know what to do with it.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    From the workbook/sheet that's got the list of name and e-mail address, select from menu bar, tools-macro-visual basic editor, and then from the microsoft visual basic screen, select from menu bar, insert module.

    And then copy those codes to that module with an addition line before the code, "Sub Test()", and after code, enter "End Sub" in the line after.

    Once you have done all that, you have setup your macro, and is ready to go.

    Now make sure your data is in the right place. ie. First names are in the column A, Surnames are in column B, and e-mail addresses are in column C.

    The codes in the previous post will not insert the headers, if your want to add that, then use the codes below:

    Open "C:FileName.txt" For Output As #1
    Print #1, "[firstname],[lastname],[email]"
    For i = 1 To 10
    Print #1, """" & Range("A1").Offset(i - 1, 0).Value & """" & "," & _
    """" & Range("A1").Offset(i - 1, 1).Value & """" & "," & _
    """" & Range("A1").Offset(i - 1, 2).Value & """"
    Next i
    Close #1

    The difference is that extra line of code, that will add the headers to the txt file.

    HTH

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi BabyTiger:
    I tried the code in a sub -- Works ... Beautiful!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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