Jaimon's Blog

An Excel Addin to work with Unicode CSV files

Excel treat .csv files as text files and will replace all Unicode characters with “?” when saved normally. If you want to preserve the Unicode characters, you would need to save the file as “Unicode text (*.txt)”, which is a Tab delimited file. Since I couldn’t find any existing solution to preserve Unicode characters in CSV format, I thought I’ll give it a go in creating an Excel Addin to do just that.

Installing the Addin

If you want to try this on your Excel 2007 or 2010, please download http://blog.jaimon.co.uk/UnicodeCSV/setupfiles.zip and extract the files to a folder before running setup.exe.


****Update****

Some of the readers have experienced issues with this installation file on some machines. For a successful installation,

  • Please make sure to install this as Local admin user
  • If setup.exe doesn’t work for some reason try running the UnicodeCSVAddin.vsto file
(Many thanks to our reader Sean for pointing this out)

Once the Addin is installed, it will look out for any CSV file being opened. If you’re opening an existing Unicode CSV file, this Addin will override the current saving method (either by pressing Ctrl-S, or clicking on any of the Save buttons/options from the Ribbon or menu) and will always save as a Unicode file.

But if you are creating a new file and want to save that as a Unicode csv file, then choose the Save or SaveAs option from the newly created Ribbon item “Unicode CSV”.

When clicking on Save from “Unicode CSV” Ribbon, it will make sure the file extension is .csv, if not it will prompt you to select another file name. Once a file is saved this way, then you don’t need to use the “Unicode CSV” Ribbon item any more, just save as you do normally.

How does it work?

As Excel can handle Unicode text (*.txt) files perfectly well, I’m relying on that to make this Addin work. On save, this Addin will be calling Excel’s SaveAs method to create a temporary Unicode Text file. It will then overwrite the original file by replacing all Tabs with comma and reload it. As it is a CSV file, Excel doesn’t preserve any column width adjustments or last active cell. Currently this Addin will reset the last active cell, and I think it should be pretty straightforward to reset column widths as well, if you wish to do so.

The code behind (for the geeks!)

This was done as an Excel 2007 application level Addin project in Visual Studio 2010 targeting .Net framework 4.0, so it can be installed on both 2007 & 2010 versions of MS Office. Feel free to fork the project from https://github.com/jaimonmathew/Unicode-CSV-Addin as a Visual Studio 2010 solution, if you want to make any modification or create an MSI installer etc. (Please read the license and notice files before using the code)

–ThisAddin.cs

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections.Generic;
using System.IO;

namespace UnicodeCSVAddin
{
    public partial class ThisAddIn
    {
        private Excel.Application app;
        private List<string> unicodeFiles; //a list of opened Unicode CSV files. We populate this list on WorkBookOpen event to avoid checking for CSV files on every Save event.
        private bool sFlag = false;

        //Unicode file byte order marks.
        private const string UTF_16BE_BOM = "FEFF";
        private const string UTF_16LE_BOM = "FFFE";
        private const string UTF_8_BOM = "EFBBBF";

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            app = this.Application;
            unicodeFiles = new List<string>();
            app.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(app_WorkbookOpen);
            app.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(app_WorkbookBeforeClose);
            app.WorkbookBeforeSave += new Excel.AppEvents_WorkbookBeforeSaveEventHandler(app_WorkbookBeforeSave);
        }

        void ThisAddIn_Shutdown(object sender, EventArgs e)
        {
            app = null;
            unicodeFiles = null;
        }

        void app_WorkbookBeforeSave(Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)
        {
            //Override Save behaviour for Unicode CSV files.
            if (!SaveAsUI && !sFlag && unicodeFiles.Contains(Wb.FullName))
            {
                Cancel = true;
                SaveAsUnicodeCSV(false, false);
            }
            sFlag = false;
        }

        //This is required to show our custom Ribbon
        protected override Office.IRibbonExtensibility CreateRibbonExtensibilityObject()
        {
            return new Ribbon1();
        }

        void app_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
        {
            unicodeFiles.Remove(Wb.FullName);
            app.StatusBar = "Ready";
        }

