Using Access Oledb Engine with .Net Programs

    I have used the Microsoft Jet engine to read csv and excel files for quite a while. However, the newer versions of Excel are not readable utilizing the Jet Engine.  To read new Excel created with later versions, it is necessary to use the Microsoft Access Oledb DataBase Engine functions, which are actually part of Office 2010.  So, unless you have 32 Bit Office 2010 installed, you need to download this from Microsoft.

     The problem I encountered revolved around using .Net applications, both Web Based and desktop, to read Excel files using the above mentioned method.   A problem occurs when trying to install the Access DataBase engine on computers running 64 Bit Office.  There is a 64 bit version of the Access Oledb Engine you can install, but the .Net programs only run with the 32 bit version. If you try to install the 32 bit Access Oledb engine normally, you receive an error stating that you are trying to install a 32 bit driver on a system containing 64 bit Office applications.

    So, in order to install the necessary 32 bit Access Oledb engine, you must install the program from the command line and add /passive to the command. This bypasses the message about 32/64 incompatibilities and allows installation necessary for the .Net programs

ie
c:\accessDataBaseEngine.exe /passive

is the command line you enter

Split function in C#

           char[] delimiterChars = {','};

            string[] parms= vLine.Split(delimiterChars);

            if (parms.Length == 0)
            {
                return;
            }

vb

Dim seperator() As String = {"@", "#", "%"}

Dim strArray = str.Split(seperator, StringSplitOptions.RemoveEmptyEntries)

LinqToExcel Reading CSV files

     I have experimented with LinqToExcel to read CSV files.

    SheetNames are not defined with a CSV file. The var sheetNames has a length of 0 after invoking the GetWorkSheetNames.

    No column names are returned. You need to access the fields returned through their ordinal number.

     If the csv file has a header row, use the _excel.Worksheet() command instead of the WorksheetNoHeader command.   This will bypass the first row.
     The WorksheetNoHeader returns the first row as data.


       public void TestClaimsExcelToXml()
        {
            var fileName = @"c:\test.csv";

            var _excelFile = new ExcelQueryFactory(fileName);

            var sheetnames = _excelFile.GetWorksheetNames();

            _excelFile.TrimSpaces=TrimSpacesType.Both;

            var csvRows = from a in _excelFile.WorksheetNoHeader()
                              select a;

            var _csvRows = csvRows.ToList();

            var output = (from myLine in _csvRows
                select (myLine[0] + "**" + myLine[1] + "**" + myLine[2] + "**" + myLine[3])).ToList();

            var alllines = string.Join(Environment.NewLine, output);

            StringBuilder sb = new StringBuilder();

            foreach (var myLine in _csvRows)
            {
                sb.Length = 0;
                sb.Append("column 0:");
                sb.Append(myLine[0]);
                sb.Append(" *** ");
                sb.Append("column 1:");
                sb.Append(myLine[1]);
                sb.Append(" *** ");
                sb.Append("column 2:");
                sb.Append(myLine[2]);
                sb.Append(" *** ");
                sb.Append("column 3:");
                sb.Append(myLine[3]);
                var outLine = sb.ToString();

                Console.WriteLine(outLine);
            }

        }

Visual Studio Snippets - Missing or unspecified language

    I have been getting a list of messages in the Snippets output window when I load a project that a number of snippets had a 'missing or unspecified language attribute' and could not figure out why these messages appeared. I had checked the Language attribute on the snippets and they were correct, but still got the message.  I had set up folders for each language type, but still got the messages. I finally figured out that I had specified they all were VB snippets in the Snippet manager and those that were not VB would cause the messages in the output window, ie when loading a VB project, the CSharp snippets would generate the error message.
   I fixed this by going into the snippet manager and specifying the Language, VB or CSharp, and adding the snippets to the correct language and removing them from the invalid language. And now these messages of invalid language attributes do not appear on my output screen.