FireDAC Query on Excel file: 'Syntax error in FROM clause'

Hi guys,

Is this a bug or I have missed something?

I used FireDAC TFDConnection, TFDQuery, TDataSource and TDBGrid to create a small interface to try out reading an excel file. The param is set as such:

Database=C:\examples\xxxx.xls
ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
DriverID=ODBC

The query string in the FDQuery is simply: SELECT * FROM `Client$`, while 'Client' is a worksheet in my excel file.

Now, I found a stranger problem: only if I set the *TFDConnection Connected=true* and the *TFDQuery Active=true* *at the design stage*, the query *will implement correctly*, and even the program changes the Query string to select from another worksheet, it still works at run time.

if I set *either or both DB connected = false or Query active = false at the design stage*, and try to change them to true at the run time, I will get a SQL error message like this: *[FireDAC][Phys][ODBC][Microsoft][ODBC Excel Driver] Syntax error in FROM clause*.

In the first working situation above, I can still get the same error *whenever I change the DB connected to false and change it back to true again* at the run time stage. Doesn't matter I keep the DB param as same or change the param to a different excel file. It just pop up the Syntax error in FROM clause.

anybody has a workaround?

Thanks.
0
Yonghui
6/17/2014 6:48:49 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

1 Replies
1355 Views

Similar Articles

[PageSpeed] 58

Table names using the Excel ODBC driver are derived from Named Ranges in 
the worksheet, not worksheet names. This is how the driver works and has 
nothing to do with FireDAC. So, create named ranges in your worksheets 
and use them for your SQL and all should be well.

A simple way to get a valid list of table names to create a TFDTable and 
look at the tablename property.

Yonghui Song wrote:
> Hi guys,
>
> Is this a bug or I have missed something?
>
> I used FireDAC TFDConnection, TFDQuery, TDataSource and TDBGrid to create a small interface to try out reading an excel file. The param is set as such:
>
> Database=C:\examples\xxxx.xls
> ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
> DriverID=ODBC
>
> The query string in the FDQuery is simply: SELECT * FROM `Client$`, while 'Client' is a worksheet in my excel file.
>
> Now, I found a stranger problem: only if I set the *TFDConnection Connected=true* and the *TFDQuery Active=true* *at the design stage*, the query *will implement correctly*, and even the program changes the Query string to select from another worksheet, it still works at run time.
>
> if I set *either or both DB connected = false or Query active = false at the design stage*, and try to change them to true at the run time, I will get a SQL error message like this: *[FireDAC][Phys][ODBC][Microsoft][ODBC Excel Driver] Syntax error in FROM clause*.
>
> In the first working situation above, I can still get the same error *whenever I change the DB connected to false and change it back to true again* at the run time stage. Doesn't matter I keep the DB param as same or change the param to a different excel file. It just pop up the Syntax error in FROM clause.
>
> anybody has a workaround?
>
> Thanks.
>
0
quinn
6/17/2014 9:54:17 PM
Reply:

Similar Artilces:

