+ Reply to Thread
Results 1 to 12 of 12

Change String Encoding Using VBA

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Change String Encoding Using VBA

    I have downloaded a .CSV file that has characters like é and è instead of and . I need some VBA code to fix the encoding for the entire sheet. Any suggestions? Assume that I cannot fix the encoding upstream (so that the CSV is okay).

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Change String Encoding Using VBA

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change String Encoding Using VBA

    Since CSV is a text file you might be able to repair the text without actually OPENING the file in Excel as an Excel-formatted CSV. You might be able to edit it using text edit function in VBA.

    Can you upload a small sample? Two CSV files with just a few lines that show the original problem text, then the corrected text?



    xlAdept's approach for making tweaks after opening the CSV is great, you should be able to build on that, too.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change String Encoding Using VBA

    Hi xladept, this would require me creating a mapping for all of these encoding issues. I think there should be a more elegant solution (I hope so at least).

  5. #5
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change String Encoding Using VBA

    Quote Originally Posted by JBeaucaire View Post
    Since CSV is a text file you might be able to repair the text without actually OPENING the file in Excel as an Excel-formatted CSV. You might be able to edit it using text edit function in VBA.

    Can you upload a small sample? Two CSV files with just a few lines that show the original problem text, then the corrected text?



    xlAdept's approach for making tweaks after opening the CSV is great, you should be able to build on that, too.
    I'm glad you suggested this! I just checked the .CSV and it looks like there is no issue there.. the characters look okay. But when I open it in Excel, they break.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change String Encoding Using VBA

    Define elegant? Are you positing there is somewhere a list where some function would know outside of your macro what replacements to use automatically? I'm not certain of that.

    But a macro could be designed to keep/build its own dictionary of search/replace combinations, just more plumbing.


    Do you need to open the file in Excel? Perhaps a font issue?

  7. #7
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change String Encoding Using VBA

    Quote Originally Posted by JBeaucaire View Post
    Define elegant? Are you positing there is somewhere a list where some function would know outside of your macro what replacements to use automatically? I'm not certain of that.

    But a macro could be designed to keep/build its own dictionary of search/replace combinations, just more plumbing.
    I think there should be, yes. It is just an encoding issue, right? I assume Excel has a built in conversion table for different encodings so it can display text.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change String Encoding Using VBA

    Do you need to open the file in Excel? Perhaps a font issue?

  9. #9
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change String Encoding Using VBA

    Quote Originally Posted by JBeaucaire View Post
    Do you need to open the file in Excel? Perhaps a font issue?
    Indeed I do. No, this isn't a font issue (I'm using Calibri). I am seeing the errors as in this table here: http://www.i18nqa.com/debug/utf8-debug.html

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change String Encoding Using VBA

    A little Google search leads me to think you might want to IMPORT the CSV instead of just opening it...

    How to import a .csv file that uses UTF-8 character encoding

    Follow the steps outlined below to use Microsoft Excel 2007 to open a .csv file that uses UTF-8 character encoding.
    1. Open Microsoft Excel 2007.
    2. Click on the Data menu bar option.
    3. Click on the From Text icon.
    4. Navigate to the location of the file that you want to import. Click on the filename and then click on the Import button. The Text Import Wizard - Step 1 or 3 window will now appear on the screen.
    5. Choose the file type that best describes your data - Delimited or Fixed Width.
    6. Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin.
    7. Click on the Next button to display the Text Import Wizard - Step 2 or 3 window.
    8. Place a checkmark next to the delimiter that was used in the file you wish to import into Microsoft Excel 2007. The Data preview window will show you how your data will appear based on the delimiter that you chose.
    9. Click on the Next button to display the Text Import Wizard - Step 3 of 3.
    10. Choose the appropriate data format for each column of data that you want to import. You also have the option to not import one or more columns of data if you want.
    11. Click on the Finish button to finish importing your data into Microsoft Excel 2007.

  11. #11
    Registered User
    Join Date
    08-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change String Encoding Using VBA

    Quote Originally Posted by JBeaucaire View Post
    A little Google search leads me to think you might want to IMPORT the CSV instead of just opening it...
    That works excellently! Thanks!

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Change String Encoding Using VBA

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to change 1) part of string, 2) the order of characters inside a string, 3) format
    By Karl Gustaf Karsten in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-16-2015, 11:51 AM
  2. Compressing/Encoding Text String
    By shawnvw in forum Excel General
    Replies: 13
    Last Post: 10-15-2015, 11:24 AM
  3. Replies: 1
    Last Post: 05-23-2014, 05:44 AM
  4. Replies: 1
    Last Post: 01-04-2013, 07:43 PM
  5. How to change encoding for posting data from Excel 2007?
    By emmanuellecc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2009, 03:51 PM
  6. Change encoding
    By user867564 in forum Excel General
    Replies: 1
    Last Post: 04-17-2007, 04:31 AM
  7. UTF-8 encoding CSV
    By [email protected] in forum Excel General
    Replies: 0
    Last Post: 04-03-2006, 09:40 PM

Tags for this Thread

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