Изпълняване на SQL заявки за файлове на Excel

Въпреки че действията на Excel могат да се справят с повечето сценарии за автоматизация на Excel, SQL заявките могат да извличат и манипулират значителни количества данни на Excel по-ефективно.

Да предположим, че даден поток трябва да модифицира само регистрите на Excel, които съдържат определена стойност. За да постигнете тази функционалност без SQL заявки, ви трябват цикли, условни и множество действия на Excel.

Като алтернатива можете да реализирате тази функционалност със SQL заявки, като използвате само две действия, Отваряне на SQL връзка и Изпълнение на SQL команди.

Отваряне на SQL връзка към файл на Excel

Преди да изпълните SQL заявка, трябва да отворите връзка с файла на Excel, до който искате да получите достъп.

За да установите връзката, създайте нова променлива с име %Excel_File_Path% и я инициализирайте с пътя до файла на Excel. По желание можете да пропуснете тази стъпка и да използвате твърдо кодирания път на файла по-късно в потока.

Сега разположете действието Open SQL връзка и попълнете следния низ за връзка в неговите свойства.

Доставчик=Microsoft.ACE.OLEDB.12.0; източник на данни=%Excel_File_Path%; Разширени свойства="Excel 12.0 Xml;HDR=YES";

Бележка

За да използвате успешно представения низ за връзка, трябва да изтеглите и инсталирате Microsoft Access Database Engine 2010 Redistributable.

Отваряне на SQL връзка към защитен с парола файл на Excel

Изисква се различен подход при сценарии, при които изпълнявате SQL заявки в защитени с парола файлове на Excel. Действието Open SQL връзка не може да се свърже с защитени с парола файлове на Excel, така че трябва да премахнете защитата.

За да постигнете това, стартирайте файла на Excel с помощта на действието Стартиране на Excel . Файлът е защитен с парола, затова въведете съответната парола в полето Парола .

След това разположете подходящите действия за автоматизация на потребителския интерфейс и навигирайте до File>Info>Protect Workbook>Encrypt with Password. Можете да намерите повече информация за автоматизацията на потребителския интерфейс и как да използвате съответните действия в Автоматизиране на настолните приложения.

След като изберете Шифроване с парола, попълнете празен низ в изскачащия диалогов прозорец, като използвате полето Попълване на текст в действието на прозореца . За да попълните празен низ, използвайте следния израз: %""%.

За да натиснете бутона OK в диалоговия прозорец и да приложите промените, разположете бутона Натиснете в действие на прозореца .

И накрая, разположете действието Close Excel , за да запишете незащитената работна книга като нов файл на Excel.

След като запишете файла, следвайте инструкциите в Отваряне на SQL връзка към файл на Excel, за да отворите връзка към него.

Когато манипулирането на файла на Excel завърши, използвайте действието Изтриване на файлове , за да изтриете незащитеното копие на файла на Excel.

Четене на съдържанието на електронна таблица на Excel

Въпреки че действието Четене от работен лист на Excel може да чете съдържанието на работен лист на Excel, циклите могат да отнемат значително време, за да се повторят извлечените данни.

По-ефективен начин за извличане на конкретни стойности от електронни таблици е да третирате файловете на Excel като бази данни и да изпълнявате SQL заявки върху тях. Този подход е по-бърз и увеличава ефективността на потока.

За да извлечете цялото съдържание на електронна таблица, можете да използвате следната SQL заявка в действието Изпълнение на SQL команда .

SELECT * FROM [SHEET$]

Бележка

За да приложите тази SQL заявка във вашите потоци, заменете контейнера SHEET с името на електронната таблица, до която искате да получите достъп.

За да извлечете редовете, които съдържат определена стойност в определена колона, използвайте следната SQL заявка:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Бележка

За да приложите тази SQL заявка във вашите потоци, заместете:

• ЛИСТ с името на електронната таблица, до която искате да получите достъп.
• ИМЕ НА КОЛОНА с колоната, съдържаща стойността, която искате да намерите. Колоните в първия ред на работния лист на Excel се идентифицират като имена на колони на таблицата.
• VALUE със стойността, която искате да намерите.

Изтриване на данни от ред на Excel

Въпреки че Excel не поддържа SQL заявката DELETE, можете да използвате заявката UPDATE , за да зададете всички клетки на конкретен ред на Null.

По-точно, можете да използвате следната SQL заявка:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Докато развивате потока си, трябва да замените контейнера SHEET с името на електронната таблица, до която искате да получите достъп.

Контейнерите COLUMN1 и COLUMN2 представляват имената на колоните, които трябва да се обработят. Този пример има две колони, но в реален сценарий броят на колоните може да се различава. Колоните в първия ред на работния лист на Excel се идентифицират като имена на колони на таблицата.

Частта [COLUMN1]='VALUE' на заявката определя реда, който искате да актуализирате. Във вашия поток използвайте името на колоната и стойността, въз основа на която комбинацията описва редовете уникално.

Извличане на данни на Excel с изключение на конкретен ред

В някои сценарии може да се наложи да извлечете цялото съдържание на електронна таблица на Excel, с изключение на конкретен ред.

Удобен начин да постигнете това е да зададете стойностите на нежелания ред на null и след това да извлечете всички стойности, с изключение на нулевите.

За да промените стойностите на конкретен ред в електронната таблица, можете да използвате SQL заявка UPDATE , както е представено в Изтриване на данни от ред на Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

След това изпълнете следната SQL заявка, за да извлечете всички редове на електронната таблица, които не съдържат празни стойности:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Контейнерите за COLUMN1 и COLUMN2 представляват имената на колоните, които трябва да се обработят. Този пример има две колони, но в реална таблица броят на колоните може да се различава. Всички колони в първия ред на работния лист на Excel са идентифицирани като имена на колони на таблицата.