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.

  • 10 February 2022
  • 15 replies
  • 30 views

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

Hi @Akshaykumar More​ 

 

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

Userlevel 3
Badge +7

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.

 

 

 

Userlevel 5
Badge +9

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>

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

Userlevel 3
Badge +7

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

Hi @Ashwin A.K​ 

 

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

Userlevel 3
Badge +7

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

Userlevel 5
Badge +9

Hi @Poonam Pandire​ ,

 

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

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

 

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

Userlevel 5
Badge +9

Welcome @Poonam Pandire​ 

Userlevel 3
Badge +7

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

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.

Userlevel 3
Badge +7

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

Reply