Import txt files into Excel

[Basta1980]

Import DataHi

When I for instance import txt files containing 4 criteria (Name, Business
Unit, Allocation Code & Dept) into Excel using Data -> Import external Data
-> Import Data, the text import wizard appears. I choose delimited and when I
go to step 2 of 3 I use space delimiters. What happens next is that the data
does not always get into the right column.

Column A Column B Column C Column D Column E
John New York 13 Sales
Mike LA 25 Accounting
Jesse New York 14 Sales-Exec
Donna New York 98 Finance

How Do i get Excel to import data in the correct columns (i.e. Name by name,
BU by BU, AC by AC and Dept. by Dept.)?!

Many tthanks in advance!!!

 

[Kevin B]

Using the import text wizard select fixed width instead of space delimited,
then adjust the column breaks in the next screen by either dragging the
column break indicator left or right, or clicking on the ruler bar at a
specified location to insert a column break. To remove unnecessary column
break lines just double click on them.

 

[Basta1980]

Kevin,

Thank you for the tip, but instead of nicely putting the data into columns
(where they should be) i find that certain strings of text are seperated (Ne
wYork for instance). So because the widt of the data is not constant it seems
i cannot use the 'fixed width' option.

 

[David Biddulph]

The best bet is to get whatever application is producing the text file to
include suitable delimiters, perhaps tab or comma. Space is obviously not
suitable when you have spaces within fields.

 

[Basta1980]

David,

What if I import the data into one single cel (whole line into a1, a2 etc.)
Is there a way I can use the len, search or replace function (or a combo of
these) to replace al spaces with a ^ (for instance) and not the "correct"
spaces (like New York)?! I can use the new delimiter (^) within the text
import wizard.

 

[Pete_UK]

Are your names always single words, or might you have "John Doe",
"Jesse James" ?

If you do import to one column, you could highlight the data and use
Edit | Replace (CTRL-H) to:

Find What: New York
Replace With: New_York
Replace All

and repeat this for other business units which have spaces in the
names (eg San Francisco to San_Francisco) as I presume you only have a
few of these, but there is no easy way of doing this with the name if
you have spaces in that.

Then you could use Data | Text-to-columns to parse the data, and
subsequently change all those _ back to spaces.

Hope this helps.

Pete

 

[David Biddulph]

Yes, providing that you have a way of defining which are "correct spaces".

 

[Kevin B]

You can import the data into a single cell and then use DATA/TEXT TO COLUMNS
off the Excel menu. However, if you select a space as your delimiter cities
with 2 words will be in 2 cells, names w/first name, middle initial, last
name will be in 3 cells. You'll still have some work to do to hammer the
data into shape.