+ Reply to Thread
Results 1 to 5 of 5

Pipe delimited vs comma delimited problem

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Pipe delimited vs comma delimited problem

    Hello,

    I'm dealing with CSVs that are pipe delimited. When I open the file in Excel, all the data fills in to column A, but if there is a comma anywhere in that row's worth of data, it bumps that data to column B, removing the comma. This creates a problem when I try to convert text to columns.

    Is there a setting in Excel or elsewhere such that when I open a pipe delimited CSV that contains commas, it doesn't treat the commas as delimiters?

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Pipe delimited vs comma delimited problem

    have you tried to un-select comma's when you do the conversion?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Pipe delimited vs comma delimited problem

    The problem I'm dealing with occurs before conversion. I open the file and the commas have already been treated as delimiters.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Pipe delimited vs comma delimited problem

    The extension CSV stands for "Comma Separated Values." I believe when the CSV extension was introduced, it was intended to specifically mean "an ascii text file that uses a comma as the delimiter." So when Excel opens a CSV (from the open file command), it is specifically looking for commas as the delimiter. Calling a pipe delimited text file a CSV is kind of a misnomer. A few suggestions:

    1) Are you allowed to replace the extension on the file? If you replace the extension to something else (maybe .txt), then Excel won't know "intuitively" what to use for the delimiter. You can open the text file, then use the Text to columns command to parse the file using whatever delimiter is present.
    2) I often use the "import external data" command for opening text files. When using the import external data command on any text file, Excel brings up the import text wizard, which allows you to specify delimiters, column data types, and so on.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Pipe delimited vs comma delimited problem

    Thanks MrShorty,

    Changed the extension to .txt and then imported the file, and it worked fine at that point.

    Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1