How do I resolve the OleDb error "Syntax error in FROM clause"?

Question

Presently I am attempting to import a CSV file using this function:

    public DataSet ImportCommaSeparatedValueFileToDataSet(string SourceFile)
    {
        var dsImportCSVtoDataSetReturn = new DataSet();

        using (var objAdapter1 = new OleDbDataAdapter())
        {
            String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFile.Substring(0, SourceFile.LastIndexOf(@"\")) + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
            var objConnection = new OleDbConnection(sConnectionString);
            objConnection.Open();
            var objCmdSelect = new OleDbCommand("SELECT * FROM " + SourceFile, objConnection);

            objAdapter1.SelectCommand = objCmdSelect;
            objAdapter1.Fill(dsImportCSVtoDataSetReturn);
            objConnection.Close();
        }

        return dsImportCSVtoDataSetReturn;
    }

When I attempt to import a file that has no space in the filename, it works fine. When I attempt to import the following file:

D:\Workspace\WoldCard export.csv

Then I receive the following exception:

excException = {"Syntax error in FROM clause."}

Source = "Microsoft JET Database Engine"

StackTrace  "   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)\r\n   at CommonObjects4.clsUtilityOffice.ImportCommaSeparatedValueFileToDataSet(String SourceFile) in D:\\DevProjects\\CommonObjects4\\classes\\clsUtilityOffice.cs:line 262" string

So it seems pretty clear that the problem is having a space in the filename in the SQL clause; however, when I attempt to use single quotes to solve the problem:

var objCmdSelect = new OleDbCommand("SELECT * FROM '" + SourceFile + "'", objConnection); 

Then I receive this exception:

excException = {"''D:\Workspace\WoldCard export.csv'' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long."}

Also, when I try to use parameters:

var objCmdSelect = new OleDbCommand("SELECT * FROM @SourceFile", objConnection);
objCmdSelect.Parameters.Add("@SourceFile", SqlDbType.NVarChar).Value = SourceFile;

Then I receive this exception:

excException = {"Syntax error in query.  Incomplete query clause."}

Also, I later learned from http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1c399bf7-a6b3-47bb-8897-d4247b4938f0 that the table name cannot be a parameter. Does anyone have any suggestions? TIA.

Answer 1

Thank you for the feedback, DJ KRAZE, it helped me to get thinking about the problem in different ways. It turns out that I simply had to add square brackets around the table name if it has spaces in the name, though it had to be only the file name and not the full path:

var objCmdSelect = new OleDbCommand("SELECT * FROM [" + SourceFile.Substring(SourceFile.LastIndexOf(@"\") + 1, SourceFile.Length - SourceFile.LastIndexOf(@"\") - 1) + "]", objConnection);

See [] brackets in sql statements for more details.