How to map an Excel column to a Database column

Question

I needed help to do the specific mapping of a column of an excel file to a column of a database. I can read the excel file but I just can’t map the file to the column I want in the database. If you need any part of the code just ask as any other question.

I wanted to do something like this :

Answer 1

For what you said, just create the tables in Bd and perform CRUD(Create,Read,Update,Delete) by the application.

Example: As you did not specify which bank you are using I will use Postgres, but should suit anyone.

First install a ADO.NET data provider in the project, can be done by Nuget Manager. I installed Npgsql.

Create the database to receive this data.

CREATE TABLE public."exemplodb"
(
  browse character varying NOT NULL,
  colunadadb1 character varying,
  colunadadb2 character varying,
  colunadadb3 character varying,
  colunadadb4 character varying,
  CONSTRAINT "PK" PRIMARY KEY (browse)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public."exemplodb"
  OWNER TO postgres;

Now by clicking the Send BD button you program your data handling.

        private void enviardbButton_Click(object sender, EventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection("sua connection string aqui"))
            {
                try
                {

                    string commandText = 
                        $"insert into exemplosdb " +
                        $"(browse, colunadadb1, colunadadb2, colunadadb3, colunadadb4) " +
                        $"values ('{browseComboBox.Text}' '{colunadb1ComboBox.Text}', " +
                        $"'{colunadb1ComboBox.Text}', '{colunadb1ComboBox.Text}', '{colunadb1ComboBox.Text}');";
                    int linhasAfetadas = 0;
                    using (NpgsqlCommand command = new NpgsqlCommand(commandText, conn))
                    {
                        conn.Open();
                        linhasAfetadas = command.ExecuteNonQuery();
                        conn.Close();
                    }
                    if (linhasAfetadas == 1)
                    {
                        //Dados da tela gravados com sucesso.
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

Making it clear that this is a super simplified example just to demonstrate! Several important concepts, such as separation of responsibilities, layered programming, among others were ignored for simplicity, but are very important.

Answer 2

I believe the easiest way for this case is to use the SQL Server (Sql Server Import and Export Data) data import tool and create a new table in the database temporarily, so that it is possible to input new data from a select or update from a Join, to load the desired column.