txt file to excel ?

[quigleybc]

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]

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]

Thanks for the reply BV

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?

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.

 

[bowens]

In the text file? or in excel?

I'm assuming you mean excel.

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

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]

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]

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.

 

[quigleybc]

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?

 

[quigleybc]

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

I'm going to post a better pic below.

 

[quigleybc]

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]

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]

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