Import Data from Excel to Sql Server 2008 in ASP.NET C# (import datetime data)

Question

0.00/5 (No votes)

See more:

, +

ASM

Hey i am trying to import data from excel to sql server , it works fine till i am not passing date but now i want to pass the date to sql server it provides error as datatype not matches.
Anyone has logic or please suggest me what can i do to ..

my code is

C#

string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
               //Save File as Temp then you can delete it if you want
               FileUpload1.SaveAs(path);


               string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

               // Create Connection to Excel Workbook
               using (OleDbConnection connection =
                            new OleDbConnection(excelConnectionString))
               {
                   OleDbCommand command = new OleDbCommand
                           ("Select * FROM [Sheet1$]", connection);

                   connection.Open();

                   // Create DbDataReader to Data Worksheet
                   using (DbDataReader dr = command.ExecuteReader())
                   {

                       // SQL Server Connection String
                       string sqlConnectionString = @conn;

                       // Bulk Copy to SQL Server
                       using (SqlBulkCopy bulkCopy =
                                  new SqlBulkCopy(sqlConnectionString))
                       {
                           bulkCopy.DestinationTableName ="Table1";
                           bulkCopy.WriteToServer(dr);
                           Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                       }
                   }
               }

this code perfectly works till i am not passing date...

Posted 23-Jan-14 17:48pm

Member 10202727

Add a Solution

4 solutions

Solution 2

Might be the column in the Excel Sheet is not in a valid date format.
Change it to Date Type.
Select the Column in the Excel Sheet -> Right Click -> Format Cells -> Number Tab -> Select Date -> Choose your desired Type -> Ok
Then you try to Import...
Hope it works...

Posted 23-Jan-14 17:57pm

Karthik_Mahalingam

Comments

Member 10202727 24-Jan-14 0:00am

i had tried this but not working...

did you try this anytime ??

Karthik_Mahalingam 24-Jan-14 0:03am

yes i have came into this situation earlier..
what is your sql column Type ?
which version of sql ?
sql= Date or DateTime column?

Member 10202727 24-Jan-14 0:17am

its datetime storing value as '1980-02-19 00:00:00.000'
and using sql server 2008

Karthik_Mahalingam 24-Jan-14 1:07am

in excel which format ?

Member 10202727 24-Jan-14 1:29am

date

Member 10202727 24-Jan-14 1:32am

The given value of type String from the data source cannot be converted to type datetime of the specified target column.

this kind of exception i am getting..

Karthik_Mahalingam 24-Jan-14 3:26am

try with all possible combinations on the excel date format..

Member 10202727 24-Jan-14 3:57am

find out the answer ..check it out

Karthik_Mahalingam 24-Jan-14 4:09am

good

Solution 4

Better convert the particular excel field into columns

steps-1: Select the field to want to convert.

step-2 : Then Data ---> Text to columns --->(Option Delimited) Next -->Next--->In column data format, choose the format ---> and finish.

Save and try to upload.

Posted 23-Jan-14 22:03pm

Senthilnathan Vaithi

Comments

satheeshkumar chinnadurai 24-Jan-14 5:45am

sing (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(4,"Dates");
copy.DestinationTableName = "Censis";
copy.WriteToServer(dtExcelRecords);

}

here 4 excel sheet column name
"Dates" is table column name pass like 2012-05-05

Solution 1

This[^] article talks about using dates in Excel for bulk inserts.

Posted 23-Jan-14 17:50pm

Christian Graus

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10202727 · Accepted Answer · 2014-01-23T21:45:00

It works i tried to convert it in datatble den change datatype and then insert

C#

string sqlConnectionString = @conn;
command.CommandType = CommandType.Text;
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                DataSet objDataset1 = new DataSet();

                objAdapter1.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[0][5].ToString() != "")
                    {
                        DateTime dt1 = cf.texttodb(dt.Rows[0][5].ToString());
                        dt.Rows[i][5] = dt1;
                    }}
 using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "Tablename";
                    bulkCopy.WriteToServer(dt);
                    Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                }

in this i had created a function txtdob which converts my string to datetime format Thank you i tried it workes if u feel so mark it as answer