-2

Having imported data from an excel spreadsheet into datagridview, I would like to create a table (determined by the user in Windows forms) and insert it with the column names into the new table from the datagridview.

The user can use the "program" to insert other excel sheets into other databases as well.

How do I add column names dynamically that is not predefined and is defined by how many columns is in the excel spreadsheet?

Alex.U
  • 1,631
  • 2
  • 16
  • 26
M Gough
  • 1
  • You need to show us what you have tried so far with what issue you are facing with that. – Chetan Sep 27 '17 at 13:29
  • parsing column names shouldnt be so bad - you need to elaborate on what problem you are having – BugFinder Sep 27 '17 at 13:45
  • The task at hand is to create an import application for excel spreadsheet to import into SQL database. But I've managed to get it right. I will try to submit the code. – M Gough Sep 28 '17 at 13:48

1 Answers1

0

My goal here was to create an import application to import excel spreadsheets into a SQL server database. The problem that I faced is to get the number columns that I needed to insert into the database and to dynamically name them according to the excel spreadsheet.

The application will be used on various spreadsheets and the column names must not be hardcoded. This is the code I used that managed the last phase of the application where all the data from the spreadsheet is inserted into to the database in SQL. 

    //Import Button
    private void button5_Click(object sender, EventArgs e)
    {
        string createColumns = "";
        string columns = "";
        string rows = "";
        var grid = (DataTable)dataGridView3.DataSource;
        for (int i = 0; i < grid.Columns.Count; i++)
        {
            if (i == grid.Columns.Count - 1)
            {
                createColumns += "[" + grid.Columns[i].ToString() + "] varchar(200) NULL";
                columns += "[" + grid.Columns[i].ToString() + "]";
            }
            else
            {
                columns += "[" + grid.Columns[i].ToString() + "],";
                createColumns += "[" + grid.Columns[i].ToString() + "] varchar(200) NULL,";
            }

        }
        string createTable = string.Format("Create table [{0}] ({1})", textBox1.Text, createColumns);
        rows = string.Format("Insert Into[{0}]({1})", textBox1.Text, columns);
        for (int i = 0; i < grid.Rows.Count; i++)
        {
            string row = "";

            for (int c = 0; c < grid.Columns.Count; c++)
            {

                if (c == grid.Columns.Count - 1)
                    row += "'" + grid.Rows[i][c].ToString() + "'";
                else
                    row += "'" + grid.Rows[i][c].ToString() + "', ";
            }

            if (i == grid.Rows.Count - 1)
                rows += string.Format(" ({0});", row);
            else
            {
                if (i == 0)
                {
                    rows += " Values";
                }
                rows += string.Format(" ({0}),", row);
            }
        }
        string s = "Integrated Security = SSPI;Persist Security Info = False;Data Source = " +
            ServerName.Text + "; Initial Catalog = " +
            Databases.Text;

        SqlConnection conn = new SqlConnection(s);

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = createTable;
        cmd.Connection = conn;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        SqlCommand cmd2 = new SqlCommand(rows, conn);
        cmd2.CommandType = CommandType.Text;
        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();

        Application.Exit();
    }
M Gough
  • 1