How to insert date while importing Excel into SQL Server

Question

I loaded an Excel file into SQL Server and it's working fine, but I want to attach current date while importing this file. That means each row will have the date data loaded in. So each time I load the file, new data should take the current date, and the old data still have the old date.

How can I do that ?

Code for importing the Excel file:

string ssqltable = comboBox1.GetItemText(comboBox1.SelectedItem);
string myexceldataquery = "select * from [" + ssqltable + "$]";

try
{
    OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + imagepath + ";Extended Properties='Excel 12.0 Xml; HDR=YES;IMEX=1;';");

    string ssqlconnectionstring = "Data Source=.;Initial Catalog=Bioxcell;Integrated Security=true";

    OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oconn);
    oconn.Open();

    SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);

    DataTable dt = new DataTable();
    dt.Load(oledbcmd.ExecuteReader());
    bulkcopy.DestinationTableName = ssqltable;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        bulkcopy.ColumnMappings.Add(i, i);
    }

    bulkcopy.WriteToServer(dt);
    oconn.Close();
}

and this for inserting date but not working for me

if (ssqltable == "Overseas")
{
    conn.Open();

    SqlCommand sqlc = new SqlCommand("delete from Overseas where Bonus = 'Bonus'", conn);
    sqlc.ExecuteScalar();

    SqlCommand Update1 = new SqlCommand("Update Overseas set ID = 1 Where ProductCode = 9630", conn);
    Update1.ExecuteNonQuery();

    SqlCommand Update2 = new SqlCommand("Update Overseas set ID = 2 Where ProductCode = 9628", conn);
    Update2.ExecuteNonQuery();

    SqlCommand Update3 = new SqlCommand("Update Overseas set ID = 3 Where ProductCode = 9629", conn);
    Update3.ExecuteNonQuery();

    SqlCommand Update4 = new SqlCommand("Update Overseas set ID = 4 Where ProductCode = 9632", conn);
    Update4.ExecuteNonQuery();

    SqlCommand Update5 = new SqlCommand("Update Overseas set ID = 5 Where ProductCode = 9631", conn);
    Update5.ExecuteNonQuery();

    SqlCommand Update6 = new SqlCommand("insert into Overseas (Date) Values (GETDATE())", conn);
    Update6.ExecuteNonQuery();
}

No error and only one date was inserted. How can I solve this problem ?

marc_s · Accepted Answer · 2017-04-12 05:10:55Z

4

If you have control over the table, you can create a new date column with a default value of GETDATE():

ALTER TABLE <tablename>
ADD ROW_CREATE_TS DATETIME DEFAULT GETDATE()

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns

edited Apr 12, 2017 at 5:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 11, 2017 at 23:31

Matt M

1064 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Amr Kamal Over a year ago

every row added it will take current date ?

Matt M Over a year ago

Correct. Link to the MS doc link

Amr Kamal Over a year ago

nice .. it's worked but how can i make date format like 12/4/2017 ? :D

Matt M Over a year ago

Depending on your SQL server version you can use one of the following commands in your SELECT statement FORMAT( <column_name>, 'dd/MM/yyyy', 'en-US' ) or for older versions CONVERT() CONVERT(nvarchar(30), <column_name>, 103)

Amr Kamal Over a year ago

my sql server version is 2008 how can i do it from design ?

|

Collectives™ on Stack Overflow

How to insert date while importing Excel into SQL Server

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related