Импортиране на данни от папка с множество файлове (Power Query)

Използвайте Power Query, за да комбинирате множество файлове с една и съща схема, съхранена в една папка, в една таблица. Например всеки месец, който искате да комбинирате работни книги за бюджета от няколко отдела, където колоните са еднакви, но броят на редовете и стойностите се различава във всяка работна книга. След като го настроите, можете да приложите допълнителни трансформации, както при всеки един импортиран източник на данни, и след това да обновите данните , за да виждате резултатите за всеки месец.

Концептуален общ преглед на комбинирането на файлове с папки

Забележка Тази тема показва как да комбинирате файлове от папка. Можете също да комбинирате файлове, съхранени в SharePoint, blob хранилище на Azure и Azure Data Lake Storage. Процесът е подобен.

По-нови версии Office 2013

Придържайте се към простите:

Уверете се, че всички файлове, които искате да комбинирате, се съдържат в специална папка без излишни файлове. В противен случай всички файлове в папката и всички подпапки, които изберете, се включват в данните, които трябва да се комбинират.
Всеки файл трябва да има една и съща схема с последователни заглавки на колони, типове данни и брой колони. Колоните не трябва да са в същия ред като съпоставянето се извършва с имената на колоните.
Ако е възможно, избягвайте несвързани обекти с данни за източници на данни, които могат да имат повече от един обект с данни, като например JSON файл, работна книга на Excel или база данни на Access.

Всеки от тези файлове следва проста схема– само по една таблица с данни във всеки файл.

Изберете >получаване на > на данниот >на файл от папка. Появява се диалоговият прозорец Преглед .
Намерете папката, съдържаща файловете, които искате да комбинирате.
Списък на файловете в папката се появява в диалоговия прозорец> път <папка. Уверете се, че всички файлове, които искате, са в списъка.
Изберете една от командите в долната част на диалоговия прозорец, например Комбиниране > Комбиниране & Зареждане. Има допълнителни команди, обсъдени в раздела За всички тези команди.
Ако изберете някоя команда Комбиниране , се показва диалоговият прозорец Комбиниране на файлове . За да промените настройките на файла, изберете всеки файл от полето Примерен файл , задайте Откриване на Произход на файла, Разделител и Тип на данните по желание. Можете също да поставите или изчистите отметката от квадратчето Пропускане на файлове с грешки в долната част на диалоговия прозорец.
Изберете OK.

Result

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

Изберете >получаване на > на данниот >на файл от папка. Появява се диалоговият прозорец Преглед.
Намерете папката, съдържаща файловете, които искате да комбинирате.
Списък на файловете в папката се появява в диалоговия прозорец> път <папка. Уверете се, че всички файлове, които искате, са в списъка.
Изберете една от командите в долната част на диалоговия прозорец, например Комбиниране > Комбиниране & трансформация. Има допълнителни команди, обсъдени в раздела За всички тези команди.

Появява се Редактор на Power Query.
Колоната "Стойност" е структурирана колона "Списък" . Изберете иконата Разгъване на и след това изберете Разгъване до нови редове.
Колоната "Стойност" сега е структурирана колона "Запис" . Изберете иконата Разгъване . Появява се падащ диалогов прозорец.
Запазване на всички колони избрани. Можете да изчистите квадратчето за отметка Използвай първоначалното име на колона като префикс . Изберете OK.
Изберете всички колони, които съдържат стойности на данни. Изберете Начало, стрелката до Премахване на колони и след това изберете Премахване на други колони.
Изберете Начало > Затваряне & Зареждане.

Result

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

Изберете >получаване на > на данниот >на файл от папка. Появява се диалоговият прозорец Преглед.
Намерете папката, съдържаща файловете, които искате да комбинирате.
Списък на файловете в папката се появява в диалоговия прозорец> път <папка. Уверете се, че всички файлове, които искате, са в списъка.
Изберете една от командите в долната част на диалоговия прозорец, например Комбиниране > Комбиниране & Зареждане. Има допълнителни команди, обсъдени в раздела За всички тези команди.
В диалоговия прозорец Комбиниране на файлове :
- В полето Примерен файл изберете файл, който да се използва като примерни данни, използвани за създаване на заявките. Или не можете да изберете обект, или да изберете само един обект. Но не можете да изберете повече от един.
- Ако имате много обекти, използвайте полето Търсене , за да намерите обект, или Опции за показване заедно с бутона Обнови , за да филтрирате списъка.
- Поставете или изчистете отметката от квадратчето Пропускане на файлове с грешки в долната част на диалоговия прозорец.
Изберете OK.

Result

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

За по-голяма гъвкавост можете изрично да комбинирате файлове в Редактор на Power Query с помощта на командата Комбиниране на файлове. Да речем, че папката източник има смесица от типове файлове и подпапки и искате да насочите определени файлове със същия тип файл и схема, но не и към други. Това може да подобри производителността и да ви помогне да опростите трансформациите.

