txt file to excel ?

quigleybc

macrumors 68030
Original poster
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.
:)
 

Blue Velvet

Moderator emeritus
Jul 4, 2004
21,922
168
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.
 

quigleybc

macrumors 68030
Original poster
Thanks for the reply BV :)


Blue Velvet said:
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.
Hmmmm, if you have a second, would you mind explaining that a little more?

Blue Velvet said:
If there is a certain character separating your values, you could do a find and replace substituting tabs for those characters.
In the text file? or in excel?

I'm assuming you mean excel.

Thanks again, I'm obviously a beginner at this. :eek:
 

bowens

macrumors 6502a
Jun 19, 2006
659
34
Trenton, FL
quigleybc said:
In the text file? or in excel?

I'm assuming you mean excel.

Thanks again, I'm obviously a beginner at this. :eek:
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.
 

Blue Velvet

Moderator emeritus
Jul 4, 2004
21,922
168
A tab delimited file is a special kind of plain text file with a tab between each column in the text. When imported into your page layout software, the tabs allow the columns to line up neatly.
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
 

bowens

macrumors 6502a
Jun 19, 2006
659
34
Trenton, FL
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.
 

Attachments

quigleybc

macrumors 68030
Original poster
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 :)
 

Phatpat

macrumors 6502a
Jun 15, 2003
903
2
Cambridge, MA
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?
 

quigleybc

macrumors 68030
Original poster
Phatpat said:
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?


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.

:)






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....