Arbeta med Excel Files med skriptuppgiften

Gäller för:SQL Server SSIS Integration Runtime i Azure Data Factory

Integration Services tillhandahåller Excel-anslutningshanteraren, Excel-källkod och Excel-destination för att arbeta med data lagrade i kalkylblad i Microsoft Excel-filformat. De tekniker som beskrivs i detta ämne använder Script-uppgiften för att hämta information om tillgängliga Excel-databaser (arbetsboksfiler) och tabeller (arbetsblad och namngivna intervall).

Konfigurera ett paket för att testa proverna

Du kan konfigurera ett enda paket för att testa alla prover i detta ämne. Proverna använder många av samma paketvariabler och samma .NET Framework-klasser.

För att konfigurera ett paket för användning med exemplen i detta ämne

1. Skapa ett nytt Integration Services-projekt i SQL Server Data Tools (SSDT) och öppna standardpaketet för redigering.

2. Variabler Öppna fönstret Variabler och definiera följande variabler :

   • ExcelFile, av typen String. Ange hela sökvägen och filnamnet till en befintlig Excel-arbetsbok.

   • ExcelTable, av typen String. Ange namnet på ett befintligt arbetsblad eller ett namngivet intervall i arbetsboken som är namngiven i variabelns värde ExcelFile . Det här värdet är skiftlägeskänsligt.

   • ExcelFileExists, av typen Boolesk.

   • ExcelTableExists, av typen Boolesk.

   • ExcelFolder, av typen String. Ange hela sökvägen för en mapp som innehåller minst en Excel-arbetsbok.

   • ExcelFiles, av typen Objekt.

   • ExcelTables, av typen Objekt.

3. Importutdrag. De flesta kodexempel kräver att du importerar en eller båda av följande .NET Framework-namnrymder högst upp i din skriptfil:

   • System.IO, för filsystemoperationer.

   • System.Data.OleDb, för att öppna Excel-filer som datakällor.

4. Referenser. De kodexempel som läser schemainformation från Excel-filer kräver en ytterligare referens i skriptprojektet till System.XML-namnrymden.

5. Ställ in standardskriptspråket för Skriptkomponenten genom att använda alternativet Skriptspråk på sidan Allmänt i dialogrutan Alternativ . Mer information finns på sidan Allmänt.

Exempel 1 Beskrivning: Kontrollera om en Excel-fil finns

Detta exempel avgör om Excel-arbetsboksfilen som anges i variabeln ExcelFile existerar, och sätter sedan det booleska värdet för variabeln ExcelFileExists till resultatet. Du kan använda detta booleska värde för att förgrena sig i paketets arbetsflöde.

För att konfigurera detta exempel på Script Task

1. Lägg till en ny Script-uppgift i paketet och ändra dess namn till ExcelFileExists.

2. I Script Task Editor, på fliken Script , klicka på ReadOnlyVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelFile.

     -or-

   • Klicka på ellipsisknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj ExcelFile-variabeln .

3. Klicka på ReadWriteVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelFileExists.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler väljer du variabeln ExcelFileExists .

4. Klicka på Redigera skript för att öppna skriptredigeraren.

5. Lägg till en Imports-sats för namnrymden System.IO högst upp i skriptfilen.

6. Lägg till följande kod.

Exempel 1 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Exempel 2 Beskrivning: kontrollera om en Excel-tabell finns

Detta exempel avgör om Excel-arbetsbladet eller det namngivna intervallet som anges i variabeln ExcelTable finns i Excel-arbetsboksfilen som anges i variabelnExcelFile, och sätter sedan det booleska värdet för variabeln ExcelTableExists till resultatet. Du kan använda detta booleska värde för att förgrena sig i paketets arbetsflöde.

För att konfigurera detta exempel på Script Task

1. Lägg till en ny Script-uppgift i paketet och ändra dess namn till ExcelTableExists.

2. I Script Task Editor, på fliken Script , klicka på ReadOnlyVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelTable och ExcelFile separerade efter kommatecken.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler välj variablerna ExcelTable och ExcelFile .

3. Klicka på ReadWriteVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelTableExists.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler välj variabeln ExcelTableExists .

4. Klicka på Redigera skript för att öppna skriptredigeraren.

5. Lägg till en referens till System.XML-assembleren i skriptprojektet.

6. Lägg till Imports-satser för System.IO och System.Data.OleDb-namnrymder högst upp i skriptfilen.

7. Lägg till följande kod.

Exempel 2 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}  

Exempel 3 Beskrivning: Hämta en lista över Excel-filer i en mapp

Detta exempel fyller en array med listan över Excel-filer som finns i mappen som anges i variabelns värdeExcelFolder, och kopierar sedan arrayen till variabelnExcelFiles. Du kan använda Foreach from Variable enumerator för att iterera över filerna i arrayen.

För att konfigurera detta exempel på Script Task

