Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's one for the master loungers. I have a file of several hundred records which are text delimited with "double quotes" and the field delimiters are commas. A sample such as
    "THE JUNKYARD, INC.","121 N TRASH ST","REFUSEHILLS KS 99999-0000"
    Using the standard Text To Columns button in the menu guides you through the paces of selecting the field (double quotes) and column (comma) characters. All is well except when there is an embedded field character within the text delimiters such as in my example where the comma follows the word JUNKYARD. When this happens the "INC" appears in its own column instead of staying with the company name. I've searched TechNet without success and the options in Excel with no luck. Is there some option somewhere that turns on or off ignoring a column delimiter when embedded? I'm glad you guys are here to make up for either my ignorance or M$ 's forgetfulness.

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by jamm3228 View Post
    Here's one for the master loungers. I have a file of several hundred records which are text delimited with "double quotes" and the field delimiters are commas. A sample such as
    "THE JUNKYARD, INC.","121 N TRASH ST","REFUSEHILLS KS 99999-0000"
    Using the standard Text To Columns button in the menu guides you through the paces of selecting the field (double quotes) and column (comma) characters. All is well except when there is an embedded field character within the text delimiters such as in my example where the comma follows the word JUNKYARD. When this happens the "INC" appears in its own column instead of staying with the company name. I've searched TechNet without success and the options in Excel with no luck. Is there some option somewhere that turns on or off ignoring a column delimiter when embedded? I'm glad you guys are here to make up for either my ignorance or M$ 's forgetfulness.

    Thanks
    You could change "," for some other symbol and delimit the text on that instead.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I have thought of this but this would be very impractical since our mail list processing system outputs this format for files that can range into the 100's of thousands. I have tried this on several systems at work and even my at home one and it results in the same outcome. Surely this is not an individual anomaly and has occurred before. Any help from anyone will be appreciated.

  4. #4
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Victoria, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    ... text delimited with "double quotes" and the field delimiters are commas ...
    .

    There is only 1 type of delimiter in your example data, which is the comma. The double quotes are a text qualifier. Any commas inside the pair of double quotes aren't used to define a new field, so you would end up with:

    THE JUNKYARD, INC. in the first column,
    121 N TRASH ST in the second, and
    REFUSEHILLS KS 99999-0000 in the third

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Stephen,

    I am aware that your response is how it should work, but for me it is not so simple and the results are just as I explained, why is that so? And how can I fix it?

  6. #6
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Victoria, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure what version you have, but does it look like the attached?

    If the drop-down box on the right of the form has {none} or ' selected, the result is a you have experienced. If it is set to " then it keeps THE JUNKYARD, INC together.
    Attached Images Attached Images

  7. #7
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stephen,

    When I get to this point in the conversion the double quotes are selected but the "INC" appears in it's own column. I am stymied. Attached is my sample.[attachment=88811:EXCEL_PROBLEMS5-20-2010 10-19-15 PM.pdf]
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Here is the result I get using what Stephen said. It looks correct to me.

    Tim

    PS: Why do you guys attach .pdf and .bmp files rather than .xls file?
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You appear to have lost the opening double quote during the import...

    Thus the first group is not considered "qualified" by the text qualifier...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •