Importowanie danych z folderu zawierającego wiele plików (Power Query)

Za pomocą dodatku Power Query możesz połączyć wiele plików z tym samym schematem przechowywanym w jednym folderze w jedną tabelę. Na przykład w każdym miesiącu mają być łączony skoroszyty budżetowe z wielu działów, w których kolumny są takie same, ale liczba wierszy i wartości w każdym skoroszycie jest inna. Po jej skonfigurowaniu możesz zastosować dodatkowe przekształcenia, tak jak w przypadku każdego pojedynczego importowanego źródła danych, a następnie odświeżyć dane, aby wyświetlić wyniki dla poszczególnych miesięcy.

Poglądowe omówienie łączenia plików folderów

Uwaga W tym temacie pokazano, jak łączyć pliki z folderu. Możesz również łączyć pliki przechowywane w usłudze SharePoint, usłudze Azure Blob Storage i usłudze Azure Data Lake Storage. Ten proces jest podobny.

Nowsze wersje Office 2010–2013



Zachowaj prostotę:

Upewnij się, że wszystkie pliki, które chcesz połączyć, znajdują się w folderze dedykowanym bez niepotrzebnych plików. W przeciwnym razie wszystkie pliki w folderze i wybrane podfoldery zostaną uwzględnione w danych do połączenie.
Każdy plik powinien mieć taki sam schemat ze spójnymi nagłówkami kolumn, typami danych i liczbą kolumn. Kolumny nie muszą być w tej samej kolejności co zgodne z nazwami kolumn.
Jeśli to możliwe, unikaj niepowiązanych obiektów danych w przypadku źródeł danych, które mogą mieć więcej niż jeden obiekt danych, taki jak plik JSON, skoroszyt Excel lub baza danych programu Access.

Każdy z tych plików ma prosty wzorzec : w każdym pliku jest tylko jedna tabela danych.

Wybierz pozycję Dane > pobierz dane > z pliku > z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie <ścieżka folderu> dialogowym. Sprawdź, czy na liście znajdują się wszystkie pliki, których chcesz użyć.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz> połącz & Załaduj. W sekcji Informacje o wszystkich tych poleceniach omówione są dodatkowe polecenia.
Jeśli wybierzesz dowolne polecenie Połącz,zostanie wyświetlone okno dialogowe Łączenie plików. Aby zmienić ustawienia pliku, zaznacz poszczególne pliki w polu Przykładowy plik, ustaw odpowiednie pola Pochodzenia pliku,Ograniczniki Wykrywanie typu danych. Możesz również zaznaczyć lub wyczyścić pole wyboru Pomiń pliki z błędami u dołu okna dialogowego.
Wybierz przycisk OK.

Wynik

Dodatku Power Query są automatycznie tworzone zapytania, aby skonsolidować dane z poszczególnych plików w arkuszu. Kroki i kolumny zapytania są zależne od tego, które polecenie zostanie wybranie. Aby uzyskać więcej informacji, zobacz sekcję Informacje o wszystkich tych zapytaniach.

Wybierz pozycję Dane > pobierz dane > z pliku > z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie <ścieżka folderu> dialogowym. Sprawdź, czy na liście znajdują się wszystkie pliki, których chcesz użyć.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz> Połącz & Przekształcenie. W sekcji Informacje o wszystkich tych poleceniach omówione są dodatkowe polecenia.

Zostanie wyświetlony Edytor dodatku Power Query.
Kolumna Wartość jest kolumną listy strukturalnej. Wybierz ikonę Rozwiń , a następnie wybierz pozycję Rozwiń do nowych wierszy.
Kolumna Wartość jest teraz kolumną rekordu strukturalnego. Wybierz ikonę Rozwiń . Zostanie wyświetlone okno dialogowe listy rozwijanej.
Zachowaj zaznaczone wszystkie kolumny. Można wyczyścić pole wyboru Użyj oryginalnej nazwy kolumny jako prefiksu. Wybierz przycisk OK.
Zaznacz wszystkie kolumny zawierające wartości danych. Wybierz pozycję Narzędziagłówna , strzałkę obok przycisku Usuń kolumny, a następnie wybierz pozycję Usuń inne kolumny.
Wybierz pozycję Narzędzia > Zamknij & Załaduj.

Wynik

Każde z tych źródeł danych może mieć więcej niż jeden obiekt do zaimportowania. Skoroszyt Excel może mieć wiele arkuszy, tabel Excel lub nazwanych zakresów. Baza danych programu Access może mieć wiele tabel i zapytań.

