LinkBack URL
About LinkBacks
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).
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).
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
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 theicon 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!)
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).
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.Originally Posted by JBeaucaire
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?
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.
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
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.
- Open Microsoft Excel 2007.
- Click on the Data menu bar option.
- Click on the From Text icon.
- 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.
- Choose the file type that best describes your data - Delimited or Fixed Width.
- Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin.
- Click on the Next button to display the Text Import Wizard - Step 2 or 3 window.
- 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.
- Click on the Next button to display the Text Import Wizard - Step 3 of 3.
- 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.
- Click on the Finish button to finish importing your data into Microsoft Excel 2007.
That works excellently! Thanks!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks