Import data from Excel to SQL Server
Left straight punch
Introduce the two ways to import data into SQL Server from Excel.
First, in the program, use ADO.NET. code show as below:
// Connection string
String strconn = "provider = microsoft.jet.Oledb.4.0; extended profr = excel 8.0; data source =" [eXCEL file, with path] ";"
OLEDBCONNECTION CONN = New OLEDBCONNECTION (STRCONN);
Cn.open ();
DataTable dtschema = conn.getoledbschematable (OLEDBSChemaGuid.Tables, new object [] {null, null, null, "table"});
DataSet DS = New Dataset ();
// An Excel file may have multiple worksheets, traversed
Foreach (Datarow Dr in DTSChema.Rows)
{
String Table = DR ["Table_Name"]. TOSTRING ();
String strexcel = "SELECT *" TABLE "]"
DS.Tables.Add (Table);
OLEDBDataAdapter mycommand = new oledbdataadapter (strexcel, conn);
MyCommand.Fill (DS, TABLE);
}
CONN.CLOSE ();
In this way, the data read is hidden in the DataSet.
In this way, the database does not have to be equipped with Excel in the machine.
Second, write the SQL statement directly in the query analyzer:
If it is imported into an existing table, use
INSERT INTO table Select * from openrowset ('Microsoft.jet.Oledb.4.0'
, 'Excel 5.0; HDR = YES; Database = c: /test.xls' ,sheet1
form
If it is imported and new, use
SELECT * INTO Table from OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'Excel 5.0; HDR = YES; Database = c: /test.xls' ,sheet1
form.
The above statement is to read all the columns in the Sheet1 worksheet in the Excel file. If you just want to guide some columns, you can
INSERT INTO Table (A1, A2, A3) SELECT A1, A2, A3 from OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'Excel 5.0; HDR = YES; Database = c: /test.xls' ,sheet1
In fact, you can use OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'Excel 5.0; HDR = yes; database = c: /test.xls' ,sheet1 $) is a table, for example, I have written such a sentence:
INSERT INTO EVAL_CHANNEL_EMPLOYEE (Channel, Employee_ID)
SELECT CASE A. Channel When 'Diy' Ten 1 When 'RDC' Ten 0 When 'KCM' THEN 2 ELSE 3 End, B.ID from
OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'Excel 5.0; HDR = YES; Database = C: /TEMP/Name.xls' ,sheet1 $) AS A, PERS_EMPLOYEE B
WHERE A. Employee Code = B.Code
No matter which method, which means, the system will default to the first line as a field name.
