How to pass excel data to a SQLServer table with visual c #?

Question

How to pass excel data to a SQLServer table with visual c #?

Navigation

#1 by (1 votes)

1

I have the following code on a button, but when I run the program I get the following error:

Format of the initialization string does not conform to specification starting at index 54. no controo argument exeption:

Here is the code

        private void button3_Click(object sender, EventArgs e)
        {
        string conexion = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Users\MICUENTA\Libraries\Documents\Copy.xlsx;Extended Properties=Excel 12.0;";

        OleDbConnection origen = default(OleDbConnection);
        origen = new OleDbConnection(conexion);


        OleDbCommand seleccion = default(OleDbCommand);
        seleccion = new OleDbCommand("select * from [Sheet1$]", origen);

        OleDbDataAdapter adaptador = new OleDbDataAdapter();
        adaptador.SelectCommand = seleccion;

        DataSet ds = new DataSet();
        adaptador.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        origen.Close();
        SqlConnection conexion_destino = new SqlConnection();
        conexion_destino.ConnectionString = ("MI BASE");
        conexion_destino.Open();
        SqlBulkCopy importar = default(SqlBulkCopy);
        importar = new SqlBulkCopy (conexion_destino);
        importar.DestinationTableName = "tbl_Volumen2";
        importar.WriteToServer(ds.Tables[0]);
        conexion_destino.Close();
    }

sql c#

asked by abi 19.04.2017 в 16:34

source

1 answer

Encode base64 Java image Generate elements dynamically in a template in Django

score 1 · Answer 1

I have taken as a basis the code that you published and I have made some modifications.

1.- Download and install the data access component:

Access Database Engine

2.- Create a table in SQL:

CREATE TABLE [dbo]. [Test] ( [Col1] nvarchar NULL, [Col2] nvarchar NULL, [Col3] nvarchar NULL, [Col4] nvarchar NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

3.- In Excel create a file called Copy.xls with the same structure as the SQL table and add data:

4.- In the OnClick event of the button that we have to read the Excel file add the code that you published but with some changes:

protected void btnReadExcelFile_Click(object sender, EventArgs e)
{        
    string conexion = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\SAND\Libraries\Documents\Copy.xlsx;Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1&quot;";

    OleDbConnection origen =  new OleDbConnection(conexion);

    OleDbCommand seleccion = default(OleDbCommand);
    seleccion = new OleDbCommand("select * from [Sheet1$]", origen);

    OleDbDataAdapter adaptador = new OleDbDataAdapter();
    adaptador.SelectCommand = seleccion;

    DataSet ds = new DataSet();
    adaptador.Fill(ds);
    GvParaGuardarValoresDeExcel.DataSource = ds.Tables[0];
    GvParaGuardarValoresDeExcel.DataBind();
    origen.Close();
    SqlConnection conexion_destino = new SqlConnection();
    conexion_destino.ConnectionString = (@"Data Source=D4MW6WZ1\SAND;Initial Catalog=Test;User ID=usr_Framework;Password=usr_Framework");
    conexion_destino.Open();
    SqlBulkCopy importar = default(SqlBulkCopy);
    importar = new SqlBulkCopy(conexion_destino);
    importar.DestinationTableName = "Prueba";
    importar.WriteToServer(ds.Tables[0]);
    conexion_destino.Close();
}

Important: You must have the following namespaces:

using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient;