sql-server excel ssis etl sql-server-data-tools

sql server - Importar archivos de Excel con encabezados variables



sql-server ssis (4)

¿Se está creando el archivo de forma manual o automática? En cualquier caso, puede eliminar la fila del encabezado (ya sea mediante programación o decirle a las personas que lo eliminen antes de guardar el archivo) del archivo de Excel por completo. Una vez que haga eso, vaya al Administrador de conexión de Excel y busque el cuadro que indica ''La primera fila tiene nombres de columna''. Si puede borrar ese cuadro, asigne nuevamente las columnas al destino que debería resolver su problema. Nunca tendrá que preocuparse por una escritura incorrecta (o espacios adicionales) en los nombres de las columnas.

Creo que también hay una opción en SSIS para omitir la primera fila por completo, pero no puedo recordar dónde está esa opción. Si puede encontrar eso, simplemente omita la primera fila del archivo de Excel. Las mismas asignaciones aún permanecen.

Gracias

Tengo el paquete SSIS, que cargará el archivo de Excel en la base de datos. He creado la tarea Fuente de Excel para asignar el nombre de la columna de Excel al nombre de la columna de la tabla de la base de datos y funciona bien.

En casos excepcionales, estamos recibiendo el nombre de la columna del archivo de Excel con algo de espacio (por ejemplo: el nombre de la columna es "ABC" pero estamos recibiendo "ABC") y eso causa el problema de mapeo y SSIS falló.

¿Hay alguna posibilidad de recortar el nombre de la columna sin abrir el Excel?

Nota: El nombre de la página será dinámico y la posición de la columna puede cambiar (por ejemplo: la columna "ABC puede existir en la primera fila o la segunda fila o ...").

Esto se ha documentado bien en MSDN, siguiendo los pasos similares a los mencionados en @houseofsql

Paso 1:

Excluya los nombres de columna en la primera fila en la conexión de Excel, use el comando sql como modo de acceso a datos

Paso 2: los nombres de columna de alias en la columna de salida coinciden con su destino,

Seleccione * de [Sheet1$A2:I] seleccionará de la segunda fila

Finalmente Agregar destino como destino OLEDB

Soy bastante nuevo en el foro, así que si crees que esto es una tontería, tómalo con un poco de sal.

MS Access tiene casi la misma funcionalidad de VBA que Excel o puede escribir un nuevo libro de trabajo de Excel que analice y formatee antes de su importación SQL y luego importe eso (un middleware si lo desea).

Para el problema con respecto a espacios finales o iniciales, he usado lo siguiente en muchas ocasiones:

myString = trim(msytring) ''Esto eliminará todos los espacios myString = trim(msytring) y finales, pero no alterará los espacios entre los caracteres. Entonces, al importar, puede ejecutar recortar en los encabezados de columna a medida que los importa.

También hay LTrim y RTrim '', puedes adivinar lo que hacen a la izquierda y a la derecha de la cadena

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

Para mayúsculas puede usar UCase

myString = UCase(Trim(myString))

Y Reemplazar siempre es útil si hay una situación, ya que a menudo trato con que a veces un usuario puede usar un # char y otras no.

