如何将数据从Excel转换为SQL Server 2005 ...? [英] How to convert data from excel to sql server 2005...?

问题描述

如何将数据从excel转换为sql server2005 ..?
请任何人都可以帮助我解决急事........

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

推荐答案

试试这个代码，

Hi,

try this code,

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;
        }

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

1.将excel文件的代码写入数据表.

1.Write code for excel file into Data table.

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.通过批量复制，您可以将数据表插入Sql数据库表.

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

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)
                {

                }
            }
        }

这篇关于如何将数据从Excel转换为SQL Server 2005 ...?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！