none
Carga masiva de datos desde excel RRS feed

  • Pregunta

  • Hola,

    Agradecería que me puedan ayudar en la siguiente situación;

    Necesito hacer una carga masiva de datos desde excel, esto lo hago de la siguiente forma:

    1- selecciono el archivo excel con un openFileDialog y hago un select a la hoja, de aquello lleno un DataTable.

    2-luego recorro el datatable con un for each (como detallo abajo) para ir insertando cada una de las filas.

    Bien, el problema esta que cuando cargo 65000 filas (15 columnas) se demora  35 minutos y en ocasiones tendré que cargar 200 mil filas y se demoraria bastante. 

    Habrá una forma de cargar estos datos de manera mas optima y rápida?

                Dim i As Integer = 0
                Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conexionVule").ToString()) 
                    conn.Open()
    
                    Dim sql As String = "INSERT INTO IndGestion.ReporteDosDos " & _
                                        "VALUES (@FecPostControlVirtual,  " & _
                                        "@HorPostControlVirtual,  " & _
                                        "@FecAntControlVirtual,  " & _
                                        "@HorAntControlVirtual,  " & _
                                        "@KmPostControlVirtual,  " & _
                                        "@KmAntControlVirtual,  " & _
                                        "@Patente,  " & _
                                        "@UnidadNegocio,  " & _
                                        "@IdRuta,  " & _
                                        "@Ruta,  " & _
                                        "@Servicio,  " & _
                                        "@KmRutaControlVirtual,  " & _
                                        "@FecInicioRuta,  " & _
                                        "@PosDeteccionBus,  " & _
                                        "@ServicioTs)"
    
                    Dim Command As New SqlCommand(sql, conn)
                    Dim resultado As Integer
                    For Each row As DataRow In dt.Rows
                        Command.Parameters.Clear()
                        Command.Parameters.AddWithValue("FecPostControlVirtual", CDate(row("F1")).ToShortDateString)
                        Command.Parameters.AddWithValue("HorPostControlVirtual", CDate(row("F1")).ToShortTimeString)
                        Command.Parameters.AddWithValue("FecAntControlVirtual", CDate(row("F3")).ToShortDateString)
                        Command.Parameters.AddWithValue("HorAntControlVirtual", CDate(row("F3")).ToShortTimeString)
                        Command.Parameters.AddWithValue("KmPostControlVirtual", CInt(row("F4")))
                        Command.Parameters.AddWithValue("KmAntControlVirtual", CInt(row("F5")))
                        Command.Parameters.AddWithValue("Patente", CStr(row("F6")))
                        Command.Parameters.AddWithValue("UnidadNegocio", CStr(row("F7")))
                        Command.Parameters.AddWithValue("IdRuta", CInt(row("F8")))
                        Command.Parameters.AddWithValue("Ruta", CStr(row("F9")))
                        Command.Parameters.AddWithValue("Servicio", CStr(row("F10")))
                        Command.Parameters.AddWithValue("KmRutaControlVirtual", CInt(row("F12")))
                        Command.Parameters.AddWithValue("FecInicioRuta", CDate(row("F13")))
                        Command.Parameters.AddWithValue("PosDeteccionBus", CStr(row("F15")))
                        Command.Parameters.AddWithValue("ServicioTs", CStr(row("F16")))
                        resultado = Convert.ToInt32(Command.ExecuteNonQuery())
                        i += 1
                        Me.SetValue_bpProgreso(i)'Informo el progreso de la operación
                        Me.SetValue_CantidadRegistros(i)
                    Next
                End Using
    Saludos


    Mauricio Hernández


    • Editado mauriciohz viernes, 22 de junio de 2012 18:31 falta info...
    viernes, 22 de junio de 2012 18:30

Respuestas

  • "mauriciohz" preguntó:

    > Solo me queda una duda con esta linea de código, no se como
    > implementarla ya que me pide un RaiseEvent
    >
    > bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(AddressOf CbulkCopy_SqlRowsCopied)

    Esa línea muestra cómo se instala en C# un controlador para el evento SqlRowsCopied del objeto SqlBulkCopy. En Visual Basic tendrías que instalarlo mediante la instrucción AddHandler.

      ' Creamos el objeto SqlBulkCopy
      '
      Dim bulkCopy As New SqlBulkCopy(conn)
    
      ' Instalamos el controlador para el evento SqlRowsCopied
      '
      AddHandler bulkCopy.SqlRowsCopied, AddressOf bulkCopy_SqlRowsCopied

    Y como tienes implementado en tu proyecto un procedimiento llamado bulkCopy_SqlRowsCopied, éste se ejecutará cada vez que se haya procesado el número de filas especificado en la propiedad NotifyAfter, en tu caso, cada vez que se añadan 10.000 filas, porque para eso has especificado el siguiente valor:

      bulkCopy.NotifyAfter = 10000

    Te advierto que si no se han insertado 10.000 filas (o el valor especificado en la propiedad NotifyAfter) nunca se desencadenará el evento SqlRowsCopied.


    Enrique Martínez
      [MS MVP - VB]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, activa la instrucción Option Strict.


    domingo, 24 de junio de 2012 9:33
    Moderador
  • Con SqlBulkCopy podria solucionar esto de manera mas sencilla
    • Marcado como respuesta mauriciohz sábado, 23 de junio de 2012 20:08
    viernes, 22 de junio de 2012 20:47

Todas las respuestas

  • Y esto lo tienes que hacer mucha veces?

    Por ahi y te puede servir lo que se describe en este video.

    http://www.youtube.com/watch?v=gKTglfmWlIc

     Yo personalmente he importado hasta 115000 registros sin tener ningun problema...

    Saludos!


    Luis Escobar :D

    viernes, 22 de junio de 2012 18:56
  • Con SqlBulkCopy podria solucionar esto de manera mas sencilla
    • Marcado como respuesta mauriciohz sábado, 23 de junio de 2012 20:08
    viernes, 22 de junio de 2012 20:47
  • "mauriciohz" escribió:

    > Necesito hacer una carga masiva de datos desde excel,

    Para insertar en una tabla de SQL Server los datos existentes en una hoja de cálculo de un libro de Excel, no es necesario rellenar un objeto DataTable ni recorrer posteriormente sus filas para insertar registros uno a uno.

    Lo puedes hacer de una manera eficaz y rapidísima mediante la utilización conjunta del ISAM de Excel del motor Microsoft Jet (hasta Excel 2003) o Microsoft ACE (Excel 2007-2010) junto con el driver ODBC de SQL Server, tal y como muestra la siguiente función:

        Private Function ExportToSqlServer(rutaLibro As String) As Integer
    
            Try
                ' Establecemos una conexión con el libro de Excel 2007/2010
                '
                Using cnn As New OleDbConnection( _
                    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Extended Properties='Excel 12.0 Xml;HDR=Yes';" & _
                    "Data Source = " & rutaLibro)
    
                    Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Construimos la consulta INSERT INTO, indicando nuestra
                    ' intención de conectarnos con SQL Server mediante su
                    ' driver ODBC.
                    '
                    cmd.CommandText = _
                        "INSERT INTO [IndGestion.ReporteDosDos] " & _
                                      "IN ''[ODBC;Driver={SQL Server};" & _
                                      "Server=(local);Database=Prueba;UID=cuenta_usuario;PWD=contraseña_usuario]" & _
                                      "SELECT Campo1, Campo2, Campo3, Campo4, ... CampoN " & _
                                      "FROM [NombreHojaExcel$]"
    ' Abrimos la conexión. ' cnn.Open() ' Ejecutamos la consulta devolviendo el número ' de registros afectados. ' Return cmd.ExecuteNonQuery() End Using Catch ex As Exception ' Devolvemos la excepción al procedimiento llamador ' Throw End Try End Function

    Eso sí, los nombres de los campos que especifiques en la consulta SELECT se deberán de llamar igual tanto en la hoja de Excel como en la tabla de SQL Server. Ahora seguro que los nombres de la hoja de Excel no concuerdan con los nombres de los campos de la tabla de SQL Server. Si es así, lo único que te puedo decir es que los adaptes para que en los dos sitios sean idénticos. :-D

    Cuando deseas llevar a cabo la exportación de datos, llamarías a la función de la siguiente manera:

            Try
                Dim n As Integer = ExportToSqlServer("C:\Mis documentos\Libro1.xlsx")
    
                MessageBox.Show("Nº de registros exportados: " & CStr(n))
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            End Try

    Un saludo


    Enrique Martínez
      [MS MVP - VB]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, activa la instrucción Option Strict.


    sábado, 23 de junio de 2012 10:29
    Moderador
  • Hola 

    Ya encontré la solución de carga masiva de datos utilizando SqlBulkCopy, detallo el código y los link donde obtener la información, funciona correctamente;

    en muy pocos segundos (8 segundos aprox.) cargue 65000 filas de excel. (me queda una duda la cual explico mas abajo)

    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conexionVule").ToString()) conn.Open() Dim delegadoInicio As MethodInvoker delegadoInicio = New MethodInvoker(AddressOf MensajeCargaMasivaInicio) lbMensajeEstado.Invoke(delegadoInicio) Dim bulkCopy As New SqlBulkCopy(conn) bulkCopy.BatchSize = 100 'Número de filas en cada lote. Al final de cada lote, se envían las filas del lote al servidor. bulkCopy.BulkCopyTimeout = 10 'Número de segundos para que finalice la operación antes de que sobrepase el tiempo de espera. bulkCopy.DestinationTableName = "IndGestion.ReporteDosDos" 'Nombre de la tabla de destino en el servidor. 'bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(AddressOf CbulkCopy_SqlRowsCopied) 'Se produce cada vez que se ha procesado el número de filas especificado por la propiedad System.Data.SqlClient.SqlBulkCopy.NotifyAfter. bulkCopy.NotifyAfter = 10000 'Se produce cada vez que se ha procesado el número de filas especificado por la propiedad System.Data.SqlClient.SqlBulkCopy.NotifyAfter. bulkCopy.WriteToServer(dtResultado) 'Copia todas las filas de la System.Data.DataTable proporcionada a una tabla del server. Dim delegadoTermino As MethodInvoker delegadoTermino = New MethodInvoker(AddressOf MensajeCargaMasivaTermino) lbMensajeEstado.Invoke(delegadoTermino) End Using

     Private Sub bulkCopy_SqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
            MessageBox.Show(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()))
     End Sub

    recursos:

    http://dotnetslackers.com/articles/ado_net/SqlBulkCopy_in_ADO_NET_2_0.aspx ---> codigo completo

    http://adalmontania.blogspot.com/2009/07/sql-bulkcopy.html --> observaciones

    Solo me queda una duda con esta linea de código, no se como implementarla ya que me pide un RaiseEvent 

    'bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(AddressOf CbulkCopy_SqlRowsCopied)

    Saludos


    Mauricio Hernández

    sábado, 23 de junio de 2012 20:06
  • "mauriciohz" preguntó:

    > Solo me queda una duda con esta linea de código, no se como
    > implementarla ya que me pide un RaiseEvent
    >
    > bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(AddressOf CbulkCopy_SqlRowsCopied)

    Esa línea muestra cómo se instala en C# un controlador para el evento SqlRowsCopied del objeto SqlBulkCopy. En Visual Basic tendrías que instalarlo mediante la instrucción AddHandler.

      ' Creamos el objeto SqlBulkCopy
      '
      Dim bulkCopy As New SqlBulkCopy(conn)
    
      ' Instalamos el controlador para el evento SqlRowsCopied
      '
      AddHandler bulkCopy.SqlRowsCopied, AddressOf bulkCopy_SqlRowsCopied

    Y como tienes implementado en tu proyecto un procedimiento llamado bulkCopy_SqlRowsCopied, éste se ejecutará cada vez que se haya procesado el número de filas especificado en la propiedad NotifyAfter, en tu caso, cada vez que se añadan 10.000 filas, porque para eso has especificado el siguiente valor:

      bulkCopy.NotifyAfter = 10000

    Te advierto que si no se han insertado 10.000 filas (o el valor especificado en la propiedad NotifyAfter) nunca se desencadenará el evento SqlRowsCopied.


    Enrique Martínez
      [MS MVP - VB]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, activa la instrucción Option Strict.


    domingo, 24 de junio de 2012 9:33
    Moderador
  • Hola Enrique,

    Estoy agradecido por tu explicación y con esto se despejan las dudas que tenia,

    el código funciona perfecto

    Saludos


    Mauricio Hernández

    lunes, 25 de junio de 2012 13:29