title: How to use Split string function with OLEDB.Jet for Excel file in a query
body: <p>I'm developing a web application in ASP.NET Web Forms 4.5 which imports Excel (.XLS, .XLSX) files data inside an Access Database.</p>
  
  <p>I need to implement a query that separate an Excel worksheet column into 2 different fields.</p>
  
  <p>My query should be something like this:</p>
  
  <pre><code>string query = @"SELECT DISTINCT split(([MyExcelColumn], "" - "")(0) AS [CustomerID], 
                   split(([MyExcelColumn], "" - "")(1) AS [CustomerDescr]
                   FROM [Sheet]";
  
  // The separator string is " - ".
  
  // Executing the query
  DataTable dt = ExcelWorksheet.ListQuery(ExcelFileConnString, query);
  </code></pre>
  
  <p><code>ExcelWorksheet.ListQuery</code> is a method that executes a list query on the Excel file.
  This is the method implementation:</p>
  
  <pre><code>public static DataTable ListQuery(string connString, string query)
  {
      OleDbConnection conn = null;
      DataSet ds = null;
  
      try
      {
          conn = new OleDbConnection(connString);
          conn.Open();
          OleDbCommand cmd = new OleDbCommand(query, conn);
          OleDbDataAdapter da = new OleDbDataAdapter(cmd);
          ds = new DataSet();
          da.Fill(ds);
      }
      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          if (conn != null)
          {
              conn.Close();
              conn.Dispose();
          }
      }
  
      return ds.Tables[0];
  }
  </code></pre>
  
  <p>The method has been already tested multiple times since it is used for some other queries on the application, but with this query I'm getting this error:</p>
  
  <pre><code>{"Synthax error (missing operator) in the expression of the query 'split(([MyExcelColumn], \" - \")(0) AS [CustomerID], \r\n                                    split(([MyExcelColumn], \" - \")(1) AS [CustomerDescr]\r\n                                    FROM ['Sheet$']'."}
  </code></pre>
  
  <p>I tried to make some changes with the parenthesis but nothing happened.</p>
  
  <p>I think it has to do with the <code>(index)</code> of the <code>split</code> function result array... but what am I doing wrong?</p>