Insertar datos desde excel a sql

Respuesta de
a
Avatar
Usuario
Hola..
Tengo una desarrollo web en visual B 2008 y SQL 2005.
Ahora estoy en la parte en que necesito subir un archivo en excel (CSV) por medio de un explorador de ventanas. Ya lo hice y lo almaceno en una carpeta temporal en el servidor; En este momento estoy frenado porque no se como tomar todos los datos de la hoja de excel e ingresarlos en todas las tablas de BD SQL. Puesto que necesito hacer operaciones con los datos de excel.
Si me puede ayudar, ¿Puedes ser lo más explicito posible?
Mil gracias...
Avatar
Experto
Bueno en respuesta a tu pregunta existen 3 formas relativamente similares para realizar dicha acción:
Una es creando desde el archivo de excel automáticamente la tabla (el problema que no siempre asigna los valores recibidos como los deseamos)
SELECT * INTO Tu_Tabla  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;
DataBase=C:\ArchivoExCel.xls;',
'SELECT * FROM [Hoja1$]')
 
Y otra es añadiendo la información a una tabla previamente creada (problema con esta opción que se le debe de dar un tratamiento especial a las hojas de excel a que me refiero con esto a que le debes de eliminar todo formato, espacio en blanco o carácter que no concuerde con las especificaiones necesarias de los tipos de datos o marcara error y el código para ello es:
 
insert Tu_Tabla  select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\NOMBRE_DEL_ARCHIVO.xls;HDR=YES','SELECT * FROM [Hoja1$]')
con esta tercera instruccion deberas ya tener definida la estructura de tu tabla.
Te dejo estos link para que veas más información sobre estas instruccciones (solo sql server 2008 o posterior):
BULK INSERT temp FROM C:\ArchivoExel.xls
WITH (FIELDTERMINATOR =';',ROWTERMINATOR ='/', FIRSTROW = 2)
http://technet.microsoft.com/es-es/library/ms188365.aspx
http://technet.microsoft.com/es-es/library/ms190312.aspx
 
Si no te funciona avisame y buscamos otra solución
Avatar
Usuario
Hola.
Gracias por la pronta respuesta. Te pido un consejo, lo que sucede es que la idea de mi aplicación ( tesis de la universidad), lo que debe hacer es cargar la base de datos que llega en excel para así poder asignar los productos en los almacenes que van a entregar los mensajeros por medio de números de guía. La idea es saber que mensajero tiene asignado cierto almacen; pero antes de asignar los números de guía debe haber una validación si realmente existe la guía. La base de datos en excel estaría conformada(para el sistema) por cuatro columnas: Producto, edición, destinatario, numero de guía. La verdad te dgio que no se como resulta más fácil hacer toda esta operación y me esta quemando neuronas. Crees que se puede optimizar el procedimiento de alguna otra forma o que haga lo mismo con menos complicación.?
Gracias...
Avatar
Experto
Un pregunta estas utilizando algún lenguaje de programación o la programación es integran en sql por que siento que lo que podrías hacer (si es que programas con un lenguaje es hacer estos cuestionamientos y comparaciones dentro del programa y unicamente en sql haces el llamado de la hoja y respectivas consultas pues seria un proceso complicado en sql hacer la transacción que teverifique dicha información dentro del sql) si no pues seria empezar por el comienzo primero sera cargar la información en una tabla temporal (o creada como almacen por así decirlo), después comparar cada una de las guías y ver cual si existe y cual no, y en base a eso pues decidir hacia donde se va la información -- el problema aquí es que si el sistema va a estar en red saber cuando un usuario ha cargado su tabla y hacer la cola de espera para que no se sobre escriban o empalmen datos.
Bueno al menos se me ocurre esa idea en este momento pero pues en base a tu respuesta veremos cual es lo que más te conviene espero que algo de esto te sea de ayuda y pues avíseme para buscar la opción más viable
Avatar
Usuario
Gracias por la respuesta..
Si mira estoy progrmanado en visual b 2008, y ya hice la parte donde subo el archivo(Lo hago en una carpeta temporal en el servidor) . La idea es tomar los datos de ahí y pasarlos a las tablas SQL, después mediante un modulo de asignación, validar que el numero de guía a asignar esta en la base de datos. Y listo.. Suena fácil ... el problema es que me quede en la parte de subir el archivo al servidor... je je... lo demás lo estoy intentando hacer... Pero se que con la ayuda que me diste se va aclara un poco el panorama...
¿De pronto tienes alguna otra cosita para decirme por ahí antes calificar la respuesta?.. je je
Gracias de nuevo...
Avatar
Experto
Ok acabo de entender tu pregunta mira te puedo pasar un ejemplo ya que yo hace mucho tiempo hice algo parecido para una empresa de seguridad privada solo que yo lo hice en c# ya que VB se me hace una bufonada y este carga los datos y los almacena directamente en una base de datos solo quedaría que vieras la manera de verificar el numero pero eso se me hace relativamente fácil pues solo con una consulta lo realizas
//VERIFICA LA EXTENSION DEL ARCHIVO QUE SE DESEA SUBIR
    protected Boolean ChecarExtension(string extension)
    {
        switch (extension.ToLower())
        {
            case ".xlsx":
                return true;
            case ".xls":
                return true;
            default:
                return false;
        }
    }
    //GUARDA EL ARCHIVO EN LA CARPETA
    private void Cargar(FileUpload archivo, HttpRequest instancia, GridView tabla)
    {
        string carpeta = "Archivos\\";
        string directorioGral = instancia.PhysicalApplicationPath;
        try
        {
            if (archivo.HasFile)
            {
                string extension = Path.GetExtension(archivo.FileName);
                if (ChecarExtension(extension))
                {
                    string directorioParaGuardar = directorioGral + carpeta + archivo.FileName;
                    EliminarArchivos(instancia);
                    archivo.SaveAs(directorioParaGuardar);
                    lblarchivo.Text = "El archivo fue copiado exitosamente" + "<br />" + "Tamaño del Archivo: " + archivo.FileBytes.Length + " Bytes"
                        + "<br />" + "Nombre del Archivo: " + FileUpload1.FileName;
                    tabla.DataSource =GuardarExcel(directorioParaGuardar);
                    tabla.DataBind();
                }
                else
                    WebMsgBox.Show("Solo se admiten archivos de Excel 97-2003 o 2007");
            }
        }
        catch (Exception ex)
        {
            lblalgo.Text = "Error al cargar el archivo " + ex;
        }
    }
    //SI EXISTE EL ARCHIVO QUE SE DESEA GUARDAR LO ELIMINA PARA CREAR UNO NUEVO
    private void EliminarArchivos(HttpRequest instancia)
    {
        string carpeta = "Archivos\\";
        string directorioGral = instancia.PhysicalApplicationPath + carpeta;
        DirectoryInfo directorio = new DirectoryInfo(directorioGral);
        FileInfo[] archivos = directorio.GetFiles();
        foreach (FileInfo archivo in archivos)
        {
            archivo.Delete();
        }
    }
    //HACE UNA CONSULTA AL ARCHIVO, LO CARGA Y LLAMA AL PROCEDIMIENTO PARA ALMACENAR
    protected DataTable GuardarExcel(string ruta)
    {
        string ccn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", ruta);
        Recursos WB = new Recursos();
        OleDbConnection cn = new OleDbConnection(ccn);
        DataTable dt = new DataTable();
        try
        {
            cn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Hoja1$]", cn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            WB.GuardaDatos(dt);
            da.Dispose();
            cmd.Dispose();
        }
        catch (Exception ex)
        {
            lblalgo.Text = "Existe un problema en el formato del archivo de entrada" + "<br />" + "Corriga el problema antes de subir el archivo" + "<br />" + ex.Message;
        }
        finally
        {
            cn.Close();
            cn.Dispose();
        }
        return dt;
    }
    //EVENTO EL CUAL LLAMA AL PROCEDIMIENTO PARA CARGAR EL ARCHIVO <<ESTE ES UN FILE UPLOAD
    protected void bntDatos_Click1(object sender, EventArgs e)
    {
        Cargar(FileUpload1, this.Request, this.dgUser);
    }
