{"Syntax error in FROM clause."} when using text based datasources
This is also one of the issues that I have seen a few times.
What happens here is that customer uses *.csv or *.xls or any text file as a data source and connects to it using .Net and OleDb.
The error reported from the framework when issuing a SELECT is:
{"Syntax error in FROM clause."}
The most common reason for this is that the *.csv or *.xls file name contains a space. No space and all is well.
The reason for this is that the file name is the table name, and as such, no spaces are allowed and should be enclosed in square brackets ie. [table name].
Let’s show by example. Create a *.csv file in C:\Temp with the following content.
CID;FNAME;LNAME
1;John;Smith
2;Peter;Johnson
3;Steve;Lloyd
Then save it as Test.csv and then as Test Test.csv.
Create a new .Net console application and just edit the Program.cs file to look like this.
using System;
using System.Text;
using System.Data.OleDb;
using System.Data;
class Program
{
static void Main(string[] args)
{
// With space
string fName = @"C:\Temp\Test Test.csv";
// Without space
//string fName = @"C:\Temp\Test.csv";
// Get the dir out of the full path.
string path = fName.Substring(0, fName.LastIndexOf(@"\"));
string cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='text;HDR=Yes;FMT=CSVDelimited'";
OleDbConnection oc = new OleDbConnection(cString);
oc.Open();
// Get the file out of the full path.
int pos = fName.LastIndexOf(@"\") + 1;
string tablename = fName.Substring(pos, fName.Length - pos);
// This will work with spaces and without spaces in filename,
// this is because the tablename will be now enclosed in square brackets.
OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + tablename + "]", oc);
// Running the select will result in:
// {"Syntax error in FROM clause."}
//OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + tablename, oc);
OleDbDataAdapter oda = new OleDbDataAdapter(ocmd);
DataSet ds = new DataSet();
oda.Fill(ds);
oc.Close();
Console.WriteLine("Number of rows in {0}: {1}", tablename, ds.Tables[0].Rows.Count);
}
}
Now, obviously there are no checks here for existence of files etc. But it demonstrates how to get around the space issue.
A common reason for this would be when, for example, the users are allowed to browse the file system for
the file that is to be used in the select and when there is no check for spaces in the filename.
About Identifiers for, for example, tablenames.
"SQL Server 2005 Books Online (September 2007) Identifiers"