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
Solved

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

Posted on 2015-01-02
7
365 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
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
Line Spacing in Word 2 140
Document For...Next Loop in Visio 6 268
Notepad++ edit issue 7 82
Problem to cell option 1 36
This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
Most MSPs worth their salt are already offering cybersecurity to their customers. But cybersecurity as a service is wide encompassing and can mean many things.  So where are MSPs falling in this spectrum?
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

840 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