1. Lägg till en ny Script-uppgift i paketet och ändra dess namn till GetExcelFiles.

2. Öppna Script Task Editor, klicka på ReadOnlyVariables under fliken Script och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelFolder

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj variabeln ExcelFolder.

3. Klicka på ReadWriteVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelFiles.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj variabeln ExcelFiles.

4. Klicka på Redigera skript för att öppna skriptredigeraren.

5. Lägg till en Imports-sats för namnrymden System.IO högst upp i skriptfilen.

6. Lägg till följande kod.

Exempel 3 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xlsx";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Alternativ lösning

Istället för att använda en Script-uppgift för att samla en lista med Excel-filer i en array, kan du också använda ForEach File enumerator för att iterera över alla Excel-filer i en mapp. För mer information, se Loop through Excel Files and Tables genom att använda en Foreach Loop Container.

Exempel 4 Beskrivning: Hämta en lista över tabeller i en Excel-fil

Detta exempel fyller en array med listan över arbetsblad och namngivna intervall som finns i Excel-arbetsboksfilen specificerad av variabelns värdeExcelFile, och kopierar sedan arrayen till variabelnExcelTables. Du kan använda Foreach från variabeluppräknaren för att iterera över tabellerna i arrayen.

För att konfigurera detta exempel på Script Task

1. Lägg till en ny Script-uppgift i paketet och ändra dess namn till GetExcelTables.

2. Öppna Script Task Editor, klicka på ReadOnlyVariables under fliken Script och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelFile.

     -or-

   • Klicka på ellipsisknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj ExcelFile-variabeln.

3. Klicka på ReadWriteVariables och ange egenskapsvärdet med en av följande metoder:

   • Skriv ExcelTables.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj ExcelTablesvariable.

4. Klicka på Redigera skript för att öppna skriptredigeraren.

5. Lägg till en referens till System.XML-namnrymden i skriptprojektet.

6. Lägg till ett Imports-sats för System.Data.OleDb-namnrymden högst upp i skriptfilen.

7. Lägg till följande kod.

Exempel 4 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim excelFile As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim tablesInFile As DataTable  
    Dim tableCount As Integer = 0  
    Dim tableInFile As DataRow  
    Dim currentTable As String  
    Dim tableIndex As Integer = 0  
  
    Dim excelTables As String()  
  
    excelFile = Dts.Variables("ExcelFile").Value.ToString  
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.0"  
    excelConnection = New OleDbConnection(connectionString)  
    excelConnection.Open()  
    tablesInFile = excelConnection.GetSchema("Tables")  
    tableCount = tablesInFile.Rows.Count  
    ReDim excelTables(tableCount - 1)  
    For Each tableInFile In tablesInFile.Rows  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].Value.ToString();  
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.0";  
            excelConnection = new OleDbConnection(connectionString);  
            excelConnection.Open();  
            tablesInFile = excelConnection.GetSchema("Tables");  
            tableCount = tablesInFile.Rows.Count;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

Alternativ lösning

Istället för att använda en Script-uppgift för att samla en lista med Excel-tabeller i en array, kan du också använda ForEach ADONET Schema Rowset Enumerator för att iterera över alla tabeller (det vill säga arbetsblad och namngivna intervall) i en Excel-arbetsbokfil. För mer information, se Loop through Excel Files and Tables genom att använda en Foreach Loop Container.

Visning av provernas resultat

Om du har konfigurerat varje exempel i detta ämne i samma paket kan du koppla alla Script-uppgifter till en extra Script-uppgift som visar utdata från alla exempel.

För att konfigurera en skriptuppgift som visar utdata från exemplen i detta ämne

1. Lägg till en ny Script-uppgift i paketet och ändra dess namn till DisplayResults.

2. Koppla ihop var och en av de fyra exempel-Script-uppgifterna med varandra, så att varje uppgift körs efter att föregående uppgift slutförts framgångsrikt, och koppla den fjärde exempeluppgiften till DisplayResults-uppgiften .

3. Öppna DisplayResults-uppgiften i skriptets uppgiftsredigerare.

4. På fliken Script , klicka på ReadOnlyVariables och använd en av följande metoder för att lägga till alla sju variabler som listas i Configuring a Package to Test the Samples:

   • Skriv in namnet på varje variabel separerad med kommatecken.

     -or-

   • Klicka på ellipsknappen (...) bredvid egenskapsfältet, och i dialogrutan Välj variabler , välj variablerna.

5. Klicka på Redigera skript för att öppna skriptredigeraren.

6. Add Imports-satser för Microsoft. VisualBasic och System.Windows. Forms-namnrymder högst upp i skriptfilen.

7. Lägg till följande kod.

8. Kör paketet och granska resultaten som visas i en meddelanderuta.

Kod för att visa resultaten

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

Relaterat innehåll

• Importera data från Excel eller exportera data till Excel med služba SSIS (SSIS)
• Loopa igenom Excel-filer och tabeller med en Foreach-loopcontainer