Ejemplo: "Patterson # 288" o "PatTeRson 288" myString = UCase(Trim(Replace(myString,"#","") ''elimina el signo # y elimina los espacios iniciales y finales y también pone en mayúsculas las letras en caso de que el usuario también cometió un error

Bastante útil para ejecutar esto es la importación y exportación de bucles.

Ahora, si el nombre del archivo está cambiando (este es el nombre del libro de trabajo) o si los nombres de la hoja de trabajo están cambiando, también puede hacer que su "middleware" siempre nombre el libro de trabajo con el mismo nombre (con el contenido del libro de trabajo que va a importar ) lo mismo con las hojas, o puede contar el número de hojas y registrar los nombres (una vez más, una oportunidad para estandarizarlos y cambiarles el nombre en su "middleware")

Supongo que no es una respuesta SQL, pero debido a que no soy tan bueno con SQL, prepararía los datos, en este caso, primero un Excel Workbook y lo estandarizaría para importar para que el código no se rompa en el lado de la base de datos (lado del servidor )

Utilizo Excel como front-end para acceder con scripts de consulta SQL y se puede vincular directamente a SQL, pero es mucho más difícil. Una base de datos amigable .CSV como PostGre SQL ayuda en ese sentido.

Espero que esto ayude. Si necesita ayuda para formatear el libro de trabajo antes de importar, haga una copia y aplique todos sus cambios (nomenclatura, convención de nombre de campo / encabezado de columna), hágamelo saber. Probablemente podría ayudar con eso.

Esto es similar al comentario de V de ejecutar un script de preprocesamiento en el libro. Así es como lo abordaría.

Saludos, WWC

En primer lugar, mi solución se basa en las respuestas @DrHouseofSQL y @Bhouse, por lo que primero debe leer la respuesta @DrHouseofSQL y luego la respuesta @BHouse y luego continuar con esta respuesta

Problema

Nota: El nombre de la página será dinámico y la posición de la columna puede cambiar (por ejemplo: la columna "ABC puede existir en la primera fila o la segunda fila o ...

Esta situación es un poco compleja y se puede resolver con la siguiente solución:

Resumen de la solución

  1. Agregue una tarea de secuencia de comandos antes de la tarea de flujo de datos que importa los datos
  2. Debe usar la tarea de script para abrir el archivo de Excel y obtener el nombre de la hoja de trabajo y la fila del encabezado
  3. Cree la consulta y guárdela en una variable
  4. en la segunda tarea de flujo de datos, debe usar la consulta almacenada anteriormente como fuente ( tenga en cuenta que debe establecer la propiedad de Delay Validation en verdadero )

Detalles de la solución

  1. Primero cree una variable SSIS de tipo cadena (es decir, @ [Usuario :: strQuery])
  2. Agregue otra variable que contenga la ruta del archivo de Excel (es decir, @ [User :: ExcelFilePath])
  3. Agregue una tarea de secuencia de comandos y seleccione @[User::strQuery] como variable ReadWrite y @[User::ExcelFilePath] como variable de solo @[User::ExcelFilePath] (en la ventana de tarea de secuencia de comandos)
  4. Establezca el lenguaje de script en VB.Net y en la ventana del editor de script escriba el siguiente script:

Nota: debe importar System.Data.OleDb

En el siguiente código, buscamos las primeras 15 filas de Excel para encontrar el encabezado, puede aumentar el número si el encabezado se puede encontrar después de las 15 filas. También supuse que el rango de columnas es de A 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. Luego debe agregar un administrador de conexión de Excel y elegir el archivo de Excel que desea importar (solo seleccione una muestra para definir los metadatos solo por primera vez)
  2. Asigne un valor predeterminado de Select * from [Sheet1$A2:I] a la variable @[User::strQuery]
  3. En la tarea de flujo de datos, agregue un origen de Excel, elija el comando SQL de la variable y seleccione @[User::strQuery]
  4. Vaya a la pestaña de columnas y nombre las columnas de la misma manera que @BHouse sugirió

Imagen tomada de la respuesta de @BHouse

  1. Establezca la propiedad DataFlow Task Delay Validation en True
  2. Agregar otros componentes a la tarea DataFlow

ACTUALIZACIÓN 1:

De los comentarios de OP: a sometimes excel with empty data will come.(ie) we have only header row not not data... in that case it fails entire task

Solución:

Si su archivo de Excel no contiene datos (solo encabezado), debe realizar estos pasos:

  1. Agregue una variable SSIS de tipo boolean * (es decir, @[User::ImportFile] )
  2. Agregue @[User::ImportFile] a la tarea de script Variables ReadWrite
  3. En la tarea de secuencia de comandos, compruebe si el archivo contiene filas
  4. En caso afirmativo, @[User::ImportFile] = True, de lo contrario @[User::ImportFile] = False
  5. Haga doble clic en la flecha (restricción de precedencia) que conecta la tarea de script al DataFlow
  6. Establezca su tipo en Restricción y Expresión

  7. Escribe la siguiente expresión

    @[User::ImportFile] == True

Nota: El nuevo código de tarea de script es:

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

ACTUALIZACIÓN 2:

De los comentarios de 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

Solución:

  1. Simplemente agregue otra tarea de FLUJO DE DATOS
  2. Conecte este flujo de datos con la tarea de script utilizando otro conector y con la expresión @[User::ImportFile] == False (mismos pasos del primer conector)
  3. En la tarea DataFlow, agregue un componente SCript como fuente
  4. Cree las columnas de salida que desea importar a los registros
  5. Cree una fila que contenga la información que necesita importar
  6. Agregar el destino del registro

O, en lugar de agregar otra Data Flow Task , puede agregar una Execute SQL Task para insertar una fila en la tabla de registro