Изберете > Получаване на > на данниот > на файлот папка. Появява се диалоговият прозорец Преглед .
Намерете папката, съдържаща файловете, които искате да комбинирате, и след това изберете Отвори.
Списък на всички файлове в папката и подпапките се появява в диалоговия прозорец >път<папка . Уверете се, че всички файлове, които искате, са в списъка.
Изберете Трансформиране на данни в долната част. Редактор на Power Query отваря и показва всички файлове в папката и всички подпапки.
За да изберете желаните файлове, филтрирайте колони, като например Разширение или Път до папка.
За да комбинирате файловете в една таблица, изберете колоната Съдържание , която съдържа всеки Binary (обикновено първата колона), и след това изберете Начало > Комбиниране на файлове. Появява се диалоговият прозорец Комбиниране на файлове .
Power Query анализира примерен файл, по подразбиране първия файл в списъка, за да използва правилния конектор и да идентифицира съответстващите колони.

За да използвате друг файл за примерния файл, изберете го от падащия списък Примерен файл .
Ако желаете, в долната част изберете Пропускане на файлове с грешки, за да изключите тези файлове от резултата.
Изберете OK.

Result

Има няколко команди, които можете да изберете, и всяка от тях има различна цел.

Комбиниране и трансформиране на данни За да комбинирате всички файлове със заявка и след това да стартирате Редактор на Power Query, изберете Комбиниране > Комбиниране и трансформиране на данни.
Комбиниране и зареждане За да покажете диалоговия прозорец Примерен файл, създайте заявка и след това я заредете в работен лист, изберете Комбиниране > Комбиниране и зареждане.
Комбиниране и зареждане в За да покажете диалоговия прозорец Примерен файл, създайте заявка и след това покажете диалоговия прозорец Импортиране , изберете Комбиниране> Комбиниране и зареждане в.
Натоварване За да създадете заявка с една стъпка и след това да я заредите в работен лист, изберете Зареждане > Зареждане.
Зареди в За да създадете заявка с една стъпка и след това да покажете диалоговия прозорец Импортиране , изберете Зареждане > Зареждане в.
Трансформиране на данниЗа да създадете заявка с една стъпка и след това да стартирате Редактор на Power Query, изберете Трансформиране на данни.

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

Списък на заявките, създадени в прозореца за заявки

Power Query създава заявка "Примерен файл", базирана на примерната заявка.
Заявката на функцията "Файл за трансформация" използва заявката "Параметър1", за да зададе всеки файл (или двоичен) като вход в заявката "Примерен файл". Тази заявка създава и колоната Съдържание , съдържаща съдържанието на файла, и автоматично разширява структурираната колона Запис , за да добави данните от колоната към резултатите. Заявките "Transform File" и "Sample File" са свързани, така че промените в заявката "Примерен файл" се отразяват в заявката "Трансформиране на файл".
Заявката, съдържаща крайните резултати, е в групата "Други заявки". По подразбиране името му е папката, от която сте импортирали файловете.

За по-нататъшно проучване щракнете с десния бутон върху всяка заявка и изберете Редактиране , за да прегледате всяка стъпка от заявката и да видите как заявките работят съгласувано.

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

Забележка: Файловете на Excel, текста, CSV, JSON, XML и Access се поддържат.

Щракнете върху раздела Power Query и след това върху От файл > от папка.
Щракнете върху бутона Преглед , за да намерите вашата папка.

Ще се появи списък на файловете в папката. Изберете опцията Редактиране, за да отворите Редактор на Power Query. В този случай заредихме 4 работни книги на Excel.
След като сте се проверили, че всички файлове, които искате, са изброени, щракнете върху бутона Комбиниране на изпълними файлове от колоната Съдържание .
Всеки файл се анализира и се определя правилният файлов формат, като например текст, Excel или JSON. В този случай се показва списък с работни листове от първата работна книга на Excel. Изберете работния лист, който искате да използвате, и след това щракнете върху OK.
След това процесът на преобразуване на комбинираните изпълними файлове автоматично ще създаде заявка за консолидиране на данните от всеки файл и показване на визуализация. Ако сте доволни от резултата, щракнете върху Затвори & Зареждане.

След това процесът на комбиниране на изпълними файлове ще консолидира изброените работни листове в един работен лист.
Ако оригиналните файлове с данни се променят, винаги можете да обновите импортираните данни. Щракнете някъде в диапазона от данни, след което отидете в Инструменти за заявки > Обновяване. Освен това можете лесно да приложите допълнителна трансформация или стъпки на извличане, като промените автоматично създадената примерна заявка, без да се налага да се безпокоите за модифицирането или създаването на допълнителни стъпки на заявката за функция; всички промени в примерната заявка се генерират автоматично в заявката за свързана функция.

Известни проблеми

При зареждане на процедурата за комбиниране на изпълними файлове в Excel може да видите следното съобщение за грешка:

Съобщение за грешка "Комбиниране на изпълними файлове". Това е известна грешка, която се адресира.

Ако отхвърлите грешките, като щракнете върху OK, процесът на комбиниране на изпълними файлове ще завърши и ще зареди правилно вашите данни въпреки грешката. Работим, за да коригираме грешката.