Wybierz pozycję Dane > pobierz dane > z pliku > z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć.
Lista plików w folderze zostanie wyświetlona w oknie <ścieżka folderu> dialogowym. Sprawdź, czy na liście znajdują się wszystkie pliki, których chcesz użyć.
Wybierz jedno z poleceń u dołu okna dialogowego, na przykład Połącz> połącz & Załaduj. W sekcji Informacje o wszystkich tych poleceniach omówione są dodatkowe polecenia.
W oknie dialogowym Łączenie plików:
- W polu Przykładowy plik wybierz plik, który ma być używany jako przykładowe dane używane do tworzenia zapytań. Nie można zaznaczyć obiektu ani zaznaczyć tylko jednego obiektu. Nie możesz jednak zaznaczyć więcej niż jednego.
- Jeśli masz wiele obiektów, użyj pola Wyszukaj, aby zlokalizować obiekt lub przycisk Opcje wyświetlania razem z przyciskiem Odśwież w celu przefiltrowania listy.
- Zaznacz lub wyczyść pole wyboru Pomiń pliki z błędami u dołu okna dialogowego.
Wybierz przycisk OK.

Wynik

Power Query automatycznie tworzy zapytanie w celu skonsolidowania danych z poszczególnych plików w arkuszu. Kroki i kolumny zapytania są zależne od tego, które polecenie zostanie wybranie. Aby uzyskać więcej informacji, zobacz sekcję Informacje o wszystkich tych zapytaniach.

Aby zapewnić większą elastyczność, możesz jawnie łączyć pliki w Edytorze dodatku Power Query przy użyciu polecenia Połącz pliki. Załóżmy, że folder źródłowy ma mieszaninę typów plików i podfolderów i chcesz kierować określone pliki z tym samym typem pliku i schematem, ale nie innymi. Może to zwiększyć wydajność i uprościć przekształcenia.

Wybierz pozycję Dane > Pobierz dane > z pliku > z folderu. Zostanie wyświetlone okno dialogowe Przeglądanie.
Znajdź folder zawierający pliki, które chcesz połączyć, a następnie wybierz pozycję Otwórz.
Lista wszystkich plików w folderze i podfolderach zostanie wyświetlona w oknie <ścieżka folderu>dialogowym. Sprawdź, czy na liście znajdują się wszystkie pliki, których chcesz użyć.
Wybierz pozycję Przekształć dane u dołu. Zostanie otwarty Edytor dodatku Power Query z wyświetlonymi wszystkimi plikami w folderze oraz wszystkimi podfolderami.
Aby wybrać odpowiednie pliki, przefiltruj kolumny, takie jak Rozszerzenie lub Ścieżka folderu.
Aby połączyć pliki w jedną tabelę, zaznacz kolumnę Zawartość zawierającą wszystkie dane binarne (zazwyczaj pierwsza kolumna), a następnie wybierz pozycję Narzędzia > Połącz pliki. Zostanie wyświetlone okno dialogowe Łączenie plików.
W dodatku Power Query jest domyślnie analizowany pierwszy plik na liście, aby użyć właściwego łącznika i zidentyfikować pasujące kolumny.

Aby użyć innego pliku dla pliku przykładowego, wybierz go z listy rozwijanej Przykładowy plik.
Opcjonalnie u dołu wybierz pozycję Pomiń pliki zbłędami, aby wykluczyć te pliki z wyniku.
Wybierz przycisk OK.

Wynik

Dodatku Power Query są automatycznie tworzone zapytania konsolidowane w celu skonsolidowania danych z poszczególnych plików w arkuszu. Kroki i kolumny zapytania są zależne od tego, które polecenie zostanie wybranie. Aby uzyskać więcej informacji, zobacz sekcję Informacje o wszystkich tych zapytaniach.

Można wybrać kilka poleceń, z których każde ma inne przeznaczenie.

Łączenie i przekształcanie danych Aby połączyć wszystkie pliki z zapytaniem, a następnie uruchomić Edytor dodatku Power Query, wybierz pozycję Połącz> łączenie i przekształcanie danych.
Łączenie i ładowanie Aby wyświetlić okno dialogowe Przykładowy plik, utwórz zapytanie, a następnie załaduj je do arkusza, wybierz pozycję Połącz > Połącz i załaduj.
Połącz i załaduj do Aby wyświetlić okno dialogowe Przykładowy plik, utwórz zapytanie, a następnie wyświetl okno dialogowe Importowanie, wybierz pozycję Połącz> Połącz i Załaduj do.
Załaduj Aby utworzyć zapytanie jednym krokiem, a następnie załadować je do arkusza, wybierz pozycję Załaduj> Załaduj.
Załaduj do Aby utworzyć zapytanie jednym krokiem, a następnie wyświetlić okno dialogowe Importowanie, wybierz pozycję Załaduj> Załaduj do.
Przekształcanie danychAby utworzyć zapytanie jednym krokiem, a następnie uruchomić Edytor dodatku Power Query, wybierz pozycję Przekształć dane.

