txt file to excel ?

Discussion in 'Mac Basics and Help' started by quigleybc, Aug 31, 2006.

  1. quigleybc macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #1
    Hi,

    I have a big text file with a bunch of info on it. How would I go about arranging it into columns so I could import it into Excel, and have it all get lined up in columns in excel?

    Is there a simple, user friendly way to do this?

    Thanks in advance.
    :)
     
  2. Blue Velvet Moderator emeritus

    Joined:
    Jul 4, 2004
    #2
    What you need to do is create what's called a 'tab-delimited' or 'comma-delimited' text file that can then be easily imported into Excel.

    If there is a certain character separating your values, you could do a find and replace substituting tabs for those characters.
     
  3. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #3
    Thanks for the reply BV :)


    Hmmmm, if you have a second, would you mind explaining that a little more?

    In the text file? or in excel?

    I'm assuming you mean excel.

    Thanks again, I'm obviously a beginner at this. :eek:
     
  4. bowens macrumors 6502a

    bowens

    Joined:
    Jun 19, 2006
    Location:
    Trenton, FL
    #4
    No, he means in the text file. You could use commas or tabs to separate where you want your column breaks to be. Then save the file as a .csv or .tsv. Then you can open it in Excel.
     
  5. Blue Velvet Moderator emeritus

    Joined:
    Jul 4, 2004
    #5
    http://desktoppub.about.com/od/textacquisition/a/tabdelimited.htm

    Working on a copy of the file, you can do a 'find and replace' from within TextEdit if the fields are separated by something other than a tab character.

    Can you post a small example of the data so we can see what you're dealing with?

    Also see:
    http://www.dimema.com/help4/acq-station/entering5.html#creating
     
  6. bowens macrumors 6502a

    bowens

    Joined:
    Jun 19, 2006
    Location:
    Trenton, FL
    #6
    Here is an example of each. Both were created with notepad and open fine in Excel. Try to open them with an editor to see what I mean, then open them in Excel.

    Just remove the .txt from the end of both files. It wouldn't let me upload them otherwise.
     

    Attached Files:

  7. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #7
    You guys are great!

    I love this site!

    I'll try and post an image of my data too

    Thanks again!

    :)


    oh, I should add that this is all being done on Windows :(

    so, it's in Notepad....does that make a difference?
     
  8. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #8
    I'm going to post a better pic below.
     
  9. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #9
    So I need the parts that say

    Description:


    Season:

    Class:

    Color:

    Sku:

    ect.... to be in their own columns

    Could I use that : to be replaced by TAB ?

    would that be the solution?

    no, I don't think that would work, because I need the info after the : to be in each column

    so it would be like:

    Description (space) Season (space) Vendor (space) Class (space) Color
    Mithril Stormshell (space) F6 (space) OR (space) 10.11 (space) Blue

    And so on....



    Thanks :)
     
  10. Phatpat macrumors 6502a

    Phatpat

    Joined:
    Jun 15, 2003
    Location:
    Washington DC
    #10
    In Excel for Windows (not at my mac write now) it lets you choose a custom delimiter, so if your values are separated by |, or spaces, or #, it will work just find without find and replacing on the text file.

    From excel, you open the text file, and it will come up with a little import wizard, and this is where you will enter your custom delimiter.

    I imagine it should work similarly on the Mac version, anyone?
     
  11. quigleybc thread starter macrumors 68030

    quigleybc

    Joined:
    Jun 17, 2005
    Location:
    Beautiful Vancouver British Columbia, Canada
    #11


    Ok, cool, I got to that wizard, and I'm just trying to figure out how I should customize the import....

    hmmm, I feel like I'm getting closer.

    :)



    [​IMG]


    I can't seem to figure out a good way to put values in between these fields...I could manually go through it, and add a comma, or a tab, but there are hundreds of fields like this one....
     

Share This Page