SQL vaicājumu izpilde Excel failos

Lai gan Excel darbības var apstrādāt lielāko daļu Excel automatizācijas scenāriju, SQL vaicājumi var efektīvāk izgūt ievērojamus Excel datu apjomus un manipulēt ar tiem.

Pieņemsim, ka plūsmai ir jāmodificē tikai tie Excel reģistri, kuros ir noteikta vērtība. Lai sasniegtu šo funkcionalitāti bez SQL vaicājumiem, ir nepieciešamas cilpas, nosacījumi un vairākas Excel darbības.

Varat arī ieviest šo funkcionalitāti ar SQL vaicājumiem, izmantojot tikai divas darbības: atvērt SQL savienojumu un izpildīt SQL priekšrakstus.

SQL savienojuma ar Excel failu atvēršana

Pirms SQL vaicājuma izpildes jums ir jāatver savienojums ar Excel failu, kuram vēlaties piekļūt.

Lai izveidotu savienojumu, izveidojiet jaunu mainīgo ar nosaukumu %Excel_File_Path% un inicializējiet to ar Excel faila ceļu. Pēc izvēles varat izlaist šo darbību un vēlāk plūsmā izmantot faila kodēto ceļu.

Tagad izvietojiet darbību Atvērt SQL savienojumu un aizpildiet šādu savienojuma virkni tās rekvizītos.

Provider=Microsoft.ACE.OLEDB.12.0; datu avots=%Excel_File_Path%; Paplašinātie rekvizīti="Excel 12.0 xml;HDR=YES";

Piezīmes

Lai veiksmīgi izmantotu piedāvāto savienojuma virkni, jums ir jālejupielādē un jāinstalē Microsoft Access Database Engine 2010 Redistributable.

SQL savienojuma atvēršana ar paroli aizsargātam Excel failam

Atšķirīga pieeja ir nepieciešama scenārijos, kuros palaižat SQL vaicājumus ar paroli aizsargātos Excel failos. Darbība Atvērt SQL savienojumu nevar izveidot savienojumu ar Excel failiem, kas ir aizsargāti ar paroli, tāpēc aizsardzība ir jānoņem.

Lai to panāktu, palaidiet Excel failu, izmantojot darbību Palaist Excel . Fails ir aizsargāts ar paroli, tāpēc laukā Parole ievadiet atbilstošo paroli .

Pēc tam izvietojiet atbilstošās UI automatizācijas darbības un naviģējiet uz Faila>informācijas>aizsardzība darbgrāmatas>šifrēšanā ar paroli. Papildinformāciju par UI automatizāciju un to, kā izmantot attiecīgās darbības, varat atrast rakstā Darbvirsmas lietojumprogrammu automatizēšana.

Pēc tam, kad esat izvēlējies Šifrēt ar paroli, uznirstošajā dialoglodziņā aizpildiet tukšu virkni, izmantojot loga darbībā Aizpildīt teksta lauku. Lai aizpildītu tukšu virkni, izmantojiet šādu izteiksmi: %""%.

Lai dialoglodziņā nospiestu pogu Labi un lietotu izmaiņas, izvietojiet pogu Nospiest loga darbībā.

Visbeidzot, izvietojiet darbību Aizvērt Excel, lai saglabātu neaizsargāto darbgrāmatu kā jaunu Excel failu.

Pēc faila saglabāšanas izpildiet norādījumus sadaļā SQL savienojuma atvēršana ar Excel failu , lai atvērtu savienojumu ar to.

Kad manipulācijas ar Excel failu ir pabeigtas, izmantojiet darbību Dzēst failus , lai izdzēstu Excel faila neaizsargāto kopiju.

Excel izklājlapas satura lasīšana

Lai gan, veicot darbību Lasīt no programmas Excel, var tikt nolasīts Excel darblapas saturs, var paiet ievērojams laiks, līdz cilpas atkārto izgūtos datus.

Efektīvāks veids, kā izgūt noteiktas vērtības no izklājlapām, ir Excel failus uzskatīt par datu bāzēm un izpildīt tajos SQL vaicājumus. Šī pieeja ir ātrāka un palielina plūsmas veiktspēju.

Lai izgūtu visu izklājlapas saturu, darbībā Execute SQL priekšraksts varat izmantot tālāk norādīto SQL vaicājumu .

SELECT * FROM [SHEET$]

Piezīmes

Lai plūsmās lietotu šo SQL vaicājumu, aizstājiet vietturi SHEET ar tās izklājlapas nosaukumu, kurai vēlaties piekļūt.

Lai izgūtu rindas, kurās ir noteikta vērtība noteiktā kolonnā, izmantojiet šādu SQL vaicājumu:

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

Piezīmes

Lai plūsmās lietotu šo SQL vaicājumu, aizstājiet:

• LAPA ar tās izklājlapas nosaukumu, kurai vēlaties piekļūt.
• KOLONNAS NOSAUKUMS ar kolonnu, kurā ir meklējamā vērtība. Excel darblapas pirmās rindas kolonnas tiek identificētas kā tabulas kolonnu nosaukumi.
• VALUE ar vērtību, kuru vēlaties atrast.

Datu dzēšana no Excel rindas

Lai gan programma Excel neatbalsta SQL dzēšanas vaicājumu, varat izmantot vaicājumu UPDATE , lai visām konkrētas rindas šūnām iestatītu vērtību Null.

Precīzāk, varat izmantot šādu SQL vaicājumu:

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

Izstrādājot plūsmu, vietturis SHEET ir jāaizstāj ar tās izklājlapas nosaukumu, kurai vēlaties piekļūt.

Vietturi COLUMN1 un COLUMN2 apzīmē apstrādājamo kolonnu nosaukumus. Šajā piemērā ir divas kolonnas, bet reālā scenārijā kolonnu skaits var atšķirties. Excel darblapas pirmās rindas kolonnas tiek identificētas kā tabulas kolonnu nosaukumi.

Vaicājuma daļa[COLUMN1]='VALUE' definē rindu, kuru vēlaties atjaunināt. Plūsmā izmantojiet kolonnas nosaukumu un vērtību, pamatojoties uz kuru kombināciju rindas tiek aprakstītas unikāli.

Excel datu izgūšana, izņemot konkrētu rindu

Dažos scenārijos, iespējams, būs jāizgūst viss Excel izklājlapas saturs, izņemot konkrētu rindu.

Ērts veids, kā to panākt, ir iestatīt nevēlamās rindas vērtības uz nulli un pēc tam izgūt visas vērtības, izņemot null.

Lai izklājlapā mainītu konkrētas rindas vērtības, varat izmantot vaicājumu UPDATE SQL, kā norādīts sadaļā Datu dzēšana no Excel rindas:

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

Pēc tam palaidiet šo SQL vaicājumu, lai izgūtu visas izklājlapas rindas, kurās nav vērtību Null:

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

Vietturi COLUMN1 un COLUMN2 apzīmē apstrādājamo kolonnu nosaukumus. Šajā piemērā ir divas kolonnas, bet reālā tabulā kolonnu skaits var atšķirties. Visas Excel darblapas pirmās rindas kolonnas tiek identificētas kā tabulas kolonnu nosaukumi.