• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2710
  • Last Modified:

import excel to sql

I am trying to insert records into an existing sql table from an excel file in c# asp.net.  I would like to only insert specifi columns (both from the excel table and to the sql table) and specific rows (from the excel file).  All the help I could find was
insert into <sql> select * from <excel>

The error I receive is error in the INSERT INTO statement, but no details on what is failing.  See attached code.
if (FileUpload1.FileName == "")
                return;
 
            FileUpload1.SaveAs("c:\\EmployeeExpenses.xls");
 
            SqlConnection SQLConn = new SqlConnection("Data Source=KSSQL01;Initial Catalog=stamp;Integrated Security=SSPI");
            SQLConn.Open();
            SqlCommand SQLCommand = new SqlCommand("Delete tempEmployeeExpenses", SQLConn);
            SQLCommand.ExecuteNonQuery();
            SQLConn.Close();
 
            OleDbConnection ExcelConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\EmployeeExpenses.xls;Extended Properties=Excel 8.0;");
            ExcelConnection.Open();
            OleDbCommand ExcelCommand = new OleDbCommand(
                "INSERT INTO [ODBC;Driver={SQL Server};Server=KSSQL01;Database=stamp;Trusted_ Connection=yes].[tempEmployeeExpenses] "+
                "(Date, Description, CardMember, Amount) "+
                "SELECT [Date], [Description], [CardMember], [Amount] "+
                "FROM [Sheet1$] "+
                "WHERE [Sheet1$].[CardMember] = \"" + CardHolderName + "\";", ExcelConnection);
                
            ExcelCommand.ExecuteNonQuery();
            ExcelConnection.Close();

Open in new window

0
KS_Mis
Asked:
KS_Mis
  • 3
1 Solution
 
vbturboCommented:
well this reads an entire sheet into a dataset and the inserts the table into a sql database

public class Form1 {
   
    // Variables set for the form
    private OpenFileDialog dlgFile;
   
    private string filePath;
   
    private string fileNameNoPath;
   
    private string connectionString;
   
    private string fileNameNoExt;
   
    private DataRow dr;
   
    // Excel connection
    private OleDb.OleDbConnection ExcelConn;
   
    // Connection
    // SQL Server connection
    private OleDb.OleDbConnection SQLConn;
   
    // Connection var
    private string theSQLConnection = "Data Source=MONICASONY;Initial Catalog=dtc;Integrated Security=True;Provider=SQLOLEDB";
   
    private OleDb.OleDbDataAdapter SQLadapter = new OleDb.OleDbDataAdapter();
   
    // Adapter
    private OleDb.OleDbCommandBuilder SQLcommander;
   
    // Command Builder
    private System.Data.DataSet DtInstitution;
   
    // DataSet
    private OleDb.OleDbDataAdapter InstitutionCommand;
   
    // Adapter
    private void Button1_Click(object sender, System.EventArgs e) {
        int tmp;
        OpenFileDialog1.ShowDialog();
        filePath = OpenFileDialog1.FileName;
        fileNameNoPath = System.IO.Path.GetFileName(filePath);
        tmp = fileNameNoPath.IndexOf(".");
        fileNameNoExt = ("["
                    + (fileNameNoPath.Substring(0, tmp) + "$]"));
        connectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                    + (filePath + ";Extended Properties=Excel 8.0;"));
        loadData(connectionString);
    }
   
    void loadData(string connection) {
        OleDb.OleDbConnection myConnection;
        System.Data.DataSet DtSetBreakOutAdvisor;
        System.Data.OleDb.OleDbDataAdapter myCommand;
        myConnection = new OleDb.OleDbConnection(connection);
        myCommand = new OleDb.OleDbDataAdapter(("select * from " + fileNameNoExt), connectionString);
        // myCommand.TableMappings.Add("Table", "Advisors")
        DtSetBreakOutAdvisor = new System.Data.DataSet();
        myCommand.Fill(DtSetBreakOutAdvisor);
        SQLConn = new OleDb.OleDbConnection(theSQLConnection);
        foreach (dr in DtSetBreakOutAdvisor.Tables[0].Rows) {
            dr.SetAdded();
        }
        try {
            SQLadapter.SelectCommand = new OleDb.OleDbCommand("select * from tempBreakout;", SQLConn);
            SQLcommander = new OleDb.OleDbCommandBuilder(SQLadapter);
            SQLadapter.FillSchema(DtSetBreakOutAdvisor, SchemaType.Mapped);
            SQLadapter.Update(DtSetBreakOutAdvisor.Tables[0]);
            DtSetBreakOutAdvisor.AcceptChanges();
        }
        catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }
        DataGridView1.DataSource = DtSetBreakOutAdvisor.Tables[0];
        myConnection.Close();
    }
   
    private void CloseToolStripMenuItem_Click(object sender, System.EventArgs e) {
        this.Close();
    }
}

vbturbo
0
 
KS_MisAuthor Commented:
that inserts all the columns, right?  What if I just want to insert a subset of columns?  Also the link was broken.
0
 
vbturboCommented:
sorry about the link

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22531755.html

this assumes that sheet columns are identical as within the sql table
of course you can manipulate the datatable object in the dataset as you like (e.g, ad , remove aggregate, rename...ect....) columns
as you like as long the number of columns and datatypes match

vbturbo
0
 
Aurora27Commented:
* Its best to have a separate connection object.
* Next is..AFAIK, Insert goes like this: INSERT INTO tablename (field1, field2) VALUES ("123", "567");

Wheher its is a subset or u want to fill in the entire table, it shud be a similar format. Check it out. Why dont u split the statement and see where its failing..the code does not follow the best practices..having too many expressions to be evalauted in a single statement is not good..

My two cents.

Charu.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now