Skip to main content
Question

Insert query to insert only "unique" value for Excel as an database

  • September 23, 2024
  • 3 replies
  • 148 views

Forum|alt.badge.img+1

Hi All

I am working with excel as an database functionality of AA360, came accross a case where I have to insert data into excel using DB query, if only the value is not existing in the sheet to inserted.

 

Here are the queries I have tried

  1. Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values (SELECT ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') where not exists (Select [Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?] from [STEP2$$] where [Vorhaben] = '$RecSqlDataset[0]$'))
  2. Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') where (Select [Vorhaben] from [STEP2$$]) not in '$RecSqlDataset[0]$
  3. Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$') Where (Select Count([Vorhaben]) from [STEP2$$] where [Vorhaben]='xyz') = 0

I am able to run the 1st part of query successfully “Insert into [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?]) values (SELECT ('$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$')”

Here Vorhaben is the column in both the input and output file.

3 replies

Chandrasekhar 7496
Forum|alt.badge.img+5

Try this:

INSERT INTO [STEP2$$] ([Vorhaben],[verteilt an],[E K Z],[Alle Stellungnahmen da?])
SELECT '$RecSqlDataset[0]$', '$RecSqlDataset[3]$', '$RecSqlDataset[8]$', '$RecSqlDataset[17]$'
WHERE NOT EXISTS (
    SELECT 1 FROM [STEP2$$]
    WHERE [Vorhaben] = '$RecSqlDataset[0]'
);
 


Forum|alt.badge.img+1
  • Author
  • Cadet | Tier 2
  • 1 reply
  • September 24, 2024

naah it doesn’t work. Gives syntatical error.


Aaron.Gleason
Automation Anywhere Team
Forum|alt.badge.img+10
  • Automation Anywhere Team
  • 503 replies
  • September 24, 2024

There’s a problem here, and it has to do with SQL query syntax and how Excel as a database works. Not all standard SQL queries work with Excel as a database. It’s a pretty bad idea to even use Excel as a database because it’s not a database.

Ref: https://absentdata.com/excel/stop-using-excel-as-a-database-heres-why/

Consider using Microsoft Access and an MDB or ACCDB file as your source. Still not perfect and won’t support 100% of SQL ‘92 standard queries, but it will do much better than Excel as a database.

@Chandrasekhar 7496 ‘s query should work with a standard database engine.


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