        void app_WorkbookOpen(Excel.Workbook Wb)
        {
            //Check to see if the opened document is a Unicode CSV files, so we can override Excel's Save method
            if (Wb.FullName.ToLower().EndsWith(".csv") && isFileUnicode(Wb.FullName))
            {
                if (!unicodeFiles.Contains(Wb.FullName))
                {
                    unicodeFiles.Add(Wb.FullName);
                }
                app.StatusBar = Wb.Name + " has been opened as a Unicode CSV file";
            }
            else
            {
                app.StatusBar = "Ready";
            }
        }

        /// <summary>
        /// This method check whether Excel is in Cell Editing mode or not
        /// There are few ways to check this (eg. check to see if a standard menu item is disabled etc.)
        /// I know in cell editing mode app.DisplayAlerts throws an Exception, so here I'm relying on that behaviour
        /// </summary>
        /// <returns>true if Excel is in cell editing mode</returns>
        private bool isInCellEditingMode()
        {
            bool flag = false;
            try
            {
                app.DisplayAlerts = false; //This will throw an Exception if Excel is in Cell Editing Mode
            }
            catch (Exception)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// This will create a temporary file in Unicode text (*.txt) format, overwrite the current loaded file by replaing all tabs with a comma and reload the file.
        /// </summary>
        /// <param name="force">To force save the current file as a Unicode CSV.
        /// When called from the Ribbon items Save/SaveAs, <i>force</i> will be true
        /// If this parameter is true and the file name extention is not .csv, then a SaveAs dialog will be displayed to choose a .csv file</param>
        /// <param name="newFile">To show a SaveAs dialog box to select a new file name
        /// This will be set to true when called from the Ribbon item SaveAs</param>
        public void SaveAsUnicodeCSV(bool force, bool newFile)
        {
            app.StatusBar = "";
            bool currDispAlert = app.DisplayAlerts;
            bool flag = true;
            int i;
            string filename = app.ActiveWorkbook.FullName;

            if (force) //then make sure a csv file name is selected.
            {
                if (newFile || !filename.ToLower().EndsWith(".csv"))
                {
                    Office.FileDialog d = app.get_FileDialog(Office.MsoFileDialogType.msoFileDialogSaveAs);
                    i = app.ActiveWorkbook.Name.LastIndexOf(".");
                    if (i >= 0)
                    {
                        d.InitialFileName = app.ActiveWorkbook.Name.Substring(0, i);
                    }
                    else
                    {
                        d.InitialFileName = app.ActiveWorkbook.Name;
                    }
                    d.AllowMultiSelect = false;
                    Office.FileDialogFilters f = d.Filters;
                    for (i = 1; i <= f.Count; i++)
                    {
                        if ("*.csv".Equals(f.Item(i).Extensions))
                        {
                            d.FilterIndex = i;
                            break;
                        }
                    }
                    if (d.Show() == 0) //User cancelled the dialog
                    {
                        flag = false;
                    }
                    else
                    {
                        filename = d.SelectedItems.Item(1);
                    }
                }
                if (flag && !filename.ToLower().EndsWith(".csv"))
                {
                    MessageBox.Show("Please select a CSV file name first");
                    flag = false;
                }
            }

            if (flag && filename.ToLower().EndsWith(".csv") && (force || unicodeFiles.Contains(filename)))
            {
                if (isInCellEditingMode())
                {
                    MessageBox.Show("Please finish editing before saving");
                }
                else
                {
                    try
                    {
                        //Getting current selection to restore the current cell selection
                        Excel.Range rng = (Excel.Range)app.ActiveCell;
                        int row = rng.Row;
                        int col = rng.Column;

                        string tempFile = System.IO.Path.GetTempFileName();

                        try
                        {
                            sFlag = true; //This is to prevent this method getting called again from app_WorkbookBeforeSave event caused by the next SaveAs call
                            app.ActiveWorkbook.SaveAs(tempFile, Excel.XlFileFormat.xlUnicodeText);
                            app.ActiveWorkbook.Close();

                            if (new FileInfo(tempFile).Length <= (1024 * 1024)) //If its less than 1MB, load the whole data to memory for character replacement
                            {
                                File.WriteAllText(filename, File.ReadAllText(tempFile, UnicodeEncoding.UTF8).Replace("\t", ","), UnicodeEncoding.UTF8);
                            }
                            else //otherwise read chunks for data (in 10KB chunks) into memory
                            {
                                using (StreamReader sr = new StreamReader(tempFile, true))
                                using (StreamWriter sw = new StreamWriter(filename, false, sr.CurrentEncoding))
                                {
                                    char[] buffer = new char[10 * 1024]; //10KB Chunks
                                    while (!sr.EndOfStream)
                                    {
                                        int cnt = sr.ReadBlock(buffer, 0, buffer.Length);
                                        for (i = 0; i < cnt; i++)
                                        {
                                            if (buffer[i] == '\t')
                                            {
                                                buffer[i] = ',';
                                            }
                                        }
                                        sw.Write(buffer, 0, cnt);
                                    }
                                }
                            }
                        }
                        finally
                        {
                            File.Delete(tempFile);
                        }

                        app.Workbooks.Open(filename, Type.Missing, Type.Missing, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, ",");
                        Excel.Worksheet ws = app.ActiveWorkbook.ActiveSheet;
                        ws.Cells[row, col].Select();
                        app.StatusBar = "File has been saved as a Unicode CSV";
                        if (!unicodeFiles.Contains(filename))
                        {
                            unicodeFiles.Add(filename);
                        }
                        app.ActiveWorkbook.Saved = true;
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show("Error occured while trying to save this file as Unicode CSV: " + e.Message);
                    }
                    finally
                    {
                        app.DisplayAlerts = currDispAlert;
                    }
                }
            }
        }

        /// <summary>
        /// This method will try and read the first few bytes to see if it contains a Unicode BOM
        /// </summary>
        /// <param name="filename">File to check for including full path</param>
        /// <returns>true if its a Unicode file</returns>
        private bool isFileUnicode(string filename)
        {
            bool ret = false;
            try
            {
                byte[] buff = new byte[3];
                using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    fs.Read(buff, 0, 3);
                }

                string hx = "";
                foreach (byte letter in buff)
                {
                    hx += string.Format("{0:X2}", Convert.ToInt32(letter));
                    //Checking to see the first bytes matches with any of the defined Unicode BOM
                    //We only check for UTF8 and UTF16 here.
                    ret = UTF_16BE_BOM.Equals(hx) || UTF_16LE_BOM.Equals(hx) || UTF_8_BOM.Equals(hx);
                    if (ret)
                    {
                        break;
                    }
                }
            }
            catch (IOException)
            {
                //ignore any exception
            }
            return ret;
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

        #endregion
    }
}

