Comparing sql field with excel field in LINQ

Question

So I've got a excel sheet table and a mssql table, and the two are being joined on excel table column 0 (first) and mssql column textfield2, using LINQ. The problem I'm having when it's joining alphanumeric values, which doesn't seem to work. It does work when the values are numeric.

        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excconnectionstring);
        var adapter2 = new SqlDataAdapter("SELECT * FROM Table1", sqlconnectionstring);

        var ds = new DataSet();
        adapter.Fill(ds, "excel");
        adapter2.Fill(ds, "sql");

        var excel = ds.Tables["excel"].AsEnumerable();
        var esqel = ds.Tables["sql"].AsEnumerable();

        var query = from exc in excel
                    from sql in esqel
                    where exc[0].ToString() == sql.Field<string>("textfield2")
                    select new
                    {
                        debnr = sql.Field<string>("debnr"),
                        bedrag = double.Parse(exc[5].ToString())/100,
                        description = DateTime.Parse(exc[7].ToString(), new CultureInfo("nl-NL")).ToString("MMM yyyy"),
                        text1 = exc[0].ToString(),
                        projectno = sql.Field<string>("textfield1"),
                        central = sql.Field<string>("CentralizationAccount").Trim()
                    };

edit: Seems the alphanumeric do work when I order the values in the excel sheet with the alphanumeric values on top. But then I have the problem that it doesn't work on the numeric values..

Side-note: use [`Join` instead of `Where`](http://stackoverflow.com/questions/5551264/why-is-linq-join-so-much-faster-than-linking-with-where) when you link tables in Linq-To-Objects. — Tim Schmelter, Dec 05 '12 at 21:38
I cannot reproduce that; the join works fine with your code and strings in both tables. I guess there's something missing. — Serge Belov, Dec 05 '12 at 21:49
You must make sure that the numeric values in the first column are converted to text _in the sheet itself_. OleDB infers the type of a Excel column from the first couple of rows. Values that do not match the type are passed through as null. Also, watch leading and trailing spaces. — Gert Arnold, Dec 05 '12 at 22:56
The OleDBDataAdaptor sets the type of the column based on a short sample at the start of the data - if your early rows are all ints then it will set it as an int and spit the dummy when it encounters a string. There is a way to force it to set the type to string but I cant remember how offhand but if you do some research along these lines you will surely find it. When you get the answer post it up for us! — Dale M, Dec 06 '12 at 00:07
That's exactly what it was. I was able to edit the excel file to convert the column to text using interop and it worked! — Joeri, Dec 06 '12 at 14:23

score 0 · Accepted Answer · answered Dec 06 '12 at 14:32

As others have said, the problem occurred due to the OleDb infering the column type based on the first couple of rows. Using Interop TextToColumn I change the excel column data type to Text. Here's the code I use to do that:

        Workbook workBook = _excelApp.Workbooks.Open(Directory.GetCurrentDirectory() + "\\" + thisFileName,
            0, false, 5, "", "", false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);


        object fieldInfo = new int[1, 2] { { 1, 2 } };
        Range _range = ((Worksheet)workBook.Worksheets.get_Item("Sheet1")).UsedRange.Columns[1, Type.Missing];

        _range.TextToColumns(
 _range, XlTextParsingType.xlDelimited,
 XlTextQualifier.xlTextQualifierNone, Type.Missing,
 Type.Missing, Type.Missing, Type.Missing,
 Type.Missing, Type.Missing,
 Type.Missing,
 fieldInfo,
 Type.Missing, Type.Missing);

        _excelApp.DisplayAlerts = false;
        _excelApp.ScreenUpdating = false;
        _excelApp.Visible = false;
        _excelApp.UserControl = false;
        _excelApp.Interactive = false;

        workBook.SaveAs(Directory.GetCurrentDirectory() + "\\" + thisFileName, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing);
        workBook.Close(false, thisFileName, null);
        Marshal.ReleaseComObject(workBook);
        _excelApp.Quit();

Comparing sql field with excel field in LINQ

1 Answers1