Przeglądanie plików i tabel programu Excel za pomocą kontenera pętli Foreach

Dotyczy:SQL Server Środowisko SSIS Integration Runtime w usłudze Azure Data Factory

Procedury opisane w tym temacie opisują, jak przechodzić przez podręczniki Excel w folderze lub tabele w zeszycie Excel, korzystając z kontenera Foreach Loop z odpowiednim enumeratorem.

Aby iterować po plikach programu Excel za pomocą enumeratora Foreach File

1. Stwórz zmienną ciągową, która będzie otrzymywać aktualną ścieżkę Excel i nazwę pliku przy każdej iteracji pętli. Aby uniknąć problemów z walidacją, przypisz prawidłową ścieżkę Excel oraz nazwę pliku jako wartość początkową zmiennej. (Przykładowe wyrażenie pokazane później w tej procedurze używa nazwy zmiennej, ExcelFile.)

2. Opcjonalnie utwórz inną zmienną typu string, która będzie przechowywać wartość argumentu „Extended Properties” ciągu połączenia programu Excel. Ten argument zawiera szereg wartości, które określają wersję Excel i określają, czy pierwszy wiersz zawiera nazwy kolumn oraz czy używany jest tryb importu. (Przykładowe wyrażenie pokazane później w tej procedurze używa nazwy ExtPropertieszmiennej , z wartością początkową "Excel 12.0;HDR=Yes".)

   Jeśli nie używasz zmiennej dla argumentu Extended Properties, musisz dodać ją ręcznie do wyrażenia zawierającego parametry połączenia.

3. Dodaj kontener Pętli Foreach do zakładki Control Flow . Aby uzyskać informacje o konfiguracji kontenera pętli Foreach, zobacz Konfiguruj kontener pętli Foreach.

4. Na stronie Collection w Edytorze pętli Foreach wybierz enumerator Foreach File, wskaż folder, w którym znajdują się skoroszyty programu Excel, i określ filtr plików (zwykle *.xlsx).

5. Na stronie Mapowania zmiennych przypisz Indeks 0 na zdefiniowaną przez użytkownika zmienną ciągową, która otrzyma aktualną ścieżkę Excel i nazwę pliku przy każdej iteracji pętli. (Przykładowe wyrażenie pokazane później w tej procedurze używa nazwy ExcelFilezmiennej.)

6. Zamknij edytor pętli Foreach.

7. Dodaj Excel connection manager do pakietu, jak opisano w Dodaj, Usuń lub Udostępnij Menedżer połączeń w Pakiecie. Wybierz istniejący plik zeszytu Excel dla połączenia, aby uniknąć błędów walidacji.

   Ważna

   Aby uniknąć błędów walidacyjnych podczas konfigurowania zadań i komponentów przepływu danych korzystających z tego Excel connection manager, wybierz istniejący zeszyt Excel w Excel Menedżer połączeń Editorze. Menedżer połączeń nie będzie używał tego workbooka w czasie działania po skonfigurowaniu wyrażenia dla właściwości ConnectionString , jak opisano w poniższych krokach. Po utworzeniu i skonfigurowaniu pakietu możesz wyczyścić wartość właściwości ConnectionString w oknie Properties. Jednak jeśli usuniesz tę wartość, właściwość parametry połączenia w menedżerze połączeń Excel nie jest już ważna, dopóki nie uruchomi się pętla Foreach. Dlatego musisz ustawić właściwość DelayValidation na True w zadaniach, w których używany jest menedżer połączeń, lub na samym pakiecie, aby uniknąć błędów walidacji.

   Musisz także użyć domyślnej wartości False dla właściwości RetainSameConnection w menedżerze połączeń Excel. Jeśli zmienisz tę wartość na True, każda iteracja pętli będzie nadal otwierać pierwszy zeszyt Excel.

8. Wybierz nowy menedżer połączeń w Excel, kliknij właściwość Expressions w oknie Properties, a następnie kliknij wielokropek.

9. W Edytorze Wyrażeń Właściwości wybierz właściwość ConnectionString , a następnie kliknij elipsę.

10. W Expressionbuilderze wpisz następujące wyrażenie:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""  
    

    Zwróć uwagę na użycie znaku escape "\" do uniknięcia cudzysłowów wewnętrznych wymaganych wokół wartości argumentu Rozszerzonych Właściwości.

    Argument Rozszerzonych Właściwości nie jest opcjonalny. Jeśli nie używasz zmiennej do zawierania jej wartości, musisz dodać ją ręcznie do wyrażenia, jak w następującym przykładzie:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 12.0"  
    

11. Utwórz zadania w kontenerze Foreach Loop, które wykorzystują menedżera połączeń Excel do wykonywania tych samych operacji na każdym podręczniku Excel odpowiadającym określonej lokalizacji i wzorcowi pliku.

Jak iterować po tabelach programu Excel za pomocą enumeratora Foreach zbioru wierszy schematu ADO.NET

1. Stwórz menedżera połączeń ADO.NET, który korzysta z dostawcy baz danych Microsoft ACE OLE do łączenia się z zeszytem Excel. Na stronie "Wszystko" w oknie dialogowym Menedżer połączeń upewnij się, że wpisałeś wersję Excel – w tym przypadku Excel 12.0 – jako wartość właściwości rozszerzonych. Więcej informacji można znaleźć w artykule Dodaj, usuń lub Udostępnij Menedżer połączeń w pakiecie.

2. Stwórz zmienną łańcuchową, która otrzyma nazwę bieżącej tabeli przy każdej iteracji pętli.

3. Dodaj kontener pętli Foreach do karty Control Flow. Aby uzyskać informacje na temat konfigurowania kontenera pętli Foreach, zobacz Konfigurowanie kontenera pętli Foreach.

4. Na stronie Kolekcja w Edytorze pętli Foreach wybierz enumerator zestawu wierszy schematu Foreach ADO.NET.

5. Jako wartość Połączenie wybierz menedżera połączeń ADO.NET, który wcześniej utworzyłeś.

6. Jako wartość Schema wybierz Tabele.

   Note

   Lista tabel w zeszycie Excel zawiera zarówno arkusze (które mają przyrostek $), jak i nazwane zakresy. Jeśli musisz filtrować listę tak, aby zawierała tylko arkusze lub tylko nazwane zakresy, może być konieczne napisanie własnego kodu w zadaniu Script w tym celu. Więcej informacji można znaleźć w artykule Praca z plikami Excel za pomocą zadania skryptowego.

7. Na stronie Mapowania zmiennych przypisz indeks 2 do zmiennej ciągowej utworzonej wcześniej w celu przechowywania nazwy bieżącej tabeli.

8. Zamknij edytor pętli Foreach.

9. Utwórz zadania w kontenerze Foreach Loop, które używają menedżera połączeń programu Excel, aby wykonać te same operacje na każdej tabeli programu Excel w określonym skoroszycie. Jeśli używasz zadania skryptowego do analizy nazwy wyliczonej tabeli lub pracy z każdą tabelą, pamiętaj, aby dodać zmienną ciągową do właściwości ReadOnlyVariables zadania skryptowego.

Treści powiązane

• importowanie danych z programu Excel lub eksportowanie danych do programu Excel przy użyciu usług SQL Server Integration Services (SSIS)
• Kontener Pętli Foreach
• Dodawanie lub zmienianie wyrażenia właściwości
• Menedżer połączeń programu Excel
• Źródło programu Excel
• Miejsce docelowe programu Excel
• Praca z plikami programu Excel za pomocą zadania skryptu