The crux of the application is in ThisAddin.cs file, and I’ve put some comments wherever I can in the code to explain the functionality. But here is a brief info on how the code works. (As it was my first attempt at using VSTO (Visual Studio Tools for Office) and the fact that I’m a Java/Oracle developer for my day job, I’m sure the .net experts here might find a better way of coding in many places).

On the ThisAddin_Startup method I’m adding three event handlers,

  1. Application_WorkbookOpen – to check for whether the new file being opened is a Unicode CSV file. The code looks at the BOM (Byte Order Mark) bytes to figure out whether it’s a Unicode file or not. At the moment it will check for UTF8, UTF16-LE and UTF16-BE preambles. If it’s a Unicode CSV file, then it will add the file name to a global list
  2. Application_WorkbookBeforeClose – to remove file name from the global Unicode CSV file list
  3. Application_WorkbookBeforeSave – This will call the method SaveAsUnicodeCSV to override Excel’s default Save behaviour if the filename is in the global Unicode CSV file list. Once some basic checks are passed, SaveAsUnicodeCSV method will save the current file as a Unicode Text file to a temporary file, and close it. We now need to replace all the Tabs in that file with a comma. If the file is small enough (<= 1MB), I’m using File.ReadAllText and File.WriteAllText to read and write in one go. Otherwise I’m using a StreamReader and StreamWriter to read/write data in 10KB chunks. (BTW, these are numbers I plucked out of thin air, so feel free to change this to suite your use case). Once the new file has been created, we will then open the new csv into current workbook.