AHORA EN UN WEBSERVICE TENGO ESTA QUE ES LA CONEXION AL SERVIDOR
SqlConnection cn = new SqlConnection("Data source=TODOS\\SQL;Initial Catalog=Temach; Integrated Security=TRUE");
//PROCEDIMIENTO QUE ALMACENA EL REGISTRO EN LA BASE DE DATOS
    public void GuardaDatos(DataTable dt)
    {
        string Delito, string Organización_Delictiva, string Nombre, string Apellido_Paterno;
int c = 0;
        foreach (DataRow row in dt.Rows)
        {             
            Delito = Convert.ToString(dt.Rows[c][0]).ToUpper();
            Organización_Delictiva = Convert.ToString(dt.Rows[c][1]).ToUpper();
            Nombre = Convert.ToString(dt.Rows[c][2]).ToUpper();
            Apellido_Paterno = Convert.ToString(dt.Rows[c][3]).ToUpper();
            Apellido_Materno = Convert.ToString(dt.Rows[c][4]).ToUpper();
SqlCommand cmd = new SqlCommand("GuardaDatos", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Caso", SqlDbType.NVarChar, 30).Value = Caso;
            cmd.Parameters.Add("@Organizacion", SqlDbType.NVarChar, 30).Value = Organización_Delictiva;
            cmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 30).Value = Nombre;
            cmd.Parameters.Add("@Paterno", SqlDbType.NVarChar, 30).Value = Apellido_Paterno;
            cmd.Parameters.Add("@Materno", SqlDbType.NVarChar, 30).Value = Apellido_Materno;
cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            c += 1;
Ahora en el sql solo haces un procedimiento almacenado en el cual apliques un insert con los parámetros que mandes y listo
Y ahora que ya esta dentro de la bd la información pues solo has una consulta para verificar si existe el numero ingresado y listo