Solved

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

Posted on 2015-01-02
7
354 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

809 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