How to convert data from excel to sql server 2005...?

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

How to convert the data from excel to sql server2005..?
please any one can help me its urgent........

Posted 21-Sep-12 0:28am

Gujula Ravindra Reddy

Add a Solution

3 solutions

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)

D-Kishore · Answer 1 · 2012-09-23T21:11:00

Hi,

try this code,

C#

string CSVFileName=@"D:\filename.csv";
string Datasetname="TableName";
CSVConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='text;HDR=Yes;FMT=Delimited";
string DBSqlServerConstringSQLAuth="Password=password;Connection Timeout=0;Persist Security Info=False;User ID=userid;Initial Catalog=databasename;Data Source=servername";
SqlConnection sqlConnection = new SqlConnection(DBSqlServerConstringSQLAuth);
private void LoadDataToDatabase(string CSVFileName, string Datasetname, string CSVConnectionString)
        {
            LAHDALClass objLAHDALClass = new LAHDALClass();
            DataTable CSVDataTable = GetCsvfileColumns(CSVFileName, CSVConnectionString);
            
	try
	{
            SqlBulkCopy bc = new SqlBulkCopy(sqlConnection.DBSqlServerConstringSQLAuth, SqlBulkCopyOptions.TableLock);
            sqlConnection.Open();
            
            bc.DestinationTableName = Datasetname;
            bc.BatchSize = CSVDataTable.Rows.Count;
            bc.WriteToServer(CSVDataTable);
	}
            catch (Exception ex)
            {
                throw ex;
            }
              finally
                {
                    sqlConnection.Close();
                    bc.Close();
                }
         }

public DataTable GetCsvfileColumns(string CSVFileName,string CSVConnectionString)
        {

            CsvText = "select * from [" + CSVFileName + "]";
            DataTable dataTable = new DataTable();
            DataTable dtSchema = new DataTable();

            OleDbConnection Con = new OleDbConnection(CSVConnectionString);
            try
            {
                OleDbDataAdapter adptr = new OleDbDataAdapter(CsvText, Con);
                dataTable.Locale = CultureInfo.CurrentCulture;
                adptr.FillSchema(dataTable, SchemaType.Mapped);
                adptr.Fill(dataTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Con != null)
                    Con.Dispose();
            }
            return dataTable;
        }

Abhinav S · Answer 2 · 2012-09-21T00:30:00

Solution 1

Try http://www.4guysfromrolla.com/articles/022708-1.aspx[^].

Posted 21-Sep-12 0:30am

Abhinav S

Kamalkant(kk) · Answer 3 · 2012-09-21T00:39:00

1.Write code for excel file into Data table.

C#

public static DataTable exceldata(string filePath)
        {     
            DataTable dtexcel = new DataTable();
               bool hasHeaders = false;
                string HDR = hasHeaders ? "Yes" : "No";
                string strConn;
                if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                
                DataRow schemaRow = schemaTable.Rows[0];
                string sheet = schemaRow["TABLE_NAME"].ToString();
                if (!sheet.EndsWith("_"))
                {
                    string query = "SELECT  * FROM [" + sheet + "]";
                    OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                    dtexcel.Locale = CultureInfo.CurrentCulture;
                    daexcel.Fill(dtexcel);
                }
            
            conn.Close();
            return dtexcel;

        }

2.Through Bulk Copy u can insert data table into Sql database table.

C#

public void BulkImport(DataTable ExcelDatatable, string TABLENAME)
        {

            if (ExcelDatatable.Rows.Count > 1)
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy
                    (
                    con,
                    SqlBulkCopyOptions.TableLock |
                    SqlBulkCopyOptions.FireTriggers |
                    SqlBulkCopyOptions.UseInternalTransaction,
                    null
                    ))
                    {                       
                       
                             bulkCopy.DestinationTableName = TABLENAME;
                       
                        bulkCopy.NotifyAfter = 1000;
                        
                        for (int i = 0; i <= ExcelDatatable.Columns.Count - 1; i++)
                        {
                            string colname = ExcelDatatable.Columns[i].ColumnName.ToString();
                           bulkCopy.ColumnMappings.Add(ExcelDatatable.Columns[i].ColumnName.ToString(), colname);
                        }

                        bulkCopy.WriteToServer(ExcelDatatable);
                    }
                    con.Close();
                }
                catch (Exception ee)
                {

                }
            }
        }

How to convert data from excel to sql server 2005...?

3 solutions

Solution 5

Solution 1

Solution 2

Add your solution here

Preview 0