Import data into seperate columns

[Guest]

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

 

[Dave Peterson]

When you do File|Open, you should be able to specify Delimited (by a comma).
Excel will respect those strings within double quotes.

 

[Doug Kanter]

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However
what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

Two thoughts:

1) Any chance the file also happens to have the data in nice, neat columns,
so it could be imported as fixed width?

2) Do you have any control over the program which created the text file? If
yes, perhaps you could design an export report which would delimit the
fields using a character other than commas, like the tilde ~ symbol.

 

[Doug Kanter]

In Excel 2000, it doesn't "respect" the commas. The OP has text strings with
commas within them. Excel interprets them as delimiters, regardless of the
quotes.

 

[Dave Peterson]

I don't have xl2k anymore, but that's not the way I remember it working.

I put:

"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"
"123","test, text","this is a test","abc"

in a text file and xl2003 imported it nicely (test, text was one field).

Can you try it one more time?

 

[Doug Kanter]

Now, that's interesting. The test strings I used were numerical, and looked
like the line below:
"1234,5678,9" Excel broke that into three columns. I wonder if it behaves
differently when the strings in question are numerical.

Time for the OP to return and tell us worked or didn't work for him.

 

[Guest]

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

 

[Dave Peterson]

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.

I am using 2003 it is not respecting the ""...I am going to have the souce
data delimiter changes to a ~ and see if that helps.

Thanks!

 

[Guest]

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.~ <<internal source group>>~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient check-in.
Not currently working in Patient Check-in.~ <<internal source group>>~ Nice
to Have~ ~

I think you should copy a few of the lines from the text file into your followup
post--if you don't get it working.

 

[Doug Kanter]

It appears you've changed commas to tilde signs. This that make the import
process work correctly?

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats
we
must prep for migrating sites to the latest 4E format. This requirement is
in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and
using
the latest claims submission formats we must prep for migrating sites to
the
latest 4E format. This requirement is in sync with the EDI requirement
with
the same name. This is a multi-step project.~ <<internal source group>>~
Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them
to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.~ <<internal source group>>~
Nice
to Have~ ~

 

[Dave Peterson]

I don't see the double quotes. I don't see the commas between fields.

But if each of those paragraphs is actually a line in a text file, I could
import the data using delimited by tilde (~) and got different fields.

Here is a sample of the data, thanks so much!

5493~ BASE-(Claims) Auto Format Update I~ Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is in
sync with the EDI requirement with the same name. This is a multi-step
project.~ Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.~ <<internal source group>>~ Have
to Have~ ~

5501~ BASE_Patient/Account Alert in Check-in~ Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.~ Need to activate Patient and Account Alert during patient check-in.
Not currently working in Patient Check-in.~ <<internal source group>>~ Nice
to Have~ ~

 

[Guest]

I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

 

[Doug Kanter]

Please describe the exact things you're doing after you open the file and
the import wizard appears.

I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

 

[Guest]

Choose Delimitted---->Next--->Choose Other and insert a ~---->Next---->Finish

Anyway I could send you a sample of the output to review?

 

[Ron Rosenfeld]

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

It seems to work fine here in XL2002 using the Data/Text to
Columns/Delimited/Comma AND ALSO indicate that the " is the text qualifier


--ron

 

[Ron Rosenfeld]

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

To expand on my previous, on the page where you set the Delimiter, there is
another box indicating Text Qualifier. If this is NOT set to the double quote
("), then it will misbehave similar to what you describe.


--ron

 

[David Biddulph]

I did remove the double quotes and added a ~ instead of the commas, I am
still not getting the output in the correct columns when using the import
with delimiter set to ~....I don't know what to do next...

You shouldn't have removed the double quotes, and you didn't need to change
the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier" box
of the text import wizard you need the double quote symbol.

 

[Doug Kanter]

Yes. The email address I use here is functional: (e-mail address removed).
I may not be able to look at it until much later tonight, or tomorrow
morning at the latest.

Choose Delimitted---->Next--->Choose Other and insert a
~---->Next---->Finish

Anyway I could send you a sample of the output to review?

 

[Doug Kanter]

You shouldn't have removed the double quotes, and you didn't need to
change the commas to tildes. Go back to your original file.

You should use your comma as the delimiter, but in the "text qualifier"
box of the text import wizard you need the double quote symbol.

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If this
is the case, Excel will split fields based not only on the commas which
exist as delimiters, but also when it sees those within the text.

 

[Guest]

you have a lot of Text in your text file. There does not seem to be any
numbers for calculations. Why don't you import to Word?.

Alternatively, you will have to use a macro to import the text

http://www.geocities.com/excelmarksway

exceluserforeman