?
Solved

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

Posted on 2015-01-02
7
Medium Priority
?
428 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 2000 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
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

 

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
The viewer will learn how to edit the master slide. They will also learn how to combine multiple themes into one master slide to use them in their presentation.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

771 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