Jednak w przypadku łączenia plików w okienku Zapytania w grupie "Zapytania pomocników" jest tworzona kilka zapytań.

Lista zapytań utworzonych w okienku Zapytania

Na podstawie przykładowego zapytania w dodatku Power Query zostanie automatycznie pobrane zapytanie "Przykładowy plik".
Zapytanie funkcji "Plik transformacji" używa zapytania "Parametr1" do określenia każdego pliku (lub pliku binarnego) jako danych wejściowych dla zapytania "Przykładowy plik". To zapytanie tworzy również kolumnę Content (Zawartość) zawierającą zawartość pliku i automatycznie rozszerza kolumnę Structured Record (Rekord) w celu dodania danych z tej kolumny do wyników. Zapytania "Plik przekształcany" i "Przykładowy plik" są połączone, dzięki czemu zmiany w zapytaniu "Przykładowy plik" są odzwierciedlane w zapytaniu "Plik przekształcenia".
Zapytanie zawierające końcowe wyniki znajduje się w grupie "Inne zapytania". Nazwa folderu, z którym zostały zaimportowane pliki, jest domyślnie domyślną nazwą.

Aby dokładniej zbadać poszczególne zapytania, kliknij je prawym przyciskiem myszy i wybierz polecenie Edytuj, aby zbadać poszczególne kroki zapytania i zobaczyć, jak działają te zapytania podczas ich pracy.

Aby rozpocząć proces łączenia wielu plików, najpierw upewnij się, że wszystkie pliki, które chcesz połączyć, znajdują się w jednym folderze plików.

Uwaga: Excel obsługiwane są pliki tekstowe, CSV, JSON, XML i access.

Kliknij kartę Power Query, a następnie pozycję Z pliku >z folderu.
Kliknij przycisk Przeglądaj, aby zlokalizować folder.

Zostanie wyświetlona lista plików w folderze. Wybierz opcję Edytuj, aby otworzyć Edytor zapytań. W tym przypadku załadowaliśmy 4 Excel skoroszytów.
Po sprawdzeniu, że na liście znajdują się wszystkie pliki, których potrzebujesz, kliknij przycisk Połącz binariusze w kolumnie Zawartość.
Każdy plik jest analizowany i jest określany właściwy format pliku, taki jak tekst,Excel lubJSON. W tym przypadku zostanie wyświetlona lista arkuszy z pierwszego Excel skoroszytu. Zaznacz arkusz, którego chcesz użyć, a następnie kliknij przycisk OK.
Proces przekształcania danych binralnych Połącz spowoduje automatyczne utworzenie zapytania w celu skonsolidowania danych z każdego pliku i wyświetlenia podglądu. Jeśli wynik jest zadowalany, kliknij pozycję Zamknij, & załaduj.

Proces łączenia danych binralnych konsoliduje wymienione arkusze w jeden arkusz.
Jeśli zmienią się oryginalne pliki danych, zawsze możesz odświeżyć zaimportowane dane. Kliknij dowolne miejsce w zakresie danych, a następnie przejdź do narzędzia zapytań, > odśwież. Ponadto można łatwo zastosować dodatkowe kroki przekształcania lub wyodrębniania, modyfikując automatycznie utworzone zapytanie exemplar bez konieczności modyfikowania ani tworzenia dodatkowych kroków zapytania funkcyjnego. Wszelkie zmiany zapytania exemplar zostaną automatycznie wygenerowane w połączonym zapytaniu funkcji.

Znane problemy

Podczas ładowania procedury combine binaries do Excel może zostać wyświetlony następujący komunikat o błędzie:

Komunikat o błędzie Łączenie danych binralnych. Jest to znany błąd, który jest wyasyłany.

Jeśli odrzucisz błędy, klikając przycisk OK,proces łączenia danych binralnych zostanie ukończony i poprawne załadowanie danych pomimo błędu. Pracujemy nad poprawieniem tego błędu.