How to use Split string function with OLEDB.Jet for Excel file in a query

Question

I'm developing a web application in ASP.NET Web Forms 4.5 which imports Excel (.XLS, .XLSX) files data inside an Access Database.

I need to implement a query that separate an Excel worksheet column into 2 different fields.

My query should be something like this:

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);

ExcelWorksheet.ListQuery is a method that executes a list query on the Excel file. This is the method implementation:

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];
}

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:

{"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$']'."}

I tried to make some changes with the parenthesis but nothing happened.

I think it has to do with the (index) of the split function result array... but what am I doing wrong?

Answer 1

As you have discovered, you cannot use the VBA Split() function in an Access SQL query, Since you are only splitting [MyExcelColumn] into two parts you can use this instead:

SELECT 
    Mid([MyExcelColumn],1,InStr([MyExcelColumn]," - ")-1) AS [CustomerID],
    Mid([MyExcelColumn],InStr([MyExcelColumn]," - ")+3) AS [CustomerDescr]
FROM [Sheet]