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.

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, Jan 21 '13 at 19:26
is this the path `D:\Workspace\WoldCard`..? if so should be `D:\Workspace\WoldCard\export.csv` please clarify Roder.. — MethodMan, Jan 21 '13 at 19:31
The path is D:\Workspace\WoldCard export.csv. Please help me understand, why should it be D:\Workspace\WoldCard\export.csv? — user8128167, Jan 21 '13 at 19:36
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` — MethodMan, Jan 21 '13 at 19:39

score 8 · Accepted Answer · edited May 23 '17 at 11:52

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.

MethodMan · Answer 2 · 2013-01-21T19:30:28.127

1

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

edited Jan 21 '13 at 19:30

answered Jan 21 '13 at 19:19

MethodMan

18,625
6
34
52

Yes, I tried the AddWithValues method, but keep getting the same error as the one with parameters I had mentioned previously. – user8128167 Jan 21 '13 at 19:38
1

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 – MethodMan Jan 21 '13 at 19:41

score 1 · Answer 3 · answered Jan 27 '16 at 08:19

1

You have to use [] brackets. File names or table names with spaces, dashes (-), reserved words, etc... are not ok, to use them in square brackets.

More info: See: https://msdn.microsoft.com/en-us/library/ms175874.aspx

answered Jan 27 '16 at 08:19

juFo

17,849
10
105
142

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

3 Answers3