Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
When you do File|Open, you should be able to specify Delimited (by a comma).
Excel will respect those strings within double quotes. naulerich wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"naulerich" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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. "Dave Peterson" wrote in message ... 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
It appears you've changed commas to tilde signs. This that make the import
process work correctly? "naulerich" wrote in message ... 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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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. naulerich wrote: 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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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... "Dave Peterson" wrote: 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. naulerich wrote: 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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Please describe the exact things you're doing after you open the file and
the import wizard appears. "naulerich" wrote in message ... 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... "Dave Peterson" wrote: 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. naulerich wrote: 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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Choose Delimitted----Next---Choose Other and insert a ~----Next----Finish
Anyway I could send you a sample of the output to review? "Doug Kanter" wrote: Please describe the exact things you're doing after you open the file and the import wizard appears. "naulerich" wrote in message ... 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... "Dave Peterson" wrote: 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. naulerich wrote: 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" wrote: 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. naulerich wrote: 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" wrote: 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 wrote: 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" wrote in message ... When you do File|Open, you should be able to specify Delimited (by a comma). Excel will respect those strings within double quotes. naulerich wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote: 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote: 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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"naulerich" wrote in message
... 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. -- David Biddulph |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"David Biddulph" wrote in message ... "naulerich" wrote in message ... 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. -- David Biddulph 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. |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
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 "naulerich" wrote: 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! |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"Doug Kanter" wrote in message
... "David Biddulph" wrote in message ... "naulerich" wrote in message ... 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. 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. No. That's what the text qualifier sorts out for you. Try it. -- David Biddulph |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"Doug Kanter" wrote in message
... "Dave Peterson" wrote in message ... 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? 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. Interesting, as you say. I found that it kept the 1234,5678,9 together if the preceding double quote mark is immediately after the comma, but not if there is a space between. The first line here keeps the numeric string together, but the second line splits it: "123","test, text","this is a test","abc","1234,5678,9" "123","test, text","this is a test","abc", "1234,5678,9" -- David Biddulph |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
If I included the space character (as well as the comma), then both lines
"123","test, text","this is a test","abc","1234,5678,9" "123","test, text","this is a test","abc", "1234,5678,9" were imported the same--the last field was 1234,5678,9 (all one cell). David Biddulph wrote: "Doug Kanter" wrote in message ... "Dave Peterson" wrote in message ... 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? 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. Interesting, as you say. I found that it kept the 1234,5678,9 together if the preceding double quote mark is immediately after the comma, but not if there is a space between. The first line here keeps the numeric string together, but the second line splits it: "123","test, text","this is a test","abc","1234,5678,9" "123","test, text","this is a test","abc", "1234,5678,9" -- David Biddulph -- Dave Peterson |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"David Biddulph" wrote in message ... "Doug Kanter" wrote in message ... "David Biddulph" wrote in message ... "naulerich" wrote in message ... 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. 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. No. That's what the text qualifier sorts out for you. Try it. -- David Biddulph In theory, yes, but the OP e-mailed me some of her original text (with commas, not tildes), and doing what you suggest, the import does NOT work correctly. Excel sees the commas WITHIN the text strings as delimiters, same as the ones BETWEEN the text strings. Here's the text she sent: "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", "", "" "3943", "BASE-Account Select Window Default Method", "Intergy today allows users to designate their user preference when selecting patients. Users would like to have this same feature when selecting accounts. By providing the user to designate their "default" account select method in user preference, it will allow them to save key strokes in the account select windows.", "Provide the same method in Patient Select Window -- ability for the user to default their search. In the User Preference for Patient Select we called the Preference -- "Select Patient Sort/Filter Default". Need to do the same for Account Select.", "<<internal source group", "Nice to Have", "", "" |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Nichole, using the sample text you e-mailed me, I got the same results you
did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
I think that the OP will have to clean up the data.
There shouldn't be spaces between fields and any field that contains double quotes will have to have those double quotes doubled up. In that last line, their "default" account would have to become their ""default"" account I don't see a way around it except to clean up that data. Doug Kanter wrote: "David Biddulph" wrote in message ... "Doug Kanter" wrote in message ... "David Biddulph" wrote in message ... "naulerich" wrote in message ... 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. 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. No. That's what the text qualifier sorts out for you. Try it. -- David Biddulph In theory, yes, but the OP e-mailed me some of her original text (with commas, not tildes), and doing what you suggest, the import does NOT work correctly. Excel sees the commas WITHIN the text strings as delimiters, same as the ones BETWEEN the text strings. Here's the text she sent: "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", "", "" "3943", "BASE-Account Select Window Default Method", "Intergy today allows users to designate their user preference when selecting patients. Users would like to have this same feature when selecting accounts. By providing the user to designate their "default" account select method in user preference, it will allow them to save key strokes in the account select windows.", "Provide the same method in Patient Select Window -- ability for the user to default their search. In the User Preference for Patient Select we called the Preference -- "Select Patient Sort/Filter Default". Need to do the same for Account Select.", "<<internal source group", "Nice to Have", "", "" -- Dave Peterson |
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
"Doug Kanter" wrote in message
... "David Biddulph" wrote in message ... "Doug Kanter" wrote in message ... "David Biddulph" wrote in message ... "naulerich" wrote in message ... 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. 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. No. That's what the text qualifier sorts out for you. Try it. -- David Biddulph In theory, yes, but the OP e-mailed me some of her original text (with commas, not tildes), and doing what you suggest, the import does NOT work correctly. Excel sees the commas WITHIN the text strings as delimiters, same as the ones BETWEEN the text strings. Here's the text she sent: "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", "", "" "3943", "BASE-Account Select Window Default Method", "Intergy today allows users to designate their user preference when selecting patients. Users would like to have this same feature when selecting accounts. By providing the user to designate their "default" account select method in user preference, it will allow them to save key strokes in the account select windows.", "Provide the same method in Patient Select Window -- ability for the user to default their search. In the User Preference for Patient Select we called the Preference -- "Select Patient Sort/Filter Default". Need to do the same for Account Select.", "<<internal source group", "Nice to Have", "", "" If you change each occurrence of , " [comma, space, double quote] to ," [comma, double quote], then it will allow the import wizard to pick up the double quote text qualifier art the start of each field, and hence it won't split the field at a comma within the double quotes. The only remaining problem is the additional double quotes around "default" in the final data set. As Dave Peterson has said, in that case you'll need to double up the double quotes. It then works OK. -- |
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
The text is coming out of a Borland database (Caliber), it will export to
Word no problem however it is requested the output be in .xls format which is not supported through the tools we have purchased through Borland. I am not sure what control the BA has, other than setting delimiters when exporting...What can I ask that the BA do to get a clean export? "Doug Kanter" wrote: Nichole, using the sample text you e-mailed me, I got the same results you did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#29
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Most database products allow the user to design outbound reports in any way
that's necessary. Although programmers are as likely to do stupid things as the rest of the population, I'd be very surprised if Borland built that product without the same report design features as their older products (Paradox, Delphi). This link takes you to the Borland support forums - I'd ask there about whether you can design an export report that's more manageable than the file you're struggling with now: http://support.borland.com/category.jspa?categoryID=3 This might take the discussion into another direction, but what's the final purpose of bringing all that text into Excel? "naulerich" wrote in message ... The text is coming out of a Borland database (Caliber), it will export to Word no problem however it is requested the output be in .xls format which is not supported through the tools we have purchased through Borland. I am not sure what control the BA has, other than setting delimiters when exporting...What can I ask that the BA do to get a clean export? "Doug Kanter" wrote: Nichole, using the sample text you e-mailed me, I got the same results you did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#30
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
I know that the capability to export to Excel is there however, I have been
told that is outside of the reporting functionality we purchased. Bottom line, we are trying to find a work around, currently the data is being maniputlated close to 20 hrs per output (not sure what takes so long...) to get into the pretty excel colums. I believe that the final reason for the output in excel is for R&D tracking of projects/deliverables. My comapny LOVES excel, spreadsheet heaven here... Can't very well ask Borland what to do... "Doug Kanter" wrote: Most database products allow the user to design outbound reports in any way that's necessary. Although programmers are as likely to do stupid things as the rest of the population, I'd be very surprised if Borland built that product without the same report design features as their older products (Paradox, Delphi). This link takes you to the Borland support forums - I'd ask there about whether you can design an export report that's more manageable than the file you're struggling with now: http://support.borland.com/category.jspa?categoryID=3 This might take the discussion into another direction, but what's the final purpose of bringing all that text into Excel? "naulerich" wrote in message ... The text is coming out of a Borland database (Caliber), it will export to Word no problem however it is requested the output be in .xls format which is not supported through the tools we have purchased through Borland. I am not sure what control the BA has, other than setting delimiters when exporting...What can I ask that the BA do to get a clean export? "Doug Kanter" wrote: Nichole, using the sample text you e-mailed me, I got the same results you did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#31
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Is there a name for the reporting capability that you DO have? A product
description? I know some ancient Borland product veterans who might have some tricks up their sleeves, but I'd need to tell them what you've got. "naulerich" wrote in message ... I know that the capability to export to Excel is there however, I have been told that is outside of the reporting functionality we purchased. Bottom line, we are trying to find a work around, currently the data is being maniputlated close to 20 hrs per output (not sure what takes so long...) to get into the pretty excel colums. I believe that the final reason for the output in excel is for R&D tracking of projects/deliverables. My comapny LOVES excel, spreadsheet heaven here... Can't very well ask Borland what to do... "Doug Kanter" wrote: Most database products allow the user to design outbound reports in any way that's necessary. Although programmers are as likely to do stupid things as the rest of the population, I'd be very surprised if Borland built that product without the same report design features as their older products (Paradox, Delphi). This link takes you to the Borland support forums - I'd ask there about whether you can design an export report that's more manageable than the file you're struggling with now: http://support.borland.com/category.jspa?categoryID=3 This might take the discussion into another direction, but what's the final purpose of bringing all that text into Excel? "naulerich" wrote in message ... The text is coming out of a Borland database (Caliber), it will export to Word no problem however it is requested the output be in .xls format which is not supported through the tools we have purchased through Borland. I am not sure what control the BA has, other than setting delimiters when exporting...What can I ask that the BA do to get a clean export? "Doug Kanter" wrote: Nichole, using the sample text you e-mailed me, I got the same results you did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#32
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
Emailed you the response from the BA.
"Doug Kanter" wrote: Is there a name for the reporting capability that you DO have? A product description? I know some ancient Borland product veterans who might have some tricks up their sleeves, but I'd need to tell them what you've got. "naulerich" wrote in message ... I know that the capability to export to Excel is there however, I have been told that is outside of the reporting functionality we purchased. Bottom line, we are trying to find a work around, currently the data is being maniputlated close to 20 hrs per output (not sure what takes so long...) to get into the pretty excel colums. I believe that the final reason for the output in excel is for R&D tracking of projects/deliverables. My comapny LOVES excel, spreadsheet heaven here... Can't very well ask Borland what to do... "Doug Kanter" wrote: Most database products allow the user to design outbound reports in any way that's necessary. Although programmers are as likely to do stupid things as the rest of the population, I'd be very surprised if Borland built that product without the same report design features as their older products (Paradox, Delphi). This link takes you to the Borland support forums - I'd ask there about whether you can design an export report that's more manageable than the file you're struggling with now: http://support.borland.com/category.jspa?categoryID=3 This might take the discussion into another direction, but what's the final purpose of bringing all that text into Excel? "naulerich" wrote in message ... The text is coming out of a Borland database (Caliber), it will export to Word no problem however it is requested the output be in .xls format which is not supported through the tools we have purchased through Borland. I am not sure what control the BA has, other than setting delimiters when exporting...What can I ask that the BA do to get a clean export? "Doug Kanter" wrote: Nichole, using the sample text you e-mailed me, I got the same results you did. This leads to another question - same one I asked yesterday: Do you have any control over the program which PRODUCES the text file? What is that program? |
#33
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import data into seperate columns
On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote: 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! Having read a bunch of this thread, it seems to me that you may be able to parse out your data using Regular Expressions in VBA. To enter the code, <alt<F11 opens the VBA Editor. Ensure your project is highlighted in the project explorer window, then Insert Module and paste the code below into the window that opens. In the VB Editor, you must also select Tools/References and select Microsoft VBScript Regular Expressions 5.5 from the list. To use this, with your data in A1, enter the following formula into B1 and copy/drag across as far as required. =remid($A1,"""[^""]+""",COLUMNS($B:B)) The Regular Expression portion: """[^""]+""" says generate a match that begins with a double quote; is followed by any number of characters that do not include a double quote; and terminated by a double quote. The Columns function is merely a method of generating an increasing number as you copy/drag the formula across several columns; and it will cause the expression to return the 1st, 2nd, etc instance of the matched pattern. The resultant string will have double quotes around it. If that is not satisfactory, you can embed the above in a SUBSTITUTE function to get rid of them. =SUBSTITUTE(remid($A1,"""[^""]+""",COLUMNS($B:B)),"""","") ============================================ Option Explicit Function REMid(str As String, Pattern As String, _ Optional Index As Variant = 1, _ Optional CaseSensitive As Boolean = True) _ As Variant 'Variant as value may be string or array Dim objRegExp As RegExp Dim objMatch As Match Dim colMatches As MatchCollection Dim i As Long 'counter Dim t() As String 'container for array results ' Create a regular expression object. Set objRegExp = New RegExp 'Set the pattern by using the Pattern property. objRegExp.Pattern = Pattern ' Set Case Insensitivity. objRegExp.IgnoreCase = Not CaseSensitive 'Set global applicability. objRegExp.Global = True 'Test whether the String can be compared. If (objRegExp.Test(str) = True) Then 'Get the matches. Set colMatches = objRegExp.Execute(str) ' Execute search. On Error Resume Next 'return null string if a colmatch index is non-existent If IsArray(Index) Then ReDim t(1 To UBound(Index)) For i = 1 To UBound(Index) t(i) = colMatches(Index(i) - 1) Next i REMid = t() Else REMid = CStr(colMatches(Index - 1)) If IsEmpty(REMid) Then REMid = "" End If On Error GoTo 0 'reset error handler Else REMid = "" End If End Function ============================= Let me know if this works for you. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data and keep duplicate rows of data | Excel Discussion (Misc queries) | |||
How do I seperate data in one column into multi columns | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
how do i import multiple data sets at one time? | Excel Discussion (Misc queries) | |||
Automatic Data Import | Excel Discussion (Misc queries) |