when importing .txt files can I separate the data horizontally

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chrisinct - 06 Jul 2006 19:45 GMT

I have to import a .txt file from a database query into an excel spreadsheet
and I can't get the data to format properly. It comes in this format from
the query...

1111 PHYSICIAN OFFICE RP 111 WEST ST 880
555-1212

SMALLVILLE, CT 06001 880
555-1212

Can I import that data into excel in a cell by cell format and then
manipulate it? I appreciate the help.

Reply to this Message

Gord Dibben - 06 Jul 2006 19:55 GMT

Your example data is all broken up so's hard to get a read on how it looks.

Does it come in as one column and one row or how?

Describe or re-format your post to readable.

Gord Dibben MS Excel MVP

>I have to import a .txt file from a database query into an excel spreadsheet
>and I can't get the data to format properly. It comes in this format from
[quoted text clipped - 9 lines]
>Can I import that data into excel in a cell by cell format and then
>manipulate it? I appreciate the help.

Reply to this Message

Chrisinct - 06 Jul 2006 20:13 GMT

sorry it looked better when I first posted it.
Seperated out - First line contains this info (quotes added for ease of
recognition)

"1009 PHYSICIAN OFFICE RP 240 EAST ST 860 747-4541"

second Line contains this Info

"PLAINVILLE, CT 06062 860 793-1281"

> Your example data is all broken up so's hard to get a read on how it looks.
>
[quoted text clipped - 17 lines]
> >Can I import that data into excel in a cell by cell format and then
> >manipulate it? I appreciate the help.

Reply to this Message

Gord Dibben - 06 Jul 2006 20:19 GMT

So row one contains the data in 4 cells or one cell and in which column(s)?

Row 2 contains the data in 2 cells or one cell and in which column(s)?

What do you want to do with the data from the two rows?

Place all one one row in separate cells or all in one cell?

"Manipulate" is quite a vague description.

Gord

>sorry it looked better when I first posted it.
>Seperated out - First line contains this info (quotes added for ease of
[quoted text clipped - 27 lines]
>> >Can I import that data into excel in a cell by cell format and then
>> >manipulate it? I appreciate the help.

Gord Dibben MS Excel MVP

Reply to this Message

Chrisinct - 06 Jul 2006 20:36 GMT

I want to be able to choose what data goes in a specific cell. I can't
manage this by using a delimiter because a comma and tab does too little and
space is too much.

I want to import it in this fashion- [brackets show what I need per cell]

[1009] [PHYSICIAN OFFICE] [RP] [230 WEST ST]
[860 757-4111]

[PLAINVILLE], [CT] [06000]
[860 711-1222]

If I can just get that specific info into individual cells then I can move
it to where I need to to go specifically this format...

EntryCode Name Mnemonic Address Address City State Zip Phone Fax Country County Inactive? Create?

wrapped by formatting but this is the top row of the spreadsheet

Thanks for your patience.

> So row one contains the data in 4 cells or one cell and in which column(s)?
>
[quoted text clipped - 41 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

SteveW - 07 Jul 2006 19:12 GMT

> I want to be able to choose what data goes in a specific cell. I can't
> manage this by using a delimiter because a comma and tab does too little
[quoted text clipped - 69 lines]
>>
>> Gord Dibben MS Excel MVP

it gets all messy when you have 2 lines which will have different
conversion rules.
Initially add a couple of columns A 1,1,2,2,3,3 ... and B with 1,2,1,2,1,2
...

Now sort all the data on B - deal with the two different formats
Then you can sort back via sort on A,B to get the original data list.

as for the conversion... first change multiple spaces to a unique
character say "!"
Then you can seperate fields on that character.
Space won't work as you have that in the data.

Signature

Steve (3)

Reply to this Message