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.

Solution 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.

Solution 2

Roger try changing your connection string to look something like this

string fileName = SourceFile;   
string sConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; " + "Extended Properties=\"text;HDR=YES;FMT=TabDelimited;\"", fileName);

for the file path and name D:\Workspace\WoldCard export.csv

you can do one of 2 things add an UnderScore in the file name

D:\Workspace\WoldCard_export.csv or add double quotes around the file name

@"D:\Workspace\" + "WoldCard export.csv"; look also at the Path.Combine Method too

when you are trying to add Parameters look at using

Parameters.AddWithValues(@paramname, paramvalue) method as well

Comments

[MethodMan]

Roger think about how spaces work in a file name it's different behavior on your desktop vs when trying to open a file thru code

[MethodMan]

is this the path D:\Workspace\WoldCard..? if so should be D:\Workspace\WoldCard\export.csv please clarify Roder..

[user8128167]

The path is D:\Workspace\WoldCard export.csv. Please help me understand, why should it be D:\Workspace\WoldCard\export.csv?

[MethodMan]

if the path is D:\Workspace\WoldCard then you need to add a "\" so that you will know the Path and File name..does this make sense.. so change your code and add an ending backslash "\" to look like this D:\Workspace\WoldCard\export.csv

[user8128167]

Yes, I tried the AddWithValues method, but keep getting the same error as the one with parameters I had mentioned previously.

[MethodMan]

Roger lets handle one error at a time.. looks like the first one or 2 deal with the Connection String, and the other one deals with the FilePath