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