Syntax error (missing operator) in query expression '[Nom support]=' béton 'neuf, brut, lisse'''.
Hello, I have a Table SupportSysteme with a field Code Support and I have this query in my code : string mSQL = "SELECT * from SupportSysteme where [Code Support]=' "+support+" '"; When I run I, have the error :Syntax error (missing operator) in query expression '[Nom support]=' béton 'neuf, brut, lisse'''. where the value of support is béton 'neuf, brut, lisse'(the connection read this value on the field Code Support ) My query does not accept parameters with quotes('), How can I resolve this problem. Here is the integral function: void Sup...

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

'IN' Clause or 'OR'
Hello, [1]: select * from TABLEA where COL1 IN('value1','value2''valu3'....) [2]:select * from TABLEA where COL1 = 'value1' OR COL1= 'value2' OR COL1='valu3'. TABLEA is a huge table and it has non-clustered index on COL1. Among the above 2 queries, which query will give me the better performance or fast response and WHY? What is the difference between 'IN' and 'OR' clauses as for as Sybase Optimization is concerned. Which is the better one to be used on huge tables. Thanks. Mac An IN list is treated ...

Syntax error (missing operator) in query expression 'Tbl_UseofOS.OS WHERE ((Tbl_Category.Categoryname)='Database')'.
Dear All,  I am already looking at this expression for a couple of days now to find out what I missed here ! But i am getting fed up with it. Can someone whit a clear head help me out with this ! please. I am desperate.  Many thanks Nancy  sub getdata (Src As Object, E As EventArgs) dim strsql as stringdim myconnection as oledbconnectiondim mycommand as oledbcommand myconnection=new oledbconnection(ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_club"))strsql="SELECT Tbl_OSS.OSSID, Tbl_OSS.OSSName, Tbl_OSS.Description, Tbl_Producer.ProducerNam...

sp_addumpdevice 'file' and 'disk' Syntax
Tape Syntax: sp_addumpdevice "tape", "Tape1", "/dev/nrmt0", 8000. I understand the 8000 reference the size of the tabe here. Disk Syntax sp_addumpdevice "disk", "CustomerDB_dump", "/dbdump/CustomerDB/CustomerDB_dump", 2 sp_addumpdevice "disk", "CustomerDB_tran", "/dbdump/CustomerDB/CustomerDB_tran", 2 The Sybase SQL Server 11 Unleashed book does not specify the purpose of the 2 in the disk syntax. What does the 2 reference in a disk dump? corey wrote: > Tape Syntax: > ...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

'Mozilla Composer' on Mac: Won't open html files
Hi. Last night I tried to help my friend install Sea Monkey on his Apple Mac so he can use Mozilla Composer to create and edit html files. We appeared to successfully install Sea Monkey for Mac OS, but when we tried to open a (very, very) simple html file that I had just created in Mozilla Composer on my PC it would not open it. Every time we tried we got a dialog box saying something like: "This is a binary file. Do you want to: (a) Open it; or (b) Save it." -When we clicked on 'Open' we got the usual list of programs, but Mozilla Composer was not one of them. Actual...

syntax error : missing ';' before identifier 'DllMain'
I created and XPCOM module using JavaScript. I created a C++ executable that loads the xpcom and calls the methods in the XPCOM component that I created. I want to do the same thing using a dll instead of an executable. As soon as started making the changes by adding bool APIENTRY DllMain(), Mozilla build environment gave me the error syntax error : missing ';' before identifier 'DllMain' Why is it throwing this error? Without the DllMain can other non- mozilla executable load this dll and use it? Other errors that I saw was that HANDLE, BSTR as not defined. ...

Select from someTable Where (someField in ('item1', 'item2', 'item3')) Syntax, Parameters and DataTableAdapter Query
Hi All, I'd like to use the above syntax within a tableAdapter query, but it only works if I pass one value in to the parameter, which I guess makes sense now that I'm writing this out (one value, one parameter). But is there a way to pass some variable number of strings to be used in a ... Where (SomeField in ('Item1', 'Item2', 'Item3')... clause? Thanks for any help. David    Not really. You can pass multiple values, but then you may need to parse this string. Search by fn_split or ufn_alines here if you want to find threads d...

execute (@query) syntax error at '(' ?
I'm my company's lead SQL developer -- I'm not a novice to SQL, just to ASA. I've used the exec (@query) syntax dozens of times on MSSQL. I have RTFM online and off, and I'm about to pull my hair out over this one. The docs say the feature works, but I can't get a useful response. Using ASA 6.0.3, I've built a long SQL query in a @variable (I'm an MSSQL junkie, so I use that syntax) as part of a stored procedure. I can verify that the query in the variable is valid SQL, because I can execute that command from isql without errors. The problem is, I...

server error in '/' Application Incorrect syntax near ','.
This code used to work now I get an error(error follows code)  can anyone tell me where I went wrong?[vbcode]i = 0            For i = 0 To 7                If requests(i) <> "" Then                    request = requests(i)                    Dim sql1 As String =...

ERROR [42S21] Unknown column 'Status' in 'where clause'
Hi,I'm getting the above error from this code: CmdStr = "Select Count(*) From `Articles` Where Category = '" + sCategory.Replace("\", "\\").Replace("'", "\'") + "' And Status = '0'"                DBSelect = New System.Data.Odbc.OdbcCommand(CmdStr, DBConn)                DBRead = DBSelect.ExecuteReader Any help would be greatly appreciatedThanks  Your query string is incorrect can you debug the code and get CmdStr out. so i can ha...

error in ''%'' operator
hi... friends    i got this error (Syntax error: Missing operand after ''%'' operator)below statement. dtBtnScr.DefaultView.RowFilter = "LOCATION_CITY'" + txtCity.Text.Trim() + "%'AND LOCATION_STATE '" + ddlState.SelectedValue + "%'AND UNIT_ID'" + txtUnitCode + "%'AND UNIT_NAME '" + txtUnitName.Text.Trim() + "%'";   thnax friends It thinks you are trying to use the modulo operator: %. Look at the RowFilter string after you build it ...you should see the prob...

REPLACE(CONVERT(VARCHAR(10),@returnDate,111), '/', '-') AS [YYYY-MM-DD] ,facing error --Incorect syntax near 'AS'
Create Function [driveuser].[ConvertVarcharToDateYearMonthDateFormat] (@date varchar(12) )Returns datetime As BeginDeclare @returnDate datetime Declare @returnDateVarchar varchar(12) Select @returnDate=Convert(datetime,@date) Select @returnDateVarchar= REPLACE(CONVERT(VARCHAR(10),@returnDate,111), '/', '-') AS [YYYY-MM-DD] Select @returnDate=Convert(Datetime,@returnDateVarchar) return @returnDate End   I am facing the error is  -----  Incorrect syantx near the keyword 'AS' Incorrect syntax near the keyword 'AS'. Hi, try thi ...

Web resources about - FireDAC Query on Excel file: 'Syntax error in FROM clause' - embarcadero.delphi.firedac

Resources last updated: 12/31/2015 8:11:02 PM