+ Reply to Thread
Results 1 to 8 of 8

CSV Data download Symbols

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    80

    CSV Data download Symbols

    Hi All,

    I am seeking an elegant and simple solution for an issue as my current resolve is long complex and not 100% accurate.

    I have a csv raw data download that contains various symbols that Excel doesn’t seem to recognize and converts them to strange combinations.

    I am current using a substitute function to change each symbol one by one but I know there must be some type of blanket change for this type of character set.

    Current code is below with only some of the characters but there could be potentially hundreds. I have also attached the raw data.

    Help please!

    Please Login or Register  to view this content.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,554

    Re: CSV Data download Symbols

    You don't need to loop:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: CSV Data download Symbols

    Thanks shg this definitely simplifies the code but I was actually looking for a way to change the overall character set so that I don't have to convert the symbols.

    Is there a way that this can be done? I think it has something to do with UTF-8 but I am not sure what this is exactly.

    Many thanks.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,554

    Re: CSV Data download Symbols

    That could well be true (that the spurious symbols are multi-character encodings from UTF-8), but I think it needs to be addressed at the source, when the CSV is exported from wherever it comes from.
    Last edited by shg; 03-07-2018 at 09:19 PM.

  5. #5
    Registered User
    Join Date
    12-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: CSV Data download Symbols

    Thanks again, however this is not an option for me as I am not running the download and have no control over how it is set up.

    Does anyone know a way that this can be achieved in Excel?

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    2,516

    Re: CSV Data download Symbols

    Hi !

    Yes ! one of the classic way is first to rename file to .txt and open it :
    the import assistant will prompt, fill the file origin as 65001 Unicode (UTF-8) …

    I have others way but can't work with this badly formated file !

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,533

    Re: CSV Data download Symbols

    Hello Ravenous,

    While working on this problem, I found that opening the csv file in Notepad displays the contents correctly. Your original file is not UTF-8, it is ANSI with embedded Unicode characters. In the word "Meredith's", the single quote is Unicode 2019. This can be entered manually in Excel by pressing ALT and holding it then pressing the digits 0146 on the numeric keypad. Numlock must be on for this to work correctly. Some word processors, like Word, will make this change automatically when you type a single quote character. Excel will not.

    Now that the file can be opened in Notepad correctly means a macro could be written to open the csv in Notepad and then copy the text back to the worksheet. The copied text could be parsed manually using Text to Columns, or by the macro itself.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  8. #8
    Registered User
    Join Date
    12-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: CSV Data download Symbols

    Figured it out...

    It was as simple as importing the file as UTF-8 instead of copy and paste!

    Appreciate everyones assistance.
    Last edited by Ravenous; 03-08-2018 at 11:39 PM.

+ 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