Solved

Issues while opening a UTF16 encoded .csv file in excel using c#

Posted on 2015-01-02
7
383 Views
Last Modified: 2015-01-02
Hello Experts,

I am facing a problem in opening a .CSV file written using UTF-16 (Unicode) formatting in excel. My all tries to open it correctly either fails while opening of the file or the Unicode characters are not printed correctly in the opened excel file.
So is there any way where we can specify the encoding format of the file to be opened while opening using excel automation objects/functions.

I tried using Workbooks.open( filename) and Workbooks.OpenText( filepath, comma: true) functions.
Note: Both are working fine for a UTf-8 encoded .CSV file but fails for UTF-16(Unicode) case.

Please help me as i badly stuck here.

Thanks in advance
zakoha.csv
0
Comment
Question by:Rohan Kumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40527384
This works for me:

    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\zakoha.csv", _
        Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "zakoha"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 28591
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = """"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Open in new window


The thing that makes it works is:

TextFilePlatform = 28591
0
 

Author Comment

by:Rohan Kumar
ID: 40527403
Hey..can u please explain how to implement your solution in c# and which approach you are using here.

I am little confused here as i didn't find any parameter (neither at opening .csv file or saving as an excel file) where i can specify this TextFilePlatform value to 28591.

Thanks..
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527406
You create a connection to Excel, add a new workbook, and then you insert the text into it - it's like importing a database connection.

The parameter TextFilePlatform is from the QueryTables interface.

Here is a list of its properties: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.querytable_properties.aspx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:Rohan Kumar
ID: 40527694
Thank u very much for the information. I tried implementing this approach and my code is executing without any errors but still m not able to see any data on saved .xls file.

My code looks like this:

   var m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
   var m_objBook = (Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(Type.Missing));
           
    // Create a QueryTable that starts at cell A1.
    var m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
    var  m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
    var m_objQryTables = m_objSheet.QueryTables;
    var range = m_objSheet.get_Range("$A$1");
    var m_objQryTable = (Microsoft.Office.Interop.Excel._QueryTable)m_objQryTables.Add(@"TEXT;c:\zakoha.csv", range);
           
       //  m_objQryTable.CommandType = 0; // throws error Invalid arguments
       m_objQryTable.FieldNames = true;
       m_objQryTable.RowNumbers = false;
       m_objQryTable.FillAdjacentFormulas = false;
       m_objQryTable.PreserveFormatting = true;
       m_objQryTable  .RefreshOnFileOpen = false;
       m_objQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
       m_objQryTable.SavePassword = false;
       m_objQryTable.SaveData = true;
       m_objQryTable.AdjustColumnWidth = true;
       m_objQryTable.RefreshPeriod = 0;
       m_objQryTable.TextFilePromptOnRefresh = false;
       m_objQryTable.TextFilePlatform = 28591;
       m_objQryTable.TextFileStartRow = 1;
       m_objQryTable.TextFileParseType = Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited;
       m_objQryTable.TextFileTextQualifier = Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
       m_objQryTable.TextFileConsecutiveDelimiter = true;
       m_objQryTable.TextFileTabDelimiter = true;
       m_objQryTable.TextFileSemicolonDelimiter = false;
       m_objQryTable.TextFileCommaDelimiter = true;
       m_objQryTable.TextFileSpaceDelimiter = false;
      m_objQryTable.TextFileOtherDelimiter = @"""";
      m_objQryTable.TextFileTrailingMinusNumbers =true;

      m_objQryTable  .TextFileColumnDataTypes = new Array [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]; // throws error Invalid       arguments
      m_objQryTable.Refresh(false); // excel cannot find the text file to refrest this data range

      // Save the workbook and quit Excel.          
      m_objSheet.get_Range("A1").Value2 = "Rohan";
      m_objBook.SaveAs (  @"C:\Users\jsingh\Desktop\TestN", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing);
            m_objBook.Close(false, Type.Missing, Type.Missing);
            m_objExcel.Quit();



Only value that comes is Rohan that is wrote using .value2 property.

Can u please tell me what i am missing here.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527696
I note you say

// excel cannot find the text file to refrest this data range

Is the csv in the root of the C drive, as in:

"TEXT;c:\zakoha.csv"

If not, the above line will need updating.
0
 

Author Comment

by:Rohan Kumar
ID: 40527701
Aha got it...users folder was different so file was not located at runtime..

Thanks a lot.
0
 

Author Closing Comment

by:Rohan Kumar
ID: 40527702
Got quick and clear response. Gr8 job.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 2013 fails to install on new Windows 7 Dell Optiplex 5 146
Follow Up FO 21 115
Creting Master Button 15 139
Problem to cell option 1 47
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
This article was originally published on Monitis Blog, you can check it  here .   Some years back, I worked as the CTO.  During my tenure, I had a head of IT support reporting to me.  He did his job quite well and had a commendable sense of duty…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question