Skip to main content

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 oSheet2$$] values ('$StockTableRowo0]$','$StockTableRowo1]$','$StockTableRowo2]$','$StockTableRowo3]$','$StockTableRowo4]$','$StockTableRowo5]$','$StockTableRowo6]$','$StockTableRowo7]$','$StockTableRowo8]$','$StockTableRowo9]$','$StockTableRowo10]$','$StockTableRowo11]$','$StockTableRowo12]$')

 

 

 

Error:

 

how to proceed further

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?

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.

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. 

Hi @Prabu.B1 

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

  1. INSERT into Sheet2$$] values ('$StockTableRoww0]$')
  2. INSERT into Sheet2$$] values ('$StockTableRoww0]$','$StockTableRoww1]$')
  3. INSERT into Sheet2$$] values ('$StockTableRoww0]$','$StockTableRoww1]$','$StockTableRoww2]$')
  4. ..
  5. ...
  • INSERT into Sheet2$$] values ('$StockTableRoww0]$','$StockTableRoww1]$','$StockTableRoww2]$','$StockTableRoww3]$','$StockTableRoww4]$','$StockTableRoww5]$','$StockTableRoww6]$','$StockTableRoww7]$','$StockTableRoww8]$','$StockTableRoww9]$','$StockTableRoww10]$','$StockTableRoww11]$','$StockTableRoww12]$')

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

HTH

Regards

 

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

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 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.

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 tSheet2$$] values ('$StockTableRowR0]$','$StockTableRowR2]$')

Also, verify , the data types of the Excel columns

 

 

@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...

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!!

Reply


Terms & ConditionsCookie settings