Ribbon1.xml has the entries to create new Ribbon items.

Jaimon

About these ads

August 23, 2011 - Posted by | Uncategorized | , , , , ,

83 Comments »

  1. Great Thing!!! Really helped me, because before I had to save mine CSVs in open office!
    Is there any way I can automate use of this add-inn using Macroses of VBA?

    Comment by Nick | October 7, 2011 | Reply

  2. You save my day. Thanks a lot.

    Comment by cklee75 (@cklee75) | October 8, 2011 | Reply

  3. Thank you very much

    Comment by Me | October 10, 2011 | Reply

  4. hello,
    I just want check, this Addin does not work with Exel 2003, does it?
    thank you!

    Comment by Natalia Aralova | October 11, 2011 | Reply

    • Hi,

      The installer I’ve provided only works for Office 2007 and 2010. But with the source code provided, you could use Visual Studio 2005 to create a version for Excel 2003, that is if you are a .Net programmer. Since I don’t have a copy of VS2005 myself, I’m afraid I can’t provide one.

      HTH

      Jaimon

      Comment by Jaimon Mathew | October 12, 2011 | Reply

  5. [...] the entire sheet as UTF8 … und hier wird ein Addin beschrieben, dass Ähnliches leisten soll: An Excel Addin to work with Unicode CSV files __________________ "!DLROW ,OLLEH" If putting it in the public domain isn't simply [...]

    Pingback by unicode in php - Seite 2 - php.de | October 19, 2011 | Reply

  6. You are a bloody God! NOTHING ELSE – NO OTHER ADVICE WORKED! Except your AWESOME tool! God bless you man!

    Comment by Jojo Mojo | October 23, 2011 | Reply

  7. good stuff! thank you very much

    Comment by Shlomi | October 27, 2011 | Reply

  8. Hi Jaimon,

    great tool – how do I know the codepage which was used creating the file?

    Kai

    Comment by Kai | October 28, 2011 | Reply

    • Hi Kai, it will be the default Excel uses when saving as a Unicode txt file. In my machine, that is UTF16-LE.
      I’ve created CSV files from Java using UTF8 codepage (ie with UTF8 BOM bytes), and Excel do handle them correctly as well.

      HTH

      Jaimon

      Comment by Jaimon Mathew | October 28, 2011 | Reply

  9. Very intresting Addin but I get this error during instaling on Windows 7, 64 bit and Office 2010:

    System.Security.SecurityException: Customized functionality in this application will not work because the certificate used to sign the deployment manifest for UnicodeCSVAddin or its location is not trusted. Contact your administrator for further assistance.

    Comment by Ricard | November 6, 2011 | Reply

    • Hi Ricard,

      Are you running the installer from a networked drive? Can you copy the installation files to your local drive and try it again?

      HTH

      Jaimon

      Comment by Jaimon Mathew | November 7, 2011 | Reply

      • Hello,

        I do have the same problem trying to install your tool for excel 2007. Some of us are running on windows 7 and others on windows xp. On 12 machines, only 2 seem to work, for the others I get the same error message as Ricard. And the installation files are on local drive.

        Do you have a hint on what I should do to properly install it.

        Many many thanks for your hard work!

        Sonia

        Comment by Sonia | March 6, 2012 | Reply

    • Hi Ricard,

      having the same problem here, did you manage to install it afterall ?

      I could use a hint or two!

      Thanks

      Comment by Sonia | March 6, 2012 | Reply

    • Windows has marked the downloaded file (and if you extracted it, the extracted files too) as “blocked”.

      If you right click each file and

      Properties >> Unblock

      then it’ll work fine.

      Comment by AndyJ | May 28, 2012 | Reply

      • The Properties> unblock on all files worked for me, thanks

        Comment by CarlPH | November 19, 2013 | Reply

  10. How can I make this work on MAC OS ? I have office 11 on MAC.

    Comment by david | November 22, 2011 | Reply

    • Hi David,

      I don’t think it will work on Office 2011, as it doesn’t have the full addin API support. It does have VBA support though. So may be a VBA function can achieve the same on Mac.

      Jaimon

      Comment by Jaimon Mathew | November 23, 2011 | Reply

      • I am a simple user and have no coding experience other than writing simple macros.
        I don’t know the diff between the full or partial addin support. I do see that I have an option to install the addins in general.
        I don’t have the knowhow to write such VBA code. I imagine it should be in a form of a macro. it would be great if you could post it here.

        Comment by david | November 23, 2011 | Reply

  11. Thanks a lot.
    (SEPAS)

    Comment by Mahdi | November 24, 2011 | Reply

  12. Nice addon, thanks! Is there any way to run it from within the excel sub?

    Comment by Artur | December 21, 2011 | Reply

  13. Jaimon,

    This will make my life tremendously easy!!!!!! This is totally super awesome!!!!!
    THANKS SO VERY MUCH FOR PUTTING THIS TOGETHER!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :)

    Happy 2012!

    Comment by Fernando Ladeira | December 29, 2011 | Reply

  14. The only problem with this is that once I save my data to csv, the dates get converted to American format. Very annoying because once I convert it to json I have too much data to be able to write things in manually. Does anyone know how to fix the date conversion problem? Thanks.

    Comment by Bissan | January 31, 2012 | Reply

    • I have a similar but different problem – dates formatted as eg 2012-09-24 become 24/09/2012 (even if I set the cells to be text format). I use the CSV as an intermin stage in a MySQL upload and particularly need the date format preserved. I am sure this problem did not occur a few months back??!!?!

      Comment by Phil Wood | January 31, 2013 | Reply

  15. Lifesaver! :D

    Comment by Henry Chong | February 16, 2012 | Reply

  16. [...] Jaimon Mathew to the rescue, who wrote a little Excel Add-In to get around exactly this issue – it works exactly as advertised, and allows a save as [...]

    Pingback by Excel 2010 and Unicode CSV files – Henry Chong | February 16, 2012 | Reply

  17. Many thanks.

    Comment by Marty | February 16, 2012 | Reply

  18. Hello,

    I install this addin on several computers. For some, it perfectly works but for other, I have this error when I install the soft:

    Customized functionality in this application will not work because the certificate used to sign the deployment manifest for UnicodeCSVAddin or its location is not trusted. Contact your administrator for further assistance.

    The setup is on the local disk. Is it possible that a GPO block the execution? Or if windows is not updating?

    Thank you.

    Comment by Claire | March 6, 2012 | Reply

    • Hi Folks,

      I have exact same problem during installation, what could be wrong?

      Thanks for your help
      –Nisarshk

      Comment by Nisarshk | March 13, 2012 | Reply

      • Hi,

        Can you see if this link helps with any ClickOnce related issues?

        http://social.msdn.microsoft.com/forums/en-US/vsto/thread/e8eb1540-a3c6-4987-9d89-909990e117d7

        Thanks

        Jaimon

        Comment by Jaimon Mathew | March 13, 2012 | Reply

        • First off, thank you so much for this solution. It’s been great. I want to echo comments by Claire. I installed this on several computers before running into the “Customized functionality… security issue”. Two things had to happen to fix this. 1. The user had to be a local admin on their account or they would not be able to install at all. 2. For the problem computers, it appeared the setup.exe file was not actually reading in the UnivodeCSVAddin.vsto file. Double-clicking that file was the answer! Hope that helps.

          Comment by Sean | April 12, 2012 | Reply

          • Hi Sean,

            Many thanks you for sharing that..

            Jaimon

            Comment by Jaimon Mathew | April 12, 2012

  19. Hi Jaimon,

    Most useful. Thanks.

    Comment by Bernhard | March 7, 2012 | Reply

  20. Hi Jaimon this is so cool!!!
    Noted someone else had the issue with American date format… any suggestions?

    Comment by Jane | March 14, 2012 | Reply

    • Hi Jane,

      I think it may be caused by your regional settings. Can you make sure your regional settings are set to your country (via Control Panel)

      HTH

      Jaimon

      Comment by Jaimon Mathew | March 14, 2012 | Reply

  21. Awesome, exactly what I was looking for. Thanks so much!

    Comment by T Allen | April 1, 2012 | Reply

  22. It was a great help for me. Massive thanks!

    Comment by RP | April 14, 2012 | Reply

  23. Great Job. It cool man.

    I used to use Access to import excel file, then use Access to export to UTF-8 format. The direct way is more productive.

    Thanks.

    Comment by Jeffrey Wong | April 27, 2012 | Reply

  24. Great, solved an annoying problem with Unicode CSV import/export on one of our internal websites. Thank you!

    Comment by Chris | May 10, 2012 | Reply

  25. Hi. first i would like to say great job. it is working great for me.
    i was wondering if it possible to covert only part of the sheet (i need only 6-7 rows to be convert to CSV )
    i am a simple user with no knowledge in VB or macro or anything like it. if you can contact me even via email it would be great. THANKS

    Comment by Eran | May 19, 2012 | Reply

  26. Hi,

    I am getting the error with Exel 2010 no matter whether I use setup or UnicodeCSVAddin.vsto:

    “Customized functionality in this application will not work because the certificate used to sign the deployment manifest for UnicodeCSVAddin or its location is not trusted. Contact your administrator for further assistance.”

    Any pointers would be appreciated.

    Worst case, is there an uninstall or any clean I need to do if I am not able to use the addin?

    Comment by Manish | May 24, 2012 | Reply

  27. Thanks, this add-in is awesome. It helps me much much to convert excel file to csv files to import into other systems.

    Comment by Lại Tuấn Cường | May 31, 2012 | Reply

  28. Hi Jaimon,
    I’m using Excel 2010 and had exactly the problem you tried to solve here…
    I must tell you that you saved my life and saved me from searching endless hours for a working solution. It worked for me like a charm and without any issue.
    Thank you so much
    With my best Regards
    Oskar

    Comment by Oskar K. | June 5, 2012 | Reply

    • One question please: is it possible to get your add-in for semicolon separated csv? The actual one is only for comma separated.
      Thanks in advance
      Regards
      Oskar

      Comment by Oskar K. | June 8, 2012 | Reply

      • Hi Oskar, it will be easier to change the separator character, but I’m not sure how Excel will render a semi colon separated file though.

        Comment by Jaimon Mathew | June 9, 2012 | Reply

        • We also required a semi-colon delimiter for an external app that wouldn’t accept anything else.
          Excel renders CSV with semi-colons with no problem. I changed the code and recompiled in Visual Studio 2010 then published it and it works perfectly.
          The only minor problem I encountered was that I had Excel 2010 installed and Visual Studio wanted to update to take that into effect so I had to change the setting to not update the Office version.

          Comment by Phil | October 23, 2012 | Reply

          • Dear Phil, is it possible for you to send me your modificated version of the Unicode CSV? I also have a requirement of a semi-colon, so it would save me time when you could send it. Thanks you so much in advance!
            Reinier
            reinier@fox-creation.nl

            Comment by Reinier | February 5, 2013

          • Hi Phil,

            could you please send me your modified version also? I really need this app with semi colon.
            rsardor@gmail.com. Thanks in advance.

            Best
            Sardor

            Comment by Sardor | February 8, 2013

        • Hello,

          first of all I would like to thank you for the addin. I downloaded it and indeed saves a csv file in unicode format.
          I have however the same problem with the comma delimiter. The initial csv file is delimited with “;”, whereas the new one is delimited with “,” and cannot be opened by Excel.

          Could you please send me a version that saves the file with semicolon delimiter?

          Thanks and regards,
          M

          Comment by Maria Tsaroucha | December 13, 2013 | Reply

  29. Worked great for me! Thanks

    Comment by Todd | June 21, 2012 | Reply

  30. It works! Thank you for sharing!

    Comment by Shen | July 11, 2012 | Reply

  31. You know it man, you did great job !

    Comment by Aaron | July 14, 2012 | Reply

  32. Hello, I have problem with is addin…
    I want to use this one in Excel 2003, but I don’t have any version of Visual Studio
    How can I solve this problem?

    Comment by Francis | July 31, 2012 | Reply

  33. Thanks for sharing!!!!!!!!!! awesome addin!!!

    Comment by Lee | August 7, 2012 | Reply

  34. [...] 파일 이 파일은 유니코드가 아니지만, http://jaimonmathew.wordpress.com/2011/08/23/excel_addin_to_work_with_unicode_csv/ 에서 Add-in을 사용하여 Unicode 로 저장된 csv를 export 할 수 있다. ( csv 포맷은 [...]

    Pingback by 다국어 관련 팁들 | 게임 프로그래머의 블로그 | August 12, 2012 | Reply

  35. Brilliant! the only problem I have is the way dates are saved. The Dutch standard is dd-mm-yyyy. In the resulting CSV I get dd/MM/yyyy which is confusing for users. I cannot change this myself. How do I get the right datelayout?

    Comment by Ellis Staas | September 4, 2012 | Reply

  36. [...] Plugin location: http://jaimonmathew.wordpress.com/2011/08/23/excel_addin_to_work_with_unicode_csv/ [...]

    Pingback by Uploading Unicode CSVs to Salesforce | Force Collective | September 26, 2012 | Reply

  37. Your addin has made my life SO much easier. I love that all I have to do is go to the tab and click the save option to export into a CSV file! Any chance that you’d have an addin for importing CSV tab-delimited UTF-8 files into Excel 2010.

    Comment by Tina | January 4, 2013 | Reply

  38. This is great stuff! I only have one problem: When I try to call your addin from within a VBA macro (by using
    Application.Run (“UnicodeCSVAddin”)
    I get an error 1004, something about macro not available.

    Does anyone have an idea how to invoke the addin from a vba macro?

    Comment by Podo | January 19, 2013 | Reply

  39. Hi,
    This is great project, and thanks for all of work.. but ..
    Could you do us all favor and digitally sign the DLL files.. It simply does not work work with new Office 2010 security. Just use signcode.exe from Microsoft SDK (framework)
    http://msdn.microsoft.com/en-us/library/aa231196%28VS.60%29.aspx

    Thanks

    Comment by Svastarija | January 22, 2013 | Reply

  40. Thank you very much. This solved the issue. Saved whole bunch of time and effort. Great tool. Keep it up!

    Comment by Sabin Shrestha | January 22, 2013 | Reply

  41. Great as far as it goes but I have also run into the problem that other folk have spoken about, i.e. dates are converted to the American format mm/dd/yy as opposed to the format dd/mm/yyyy used more commonly in Europe. My locality settings are corect, i.e. English(United Kingdom). A shame as otherwise this AddIn does th job.

    Comment by Rob Birrell | February 5, 2013 | Reply

    • Unfortunately I had to implement a workaround. I ended up inserting columns of commas between the valid data and then saving the spreadsheet as unicode text. I then edited the resulting files in Notepad++ and did a global search and replace of “,” with , (i.e. to get rid of the quoted commas). Finally, I changed the extension of the file to .csv. This allowed me to import the comma separated data into CRM 2011 with dates and special characters (in Polish names etc.) retained.

      Comment by Rob Birrell | February 5, 2013 | Reply

  42. Amazing. You’ve saved me so much time and effort. Why this couldn’t have been standard in excel, I don’t know…

    Comment by martin | February 7, 2013 | Reply

  43. As someone has previously mentioned, this is not working with Excel 2010 security. Can you please fix this? Thanks.

    Comment by Meghan | February 13, 2013 | Reply

  44. excellent! just saved me hours, why cannot MS do this as standard?

    Comment by Raydarman | February 25, 2013 | Reply

  45. Excellent! I was searching for solution from many days. It is really a great piece of code and thanks for your generosity to share with every one.

    Comment by Rajesh Khatri | February 26, 2013 | Reply

  46. Hello Jaimon, thanks a lot! Works perfectly! :) But in my current project, the pure “comma separated” is not enough :( Is there any chance to compile it with different separator than comma?
    In the “We now need to replace all the Tabs in that file with a comma.” – could that be changed to “…. with a semicolon” (that separator I’ve desperate need)

    Comment by miro | April 3, 2013 | Reply

    • Hi,

      You’ll need to recompile the source with the new separator

      Jaimon

      Comment by Jaimon Mathew | April 3, 2013 | Reply

    • Miro,

      I would suggest you look at CSV Fix to run on your file for converting the character types etc. The only things with CSVFix is it is Comma Quote delimited.
      http://code.google.com/p/csvfix/

      Comment by Alan | July 25, 2013 | Reply

  47. Hi Jaimon!
    This looks like a solution I’m looking for. I installed it, but I am working with Serbian Cyrillic and UTF8 always works fine, but the addin just doesn’t save Cyrillic, all I get when I save the file with “чћжшђ” letters is “шђчћжЧЋЖШЂ”.

    Any ideas!
    Thanks!!!

    Comment by Konstantin | April 21, 2013 | Reply

  48. Hello Jaimon,
    I’ve downloaded the source on gihub and downloaded visual studio 2010.
    But how do i do to open this project ? each time it’s say me impossible to open project.
    I’ve tried with C# Visual Basic web developper each time same message, also i’ve found the line of code for the , by a ;
    Thx by advance

    Comment by jaylab | July 19, 2013 | Reply

    • Hi
      If you’re using VS 2010, you would need Ultimate, Premium or Professional edition to open this project. ie a Visual studio edition that includes Office developer tools. It wouldn’t work with any of the express editions.

      HTH

      Jaimon

      Comment by Jaimon Mathew | July 19, 2013 | Reply

      • Ok, so i’m stuck for now, till i found à premium édition, i’m french and i really need the semi colon solution, thanks for the explanation.

        Comment by sublab | July 21, 2013 | Reply

  49. Hi guys. I have a small problem. I am using Windows Vista and Office 2007. I installed this add-ins and it appear on tab bar. However it is not working correctly. It save the new file, but without correct encryption. I don’t have any errors, or anything like it.

    ??? Why it is always me??? It works on other computers, but not on mine.

    Comment by Luke78 | September 1, 2013 | Reply

    • Problem solved. It appear to be missing Service Pack in Office. Once I get to SP3, everything is working.

      Comment by Luke78 | September 3, 2013 | Reply

  50. Thanks for this article. You’ve saved my day!

    Comment by Peter | September 5, 2013 | Reply

  51. Hello Jaimon. Did you have cases where you had Excel breaking just AFTER exiting the BeforeSave event? I set Cancel to false, and do practically the same thing (with some changes to change the delimiter to a semicolon), but every time, my 2nd save breaks with a COM Exception (access denied), AFTER it has gone through all the code and has reopened the file perfectly.

    Comment by tec-goblin | October 25, 2013 | Reply

  52. Jaimon, thanks for the great tool! It has saved a few of my users a great deal of trouble, but as always is the case, one is having issues. I was just wondering if anyone is still around this thread because I have a few questions to ask.

    Comment by tony | October 29, 2013 | Reply

  53. […] Excel to Unicode CSV addin from this great fellow (hope, he don’t mind me using it). Just follow the simple steps to install it to your system […]

    Pingback by Automatically create in-apps in iTunesConnect with iMacros | Meisterred | November 8, 2013 | Reply

  54. thanks a lot friend. ur tool saved a lot of time for me. i used to do it open office . but now in a single click it converts to utf-8 csv file . thanks again…

    Comment by leonodice | December 27, 2013 | Reply

  55. This was awesome! Solved integration issues to imacros. Everyone should get this addin.

    Comment by Chris | June 6, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 35 other followers

%d bloggers like this: