sql server - Importer des fichiers Excel ayant des en-têtes variables




sql-server ssis (3)

J'ai le paquet SSIS, qui chargera le fichier Excel dans la base de données. J'ai créé une tâche source Excel pour mapper le nom de colonne Excel au nom de colonne de la table de base de données et son fonctionnement correct

Dans de rares cas, nous recevons le nom de colonne du fichier Excel avec un espace (par exemple: le nom de colonne est "ABC" mais nous recevons "ABC") et la cause du problème de mappage et l'échec de SSIS.

Est-il possible de supprimer le nom de la colonne sans ouvrir Excel?

Remarque: Le nom de la page sera dynamique et la position de la colonne peut changer (par exemple: La colonne "ABC peut exister dans la première ligne ou dans la deuxième ligne ou ..").


Ceci a été bien documenté dans MSDN, en passant par les étapes similaires à @houseofsql mentionné

Étape 1:

Exclure les noms de colonne dans la première ligne de la connexion Excel, utilisez la commande sql comme mode d'accès aux données

Étape 2: Les noms de colonne alias dans la colonne de sortie correspondent à votre destination,

Select * from [Sheet1$A2:I] sélectionnera de la deuxième rangée

Enfin, ajoutez une destination comme destination OLEDB


Je suis assez nouveau sur le forum, donc si vous pensez que c'est idiot, prenez-le avec un grain de sel.

MS Access a beaucoup de fonctionnalités VBA identiques à celles d'Excel. Vous pouvez aussi écrire un nouveau classeur Excel sous forme de stub qui analyse et formate les formats avant votre importation SQL, puis l'importe (un middleware si vous voulez).

Pour le problème concernant les espaces de fin ou de début, j’ai utilisé à plusieurs reprises les éléments suivants:

myString = trim(msytring) 'Ceci supprimera tous les espaces de myString = trim(msytring) et de fin, mais ne dérangera pas les espaces entre les caractères. Ainsi, lors de l’importation, vous pouvez exécuter la commande trim sur les en-têtes de colonne au fur et à mesure de leur importation.

Il y a aussi LTrim et RTrim 'vous pouvez deviner ce que ceux-ci font à gauche et à droite de la corde

https://support.office.com/en-us/article/LTrim-RTrim-and-Trim-Functions-e340ced1-67df-435f-b078-1527a4eddea2

Pour les majuscules, vous pouvez utiliser UCase

myString = UCase(Trim(myString))

Et remplacer est toujours utile s'il y a une situation que je traite souvent, où parfois un utilisateur peut utiliser un caractère # et parfois non.

Exemple: "Patterson # 288" ou "PatTeRson 288" myString = UCase(Trim(Replace(myString,"#","") élimine le signe # et supprime les espaces de début et de fin et met également les lettres majuscules en casse l'utilisateur a également commis une erreur

Assez pratique pour exécuter ceci est l’importation et l’exportation de boucles.

Maintenant, si le nom du fichier est en train de changer (c'est le nom du classeur) ou si les noms de la feuille de travail changent, vous pouvez également demander à votre "middleware" de toujours nommer le classeur avec le même nom (avec le contenu du classeur que vous allez importer). ) idem avec les feuilles, ou vous pouvez compter le nombre de feuilles et enregistrer les noms (encore une fois, une chance de les normaliser et de les renommer dans votre "middleware")

Je suppose que ce n’est pas une réponse SQL, mais parce que je ne suis pas très bon avec SQL, je préparerais les données, dans ce cas, un classeur Excel et le normaliser pour l’importation afin que le code ne tombe pas en panne du côté de la base de données (côté serveur). ).

J'utilise excel comme interface frontale à Access avec des scripts de requête SQL et il peut être lié directement à SQL, mais c'est beaucoup plus difficile. Une base de données conviviale au format CSV, telle que PostGre SQL, est utile à cet égard.

J'espère que ça aide. Si vous avez besoin d’aide pour formater le classeur avant l’importation, faites-en une copie et appliquez toutes vos modifications (nom, convention de nom de champ // en-tête de colonne), faites-le moi savoir. Je pourrais probablement aider avec ça.

Ceci est similaire au commentaire de V sur l'exécution d'un script de pré-traitement sur le classeur. C'est comme ça que je l'aborderais.

À la vôtre, WWC


Tout d’abord, ma solution est basée sur les réponses @DrHouseofSQL et @Bhouse, vous devez donc lire la réponse @DrHouseofSQL en premier, puis la réponse @BHouse, puis poursuivre avec cette réponse.

Problème

Remarque: Le nom de la page sera dynamique et la position de la colonne peut changer (par exemple: Column "ABC peut exister dans la première ligne ou la deuxième ligne ou ...

Cette situation est un peu complexe et peut être résolue à l'aide de la solution de contournement suivante:

Vue d'ensemble de la solution

  1. Ajouter une tâche de script avant la tâche de flux de données qui importe les données
  2. Vous devez utiliser la tâche de script pour ouvrir le fichier Excel et obtenir le nom de la feuille de calcul et la ligne d'en-tête.
  3. Construire la requête et la stocker dans une variable
  4. dans la deuxième tâche de flux de données, vous devez utiliser la requête stockée ci-dessus en tant que source ( notez que vous devez définir la propriété Delay Validation sur true )

Détails de la solution

  1. Commencez par créer une variable SSIS de type chaîne (c'est-à-dire @ [User :: strQuery])
  2. Ajouter une autre variable contenant le chemin du fichier Excel (par exemple, @ [User :: ExcelFilePath])
  3. Ajoutez une tâche de script et sélectionnez @[User::strQuery] tant que variable ReadWrite et @[User::ExcelFilePath] tant que variable en lecture @[User::ExcelFilePath] (dans la fenêtre de la tâche de script).
  4. Définissez le langage de script sur VB.Net et dans la fenêtre de l'éditeur de script, écrivez le script suivant:

Remarque: vous devez importer System.Data.OleDb

Dans le code ci-dessous, nous cherchons dans les 15 premières lignes Excel pour trouver l'en-tête. Vous pouvez augmenter le nombre si l'en-tête se trouve après les 15 lignes. Aussi, j'ai supposé que la plage de colonnes est de A à I

    m_strExcelPath = Dts.Variables.Item("ExcelFilePath").Value.ToString

    Dim strSheetname As String = String.Empty
    Dim intFirstRow As Integer = 0

    m_strExcelConnectionString = Me.BuildConnectionString()
    Try


        Using OleDBCon As New OleDbConnection(m_strExcelConnectionString)

            If OleDBCon.State <> ConnectionState.Open Then
                OleDBCon.Open()
            End If

            'Get all WorkSheets
            m_dtschemaTable = OleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                               New Object() {Nothing, Nothing, Nothing, "TABLE"})

            'Loop over work sheet to get the first one (the excel may contains temporary sheets or deleted ones

            For Each schRow As DataRow In m_dtschemaTable.Rows
                strSheetname = schRow("TABLE_NAME").ToString

                If Not strSheetname.EndsWith("_") AndAlso strSheetname.EndsWith("$") Then

                    Using cmd As New OleDbCommand("SELECT * FROM [" & strSheetname & "A1:I15]", OleDBCon)

                        Dim dtTable As New DataTable("Table1")


                        cmd.CommandType = CommandType.Text

                        Using daGetDataFromSheet As New OleDbDataAdapter(cmd)

                            daGetDataFromSheet.Fill(dtTable)

                            For intCount As Integer = 0 To 15

                                If Not String.IsNullOrEmpty(dtTable.Rows(intCount)(0).ToString) Then

                                    '+1 because datatable is zero based indexed, +1 because we want to start from the second row
                                    intFirstRow = intCount + 2

                                End If


                            Next



                        End Using

                        If intFirstRow = 0 Then Throw New Exception("header not found")

                    End Using

                    'when the first correct sheet is found there is no need to check others
                    Exit For

                End If
            Next

            OleDBCon.Close()

        End Using

    Catch ex As Exception
        Throw New Exception(ex.Message, ex)
    End Try


    Dts.Variables.Item("strQuery").Value = "SELECT * FROM [" & strSheetname & "A" & intFirstRow.ToString & ":I]"

    Dts.TaskResult = ScriptResults.Success
End Sub
  1. Ensuite, vous devez ajouter un gestionnaire de connexions Excel et choisir le fichier Excel que vous souhaitez importer (il suffit de sélectionner un exemple pour définir les métadonnées pour la première fois uniquement).
  2. Attribuez une valeur par défaut de Select * from [Sheet1$A2:I] à la variable @[User::strQuery]
  3. Dans la tâche de flux de données, ajoutez une source Excel, choisissez la commande SQL dans la variable, puis sélectionnez @[User::strQuery]
  4. Accédez à l'onglet Colonnes et nommez les colonnes de la même manière que @BHouse a suggéré

Image tirée de @BHouse answer

  1. Définissez la propriété DataFlow Task Delay Validation sur True
  2. Ajouter d'autres composants à la tâche DataFlow

MISE À JOUR 1:

D'après les commentaires de l'OP: il sometimes excel with empty data will come.(ie) we have only header row not not data... in that case it fails entire task

Solution:

Si votre fichier excel ne contient aucune donnée (uniquement l'en-tête), procédez comme suit:

  1. Ajoutez une variable SSIS de type boolean * (c'est- @[User::ImportFile] dire @[User::ImportFile] )
  2. Ajoutez @[User::ImportFile] aux variables ReadWrite de la tâche de script
  3. Dans la tâche de script, vérifiez si le fichier contient des lignes.
  4. Si oui, @[User::ImportFile] = True, sinon @[User::ImportFile] = False
  5. Double-cliquez sur la flèche (contrainte de précédence) qui connecte la tâche de script à DataFlow.
  6. Définissez son type sur Contrainte et Expression
  7. Écrivez l'expression suivante

    @[User::ImportFile] == True

Remarque: le nouveau code de tâche de script est le suivant:

    m_strExcelPath = Dts.Variables.Item("ExcelFilePath").Value.ToString

    Dim strSheetname As String = String.Empty
    Dim intFirstRow As Integer = 0

    m_strExcelConnectionString = Me.BuildConnectionString()
    Try


        Using OleDBCon As New OleDbConnection(m_strExcelConnectionString)

            If OleDBCon.State <> ConnectionState.Open Then
                OleDBCon.Open()
            End If

            'Get all WorkSheets
            m_dtschemaTable = OleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                               New Object() {Nothing, Nothing, Nothing, "TABLE"})

            'Loop over work sheet to get the first one (the excel may contains temporary sheets or deleted ones

            For Each schRow As DataRow In m_dtschemaTable.Rows
                strSheetname = schRow("TABLE_NAME").ToString

                If Not strSheetname.EndsWith("_") AndAlso strSheetname.EndsWith("$") Then

                    Using cmd As New OleDbCommand("SELECT * FROM [" & strSheetname & "A1:I15]", OleDBCon)

                        Dim dtTable As New DataTable("Table1")


                        cmd.CommandType = CommandType.Text

                        Using daGetDataFromSheet As New OleDbDataAdapter(cmd)

                            daGetDataFromSheet.Fill(dtTable)

                            For intCount As Integer = 0 To 15

                                If Not String.IsNullOrEmpty(dtTable.Rows(intCount)(0).ToString) Then

                                    '+1 because datatable is zero based indexed, +1 because we want to start from the second row
                                    intFirstRow = intCount + 2

                                End If


                            Next



                        End Using





                    End Using

                    'when the first correct sheet is found there is no need to check others
                    Exit For

                End If
            Next

            OleDBCon.Close()

        End Using

    Catch ex As Exception
        Throw New Exception(ex.Message, ex)
    End Try

                If intFirstRow = 0 OrElse _
                   intFirstRow > dtTable.Rows.Count Then

                    Dts.Variables.Item("ImportFile").Value = False

                Else

                    Dts.Variables.Item("ImportFile").Value = True

                End If                    

    Dts.Variables.Item("strQuery").Value = "SELECT * FROM [" & strSheetname & "A" & intFirstRow.ToString & ":I]"

    Dts.TaskResult = ScriptResults.Success
End Sub

MISE À JOUR 2:

A partir des commentaires de l'OP: is there any other work around available to process the data flow task without skipping all data flow task,Actually one of the task will log the filename and data count and all, which are missing here

Solution:

  1. Ajoutez simplement une autre tâche DATA FLOW
  2. Connectez ce flux de données avec la tâche de script à l'aide d'un autre connecteur et avec l'expression @[User::ImportFile] == False (même @[User::ImportFile] == False le premier connecteur).
  3. Dans la tâche DataFlow, ajoutez un composant SCript en tant que source.
  4. Créez les colonnes de sortie que vous souhaitez importer dans les journaux
  5. Créer une ligne contenant les informations à importer
  6. Ajouter la destination du journal

Ou au lieu d'ajouter une autre Data Flow Task , vous pouvez ajouter une Execute SQL Task d' Execute SQL Task pour insérer une ligne dans la table de journal





sql-server-data-tools