none
importar datos de hoja de excel a tabla access desde mi formlario VB.NET 2012 RRS feed

  • Pregunta

  • Hola amigos

    Tengo mi aplicación desarrollada en VB.NET 2012 y mi base de datos Access. tengo un formulario que tiene el registro de clientes de la empresa (tabla con  23 campos), tenemos listados en Excel que nos llega frecuentemente, estos listados contienen muchos registros. Quisiera presionar un boton en mi formulario e importar los datos del archivo Excel a mi tabla de access; pero no tengo idea de como hacerlo. he buscado en los foros y encuentro ejemplos de iportar excel a SQL pero lo poco que encuentro de access no funcionan correctamente. podrían ayudarme con eso? gracias.

    viernes, 10 de octubre de 2014 22:28

Respuestas

  • "MateoDeveloper" preguntó:

    > Tengo mi aplicación desarrollada en VB.NET 2012 y mi base de datos Access.
    > tengo un formulario que tiene el registro de clientes de la empresa
    > (tabla con  23 campos), tenemos listados en Excel que nos llega frecuentemente,
    > estos listados contienen muchos registros. Quisiera presionar un boton en
    > mi formulario e importar los datos del archivo Excel a mi tabla de access;

    Hola, Mateo:

    Si la tabla de Access y la hoja de cálculo o rango de celdas del libro de Excel TIENEN EL MISMO NÚMERO Y NOMBRE DE CAMPOS, puedes pasar los datos de Excel a Access en un abrir y cerrar de ojos, tal y como muestro a continuación:

    Imports System.Data.OleDb
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            ' Establecemos una conexión con el archivo
             ' de Excel 2007 o superior.
             '
             Dim cadenaConexionExcel As String = _
                 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mis documentos\Libro1.xlsx;" & _
                 "Extended Properties='Excel 12.0 Xml;'"
    
            Try
                 Using cnn As New OleDbConnection(cadenaConexionExcel)
    
                    ' Creamos y configuramos el comando.
                     Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Indicamos que nuestra intención es insertar en la tabla Clientes
                     ' de la base de datos de Access todos los registros existentes en
                     ' la hoja de cálculo llamado Hoja1 del libro de Excel.
                     '
                     cmd.CommandText = "INSERT INTO Clientes IN 'C:\Mis documentos\Database1.accdb' " & _
                         "SELECT * FROM [Hoja1$]"
    
                    ' Abrimos la conexión
                     cnn.Open()
    
                    ' Ejecutamos la consulta INSERT INTO
                     Dim n As Integer = cmd.ExecuteNonQuery()
    
                    ' Mostramos el resultado
                     '
                     MessageBox.Show("Nº de registros insertados: " & CStr(n))
    
                End Using
    
            Catch ex As Exception
                 ' Se ha producido un error
                 MessageBox.Show(ex.Message)
    
            End Try
    
        End Sub

    Como podrás comprobar, ni tan siquiera es necesario que rellenes un objeto DataTable. Se comprende que tanto el libro de Excel como la base de Access se encuentran físicamente en alguna carpeta del PC del usuario.

    ¡Mas sencillo imposible! ;-)

    Un saludo


    Enrique Martínez Montejo
            [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, se inteligente y activa la instrucción
    Option Strict.


    sábado, 11 de octubre de 2014 10:32
    Moderador
  • "alexcq1" preguntó:

    > y como seria alrevez de access a excel

    Hola:

    El siguiente ejemplo establece una conexión con una base de datos de Access versión 2007 o superior para exportar los datos de una tabla cualquiera a un nuevo libro de Excel versión 2007 o superior. Si no existe el libro de Excel, se creará uno nuevo.

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            ' Establecemos una conexión con el archivo de Access 2007 o superior.
            '
            Dim cadenaConexion As String =
                 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mis documentos\Database1.accdb"
    
            Try
                Using cnn As New OleDbConnection(cadenaConexion)
    
                    ' Creamos y configuramos el comando.
                    Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Indicamos que nuestra intención es crear un nuevo libro de Excel
                    ' versión 2007 o superior con los datos de la tabla Clientes existente
                    ' en el archivo de Access.
                    '
                    cmd.CommandText =
                        "SELECT * INTO [NombreRangoExcel] IN ''[Excel 12.0 Xml;DATABASE=C:\Mis documentos\Libro1.xlsx] " &
                        "FROM [Clientes]"
    
                    ' Abrimos la conexión
                    cnn.Open()
    
                    ' Ejecutamos la consulta de creación de tabla
                    Dim n As Integer = cmd.ExecuteNonQuery()
    
                    ' Mostramos el resultado
                    '
                    MessageBox.Show(String.Format("Nº de registros exportados: {0}", n))
    
                End Using
    
            Catch ex As Exception
                ' Se ha producido un error
                MessageBox.Show(ex.Message)
    
            End Try
    
        End Sub

    Si todo ha ido bien, se habrá creado en el Libro1.xlsx una nueva hoja de cálculo, y un nuevo rango de celdas con nombre, llamados ambos 'NombreRangoExcel'.

    Para más información sobre el ISAM de Excel, consulta el siguiente artículo:

    Trabajar con ADO, DAO y Excel

    Dicho artículo lo escribí en su día para los usuarios de Visual Basic clásico, por lo que lo puedes adaptar perfectamente a Visual Basic .net, ya que las consultas SQL son iguales para ambas versiones de Visual Basic.

    Un saludo


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    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, se inteligente y activa la instrucción
    Option Strict.


    sábado, 18 de marzo de 2017 10:28
    Moderador

Todas las respuestas

  • Hola, ahi te va una idea para ke tomes rumbo

    // te conectas a tu bd de access con una consulta de registros ke no existen solo para obtener la estructura en un DataSet ejemplo SELECT * FROM tuTablaAccess WHERE idCliente = -9 (puse -9 pero puedes usar un dato ke seguramente no exista, la idea es traer las cabeceras de tu tabla)

    // paso 2 te conectas a tu excel con oledb por ejemplo obtienes los datos 

    // con un foreach iteras por los datos de tu excel metiendo validaciones a gusto mientras las vas incorporando al dataset ligado a tu access, u algun otro metodo ke gustes

    //cuando termine el loop realizas los cambios en el bd conectada a tu access y ya, bueno es una idea para rumbear el barco seguramente hay otras alternativas

    ejemplo de conectarse a un excel es asi

    OleDb.OleDbConnection MyConnection ;
    DataSet DtSet ;
    OleDbDataAdapter MyCommand ;
    MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;DataSource='c:\tuarchivo.xls';Extended Properties=Excel 8.0;");
    MyCommand = new OleDbDataAdapter("select * from Sheet1$]",MyConnection);
    MyCommand.TableMappings.Add("Table", "TestTable");
    DtSet = new System.Data.DataSet();
    MyCommand.Fill(DtSet);
    MyConnection.Close();

    Saludos


    G. Poliovei

    viernes, 10 de octubre de 2014 23:06
  • "MateoDeveloper" preguntó:

    > Tengo mi aplicación desarrollada en VB.NET 2012 y mi base de datos Access.
    > tengo un formulario que tiene el registro de clientes de la empresa
    > (tabla con  23 campos), tenemos listados en Excel que nos llega frecuentemente,
    > estos listados contienen muchos registros. Quisiera presionar un boton en
    > mi formulario e importar los datos del archivo Excel a mi tabla de access;

    Hola, Mateo:

    Si la tabla de Access y la hoja de cálculo o rango de celdas del libro de Excel TIENEN EL MISMO NÚMERO Y NOMBRE DE CAMPOS, puedes pasar los datos de Excel a Access en un abrir y cerrar de ojos, tal y como muestro a continuación:

    Imports System.Data.OleDb
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            ' Establecemos una conexión con el archivo
             ' de Excel 2007 o superior.
             '
             Dim cadenaConexionExcel As String = _
                 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mis documentos\Libro1.xlsx;" & _
                 "Extended Properties='Excel 12.0 Xml;'"
    
            Try
                 Using cnn As New OleDbConnection(cadenaConexionExcel)
    
                    ' Creamos y configuramos el comando.
                     Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Indicamos que nuestra intención es insertar en la tabla Clientes
                     ' de la base de datos de Access todos los registros existentes en
                     ' la hoja de cálculo llamado Hoja1 del libro de Excel.
                     '
                     cmd.CommandText = "INSERT INTO Clientes IN 'C:\Mis documentos\Database1.accdb' " & _
                         "SELECT * FROM [Hoja1$]"
    
                    ' Abrimos la conexión
                     cnn.Open()
    
                    ' Ejecutamos la consulta INSERT INTO
                     Dim n As Integer = cmd.ExecuteNonQuery()
    
                    ' Mostramos el resultado
                     '
                     MessageBox.Show("Nº de registros insertados: " & CStr(n))
    
                End Using
    
            Catch ex As Exception
                 ' Se ha producido un error
                 MessageBox.Show(ex.Message)
    
            End Try
    
        End Sub

    Como podrás comprobar, ni tan siquiera es necesario que rellenes un objeto DataTable. Se comprende que tanto el libro de Excel como la base de Access se encuentran físicamente en alguna carpeta del PC del usuario.

    ¡Mas sencillo imposible! ;-)

    Un saludo


    Enrique Martínez Montejo
            [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, se inteligente y activa la instrucción
    Option Strict.


    sábado, 11 de octubre de 2014 10:32
    Moderador
  • Enrique

    Gracias por el apoyo. ejecute tu código y me salio mensaje de error "Ha intentado asignar valor Null a una variable que no es tipo de dato Variant".mi archivo excel tiene todas las celdas completas según los tipos de campos de la tabla en access. modifique un poco tu codigo, podrias ayudarme please?


    lunes, 13 de octubre de 2014 16:29
  • "MateoDeveloper" escribió:

    > ejecute tu código y me salio mensaje de error "Ha intentado asignar valor Null
    > a una variable que no es tipo de dato Variant".mi archivo excel tiene todas las
    > celdas completas según los tipos de campos de la tabla en access.

    Pues lo siento por tí, porque yo soy incapaz de reproducir el error que comentas. Entiendo que debe existir alguna incompatibilidad entre los tipos de datos de las columnas de Excel y los campos de la tabla de Access, pero sin ver los datos y los esquemas del archivo de Excel y Access, no tengo ni la más remota idea del motivo que obtengas dicho error. ¿?

    Siempre que el esquema de las columnas de la hoja de Excel se corresponda EXACTAMENTE con el esquema de campos de la tabla de Access, así como los tipos de datos de los valores de uno y otro, no tienes por qué obtener error alguno.

    Lo siento.


    Enrique Martínez Montejo
            [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, se inteligente y activa la instrucción
    Option Strict.

    lunes, 13 de octubre de 2014 18:51
    Moderador
  • Enrique

    Corregí el problema, era mi tabla de Access, se trataba de un campo tipo numero que pensé que era texto el mismo que esta relacionado con otra tabla. Tu código funciona perfectamente. Gracias por el apoyo!.

    Saludos.

    martes, 14 de octubre de 2014 17:38
  • y como seria alrevez de access a excel
    viernes, 17 de marzo de 2017 19:21
  • "alexcq1" preguntó:

    > y como seria alrevez de access a excel

    Hola:

    El siguiente ejemplo establece una conexión con una base de datos de Access versión 2007 o superior para exportar los datos de una tabla cualquiera a un nuevo libro de Excel versión 2007 o superior. Si no existe el libro de Excel, se creará uno nuevo.

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            ' Establecemos una conexión con el archivo de Access 2007 o superior.
            '
            Dim cadenaConexion As String =
                 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mis documentos\Database1.accdb"
    
            Try
                Using cnn As New OleDbConnection(cadenaConexion)
    
                    ' Creamos y configuramos el comando.
                    Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Indicamos que nuestra intención es crear un nuevo libro de Excel
                    ' versión 2007 o superior con los datos de la tabla Clientes existente
                    ' en el archivo de Access.
                    '
                    cmd.CommandText =
                        "SELECT * INTO [NombreRangoExcel] IN ''[Excel 12.0 Xml;DATABASE=C:\Mis documentos\Libro1.xlsx] " &
                        "FROM [Clientes]"
    
                    ' Abrimos la conexión
                    cnn.Open()
    
                    ' Ejecutamos la consulta de creación de tabla
                    Dim n As Integer = cmd.ExecuteNonQuery()
    
                    ' Mostramos el resultado
                    '
                    MessageBox.Show(String.Format("Nº de registros exportados: {0}", n))
    
                End Using
    
            Catch ex As Exception
                ' Se ha producido un error
                MessageBox.Show(ex.Message)
    
            End Try
    
        End Sub

    Si todo ha ido bien, se habrá creado en el Libro1.xlsx una nueva hoja de cálculo, y un nuevo rango de celdas con nombre, llamados ambos 'NombreRangoExcel'.

    Para más información sobre el ISAM de Excel, consulta el siguiente artículo:

    Trabajar con ADO, DAO y Excel

    Dicho artículo lo escribí en su día para los usuarios de Visual Basic clásico, por lo que lo puedes adaptar perfectamente a Visual Basic .net, ya que las consultas SQL son iguales para ambas versiones de Visual Basic.

    Un saludo


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    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, se inteligente y activa la instrucción
    Option Strict.


    sábado, 18 de marzo de 2017 10:28
    Moderador
  • hermano muchisimas graciasss e aprendido algo nuevo,  te debo una cerveza :)
    martes, 21 de marzo de 2017 15:31
  • Hola entiendo tu código pero quería saber si se puede elegir la dirección y el nombre de la tabla ya que mi archivo dispone de 5 tablas diferentes
    martes, 1 de agosto de 2017 5:00
  • hola, buena tarde, este ejemplo esta excelente!! me sirvió mucho, solo que tengo una duda, como puedo hacer para que me pase los datos de Excel a Access sin importar los nombres de campos que tiene mi base de datos de Access?

    el detalle es que mi archivo de Excel tiene un nombre de columna, un ejemplo [ventas jul 2017] y en Access mi campo se llama [Venta Actual] al querer pasar los datos como el ejemplo me marca error, lo que no quiero es tener que renombrar los campos de Excel o de Access.

    espero haberme explicado, muchas gracias.

    saludos.

    jueves, 10 de agosto de 2017 17:39
  • "AaronG_Mty" preguntó:

    > como puedo hacer para que me pase los datos de Excel a Access sin importar los
    > nombres de campos que tiene mi base de datos de Access?
    >
    > el detalle es que mi archivo de Excel tiene un nombre de columna, un ejemplo
    > [ventas jul 2017] y en Access mi campo se llama [Venta Actual] al querer pasar
    > los datos como el ejemplo me marca error, lo que no quiero es tener que renombrar
    > los campos de Excel o de Access.

    Hola:

    Con independencia que el origen de los datos sea una hoja de Excel, SQL Server, un archivo de texto delimitado, otra base de datos de Access, etc., lo que pretendes hacer no se puede, al menos hasta donde llegan mis conocimientos sobre el ISAM de Excel. Es como si me dices que deseas insertar los datos de una tabla Clientes en una tabla Facturas mediante la ejecución de una consulta INSERT INTO donde el origen de los datos es otra consulta SQL de selección con alguna sintaxis parecida a las siguientes:

        INSERT INTO Tabla1 SELECT * FROM NombreTabla
        INSERT INTO Tabla1 SELECT [ventas jul 2017], [ventas ago 2017], [ventas sep 2017] FROM NombreTabla

    Los nombres de los campos tienen que tener el mismo número de campos y el mismo nombre, siempre y cuando, repito, desees ejecutar la consulta INSERT INTO que aparece en el ejemplo de más arriba:

        ' Indicamos que nuestra intención es insertar en la tabla Clientes
        ' de la base de datos de Access todos los registros existentes en
        ' la hoja de cálculo llamado Hoja1 del libro de Excel.
        '
        cmd.CommandText = "INSERT INTO Clientes IN 'C:\Mis documentos\Database1.accdb' " & _
                          "SELECT * FROM [Hoja1$]"

    Aunque comentas en tu mensaje que no quieres renombrar los campos de Excel, entiendo que eso mismo es lo que yo haría para salir airoso del problema planteado, y más sabiendo de antemano que tengo que importar a Access los datos existentes en Excel, por lo que haría que los nombres de la hoja o rango de Excel coincidieran con los nombres de la tabla de Access donde deseo insertar los datos.

    Te lo comento porque personalmente no le veo ningún sentido a que el nombre de un campo lleve el nombre de un mes cualquiera, porque se supone que esa tabla deberá contener un campo del tipo Fecha/Hora (DateTime) que nos indique la fecha del día en la que se ha realizado la venta, para que posteriormente podamos mostrar un informe con las ventas realizadas en un mes concreto.

    Todo es cuestión de tener diseñado correctamente tanto la hoja de Excel como la tabla de la base de datos de Access. ;-)

    Un saludo


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    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, se inteligente y activa la instrucción
    Option Strict.





    viernes, 11 de agosto de 2017 16:55
    Moderador

  • Hola, Mateo:

    Si la tabla de Access y la hoja de cálculo o rango de celdas del libro de Excel TIENEN EL MISMO NÚMERO Y NOMBRE DE CAMPOS, puedes pasar los datos de Excel a Access en un abrir y cerrar de ojos, tal y como muestro a continuación:

    Imports System.Data.OleDb
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            ' Establecemos una conexión con el archivo
             ' de Excel 2007 o superior.
             '
             Dim cadenaConexionExcel As String = _
                 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Mis documentos\Libro1.xlsx;" & _
                 "Extended Properties='Excel 12.0 Xml;'"
    
            Try
                 Using cnn As New OleDbConnection(cadenaConexionExcel)
    
                    ' Creamos y configuramos el comando.
                     Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Indicamos que nuestra intención es insertar en la tabla Clientes
                     ' de la base de datos de Access todos los registros existentes en
                     ' la hoja de cálculo llamado Hoja1 del libro de Excel.
                     '
                     cmd.CommandText = "INSERT INTO Clientes IN 'C:\Mis documentos\Database1.accdb' " & _
                         "SELECT * FROM [Hoja1$]"
    
                    ' Abrimos la conexión
                     cnn.Open()
    
                    ' Ejecutamos la consulta INSERT INTO
                     Dim n As Integer = cmd.ExecuteNonQuery()
    
                    ' Mostramos el resultado
                     '
                     MessageBox.Show("Nº de registros insertados: " & CStr(n))
    
                End Using
    
            Catch ex As Exception
                 ' Se ha producido un error
                 MessageBox.Show(ex.Message)
    
            End Try
    
        End Sub

    Como podrás comprobar, ni tan siquiera es necesario que rellenes un objeto DataTable. Se comprende que tanto el libro de Excel como la base de Access se encuentran físicamente en alguna carpeta del PC del usuario.

    ¡Mas sencillo imposible! ;-)ddfd

    Un saludo

    hola muy buenas, estoy probando este codigo y funciona muy bien, pero me surge un problema, he conectado un access al proyecto por origen de datos y cuando inserto los datos desde el archivo excel al access, hasta ahí todo bien pero luego no me actualiza el datagridwied hasta que reinicio aplicacion y he probado varias formas pero nada. No se  que estoy haciendo mal




    sábado, 30 de septiembre de 2017 18:14