Skip to main content
Question

There is a syntactical error in the SQL statement using Database action


Forum|alt.badge.img+4

Scenario:

Open URL - https://www.nseindia.com/market-data/live-equity-market?

Extract the whole table data into Excel (xslx)

Code Snapshot:

Connection String: 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\RPA\Test\ExtractedDetails.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES";

SQL Query:

INSERT into [Sheet2$$] values ('$StockTableRow[0]$','$StockTableRow[1]$','$StockTableRow[2]$','$StockTableRow[3]$','$StockTableRow[4]$','$StockTableRow[5]$','$StockTableRow[6]$','$StockTableRow[7]$','$StockTableRow[8]$','$StockTableRow[9]$','$StockTableRow[10]$','$StockTableRow[11]$','$StockTableRow[12]$')

 

 

 

Error:

 

how to proceed further

10 replies

Padmakumar
Forum|alt.badge.img+13
  • Navigator | Tier 3
  • 785 replies
  • December 22, 2022

Hi @Prabu.B1,

 

I can see that you have mentioned the values here directly without specifying the respective column names in the excel file to which each data need to be inserted. So, I hope the purpose of it to populate the data to all the column since you should be having data for each of them and should be in the sequential order.

Have you checked there is any missing column value in that INSERT query which actually expecting?

 

Also, I can see that there is a ‘-’ sign at the starting of date. Is it like that only or is it a typo error?


Forum|alt.badge.img+4
  • Author
  • Cadet | Tier 2
  • 9 replies
  • December 22, 2022
Padmakumar wrote:

Hi @Prabu.B1,

 

I can see that you have mentioned the values here directly without specifying the respective column names in the excel file to which each data need to be inserted. So, are they in the sequential order or is there any missing column value?

https://www.w3schools.com/sql/sql_insert.asp

I have tried both the ways. I hardcoded the header column in the excel sheet still doesn’t work.

 

If possible can you try and share the results.


Padmakumar
Forum|alt.badge.img+13
  • Navigator | Tier 3
  • 785 replies
  • December 22, 2022
Prabu.B1 wrote:
Padmakumar wrote:

Hi @Prabu.B1,

 

I can see that you have mentioned the values here directly without specifying the respective column names in the excel file to which each data need to be inserted. So, are they in the sequential order or is there any missing column value?

https://www.w3schools.com/sql/sql_insert.asp

I have tried both the ways. I hardcoded the header column in the excel sheet still doesn’t work.

 

If possible can you try and share the results.

 

Since you have already used the Message box here to test the output, what I can give you as recommendation would be to use SQL Server Management Studio to build the queries and if there is no error, you can copy that and put it in the A360. 


Raul Jaimes
Forum|alt.badge.img+9
  • Navigator | Tier 3
  • 494 replies
  • December 22, 2022

Hi @Prabu.B1 

Try the follow approach if it is possible inserting sequencial values:

  1. INSERT into [Sheet2$$] values ('$StockTableRow[0]$')
  2. INSERT into [Sheet2$$] values ('$StockTableRow[0]$','$StockTableRow[1]$')
  3. INSERT into [Sheet2$$] values ('$StockTableRow[0]$','$StockTableRow[1]$','$StockTableRow[2]$')
  4. ..
  5. ...
  • INSERT into [Sheet2$$] values ('$StockTableRow[0]$','$StockTableRow[1]$','$StockTableRow[2]$','$StockTableRow[3]$','$StockTableRow[4]$','$StockTableRow[5]$','$StockTableRow[6]$','$StockTableRow[7]$','$StockTableRow[8]$','$StockTableRow[9]$','$StockTableRow[10]$','$StockTableRow[11]$','$StockTableRow[12]$')

And try to identify any wrong in the query or data.

HTH

Regards

 


Forum|alt.badge.img+4
  • Author
  • Cadet | Tier 2
  • 9 replies
  • December 23, 2022

I tried only one column data is working. from second column data it doesn’t work.


Padmakumar
Forum|alt.badge.img+13
  • Navigator | Tier 3
  • 785 replies
  • December 23, 2022
Prabu.B1 wrote:

I tried only one column data is working. from second column data it doesn’t work.

 

Have you tried replicating the query in SSMS? If there is any syntax error in the query, it will help you to identify.


Forum|alt.badge.img+2
Padmakumar wrote:
Prabu.B1 wrote:

I tried only one column data is working. from second column data it doesn’t work.

 

Have you tried replicating the query in SSMS? If there is any syntax error in the query, it will help you to identify.

 

I am always following this approach to ensure that all my queries are syntax-wise correct.


Raul Jaimes
Forum|alt.badge.img+9
  • Navigator | Tier 3
  • 494 replies
  • December 23, 2022
Prabu.B1 wrote:

I tried only one column data is working. from second column data it doesn’t work.

Ok, and if you skipping the second one y providing the third one, is it works ?

 

INSERT into [Sheet2$$] values ('$StockTableRow[0]$','$StockTableRow[2]$')

Also, verify , the data types of the Excel columns

 

 


rbkadiyam
Forum|alt.badge.img+17
  • Navigator | Tier 3
  • 582 replies
  • December 28, 2022

@Prabu.B1 datatype for all columns are string?

If not string for some columns, it may require type casting... 

Run the same statement i  sql developer will provide more clarity where its failing... 

I suspect order of values that are executing vs column names / datatype / length not matching...


Forum|alt.badge.img

I Have the same problem. But it is working fine for few line item after like 550 line Bot is showing There is a syntactical error in the SQL statement using Database action.

Timeout also I have given as 9999. But still the same error

How to solve this in Bot.

 

Thanks!!


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings