Skip to main content
Question

How to insert data from one excel to another? I am using query "insert into [Tracking$$] ([Customer ID Number],[Company code],[Portal]) values ('$strCustomerID$','$strCompanyCode$','$strPortal$')". it is not working and also not throwing any error.


How to insert data from one excel to another? I am using query "insert into [Tracking$$] ([Customer ID Number],[Company code],[Portal]) values ('$strCustomerID$','$strCompanyCode$','$strPortal$')". it is not working and also not throwing any error.

15 replies

  • Author
  • Cadet | Tier 2
  • 9 replies
  • February 10, 2022

Hi @Akshaykumar More​ 

 

I tried suggested query, it is throwing error as " There is a syntactical error in the SQL statement."


Ashwin A.K
Forum|alt.badge.img+8
  • Navigator | Tier 3
  • 2445 replies
  • February 10, 2022

Hi @Poonam Pandire​ ,

 

When you stated that you are trying to adding data from one Excel to another, are you trying to connect to both files as it were a database and then try to pool data from one file into another?

 

Could you please share a screenshot of the workflow so that we may be in a better position to assist you?

 

Kind Regards,

Ashwin A.K


Hi @Poonam Pandire​ 

 

Could you please verify the variable ('$strCustomerID$','$strCompanyCode$','$strPortal$') you are passing in that any invalid char/symbol is available?

 

Or add try catch to display the record which is having problem

 

Add insert command under Try block and same insert query add under message box but in catch block. try in below way.

 

image 

if possible try the check Database connect command using below URL.

 

https://developer.automationanywhere.com/blog/quicktip-connect-to-an-excel-spreadsheet-as-a-database/p>


Hi @Poonam Pandire​ 

 

Could you please try in below format

 

INSERT INTO [Tracking$$] (`Customer ID Number`, `Company code`, `Portal` ) VALUES ('$strCustomerID$','$strCompanyCode$','$strPortal$')

 

and please make sure you have used Insert/Update/Delete command from database package.

 

 

 


Forum|alt.badge.img+19
  • Most Valuable Pathfinder
  • 2694 replies
  • February 10, 2022

HI @Poonam Pandire​ ,

 

Could you remove the square brackets for column names and try?

 

Checkout below tutorial for hands-on

 

https://www.youtube.com/watch?v=uYEKO_y4NNk/p>


  • Author
  • Cadet | Tier 2
  • 9 replies
  • February 10, 2022

Hi @Ashwin A.K​ 

 

Yes, HDR= Yes is also provided in both connection string. Values are fetched as expected but failed to insert it into excel.

image


Ashwin A.K
Forum|alt.badge.img+8
  • Navigator | Tier 3
  • 2445 replies
  • February 10, 2022

Hi @Poonam Pandire​ ,

 

Could you verify if HDR=Yes is provided in the connection string and try passing this query into Line 70 as a test?

 

INSERT INTO [Tracking$$] ([Customer ID Number],[Company code],[Portal]) 

VALUES ('$strCustomerID$','$strCompanyCode$','$strPortal$');

 

Also, add a message box just before it to see the values that are going into the Excel file.

 

Kind Regards,

Ashwin A.K


  • Author
  • Cadet | Tier 2
  • 9 replies
  • February 10, 2022

Hi @Ashwin A.K​ 

 

Yes I connected both excel with different session name. Please see below screenshot.image


Ashwin A.K
Forum|alt.badge.img+8
  • Navigator | Tier 3
  • 2445 replies
  • February 10, 2022

Hi @Poonam Pandire​ ,

 

I've tried a similar operation from my end and it works fine, could you maybe have a look at the headers in the second Excel Spreadsheet and check if there are any discrepancies with the spellings?

 

If you could provide us with a sample Excel Spreadsheets for us to test out, then we will be in a better position to assist you.

 

Kind Regards,

Ashwin A.K


Forum|alt.badge.img+19
  • Most Valuable Pathfinder
  • 2694 replies
  • February 11, 2022

Hi @Poonam Pandire​ ,

 

Adding to the above, could you check in the excel headers contains Spaces in the end?


  • Author
  • Cadet | Tier 2
  • 9 replies
  • February 11, 2022

Hi @Ashwin A.K​ and @ChanduMohammad S​ ,

 

Yes, It is working now. Thank you so much for your help.


Forum|alt.badge.img+19
  • Most Valuable Pathfinder
  • 2694 replies
  • February 11, 2022

Welcome @Poonam Pandire​ 


Ashwin A.K
Forum|alt.badge.img+8
  • Navigator | Tier 3
  • 2445 replies
  • February 11, 2022

Hi @Poonam Pandire​ ,

 

That's great!

Could you please explain how you resolved it?

It will benefit the community and help anyone facing similar issues.

 

Kind Regards,

Ashwin A.K


  • Author
  • Cadet | Tier 2
  • 9 replies
  • February 12, 2022

Hi @Ashwin A.K​ ,

 

I deleted old file and created new excel and it worked. Don't know why query was not working on old file.


Ashwin A.K
Forum|alt.badge.img+8
  • Navigator | Tier 3
  • 2445 replies
  • February 14, 2022

Then the file might have been corrupted.

Anyway, thank you for sharing the solution here with us, we appreciate it.

 

Kind Regards,

Ashwin A.K


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