Excel as DB - UPDATE syntactical error

​@samlop Using Excel as a database is challenging. You're having to work through three layers of abstraction just to make it work. One of the side effects is that some of your SQL commands won't have the same syntax as before. This is an Excel as a DB problem, not an Automation Anywhere problem. 

You will have to experiment to figure out the syntax difference. 

Your other option is to import the Excel file into an Access database. Then, you can truly use the contents of the Excel file as a database without the abstraction and syntax issues. 

Hi ​@samlop ,

Could you please provide your UPDATE query here?

Hi ​@samlop ,

If you are having excel with No Column header, then used below connection string to connect excel as Database.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<Excel_Path>';Extended Properties="Excel 12.0 Xml;HDR=NO";

Example:

If you use the select * query without WHERE clause, it shows like this:

Then if you want to Update Nithish’s Phone number(F2) to “9999999999” 

Update [Sheet1$$] Set F2="9999999999" Where F1="Nithish"

or want to Update Rajani (f1) Name as “RAJANI MR”

Update [Sheet1$$] Set F1="RAJANI MR" Where F1="Rajani"

Select F1, F2 from [Sheet1$$]

This tells, how to update excel using SQL in AA  for non header records.

If this also not solving the problem, then please revert here with screenshot or data of excel template and what action you need to perform.

Thanks!

Try to paste the Update query in SQL server or whatever database you are using to remove the syntactical error (they may highlight in a red) and the use the query.