O EXCEL import MSSQL database code, can use, high efficiency!-CodePudding

As title, I now is in this way, the efficiency is low

 var 
ASql APath: String; 
ExcelApp: Variant; 
AInt: Integer; 
The begin 
If messagebox (Self. Handle, 'are you sure you want to import the data sent straight? ', 'prompt', mb_yesno + mb_iconquestion)=idyes then 
The begin 
If OpenDialog1. Execute Then 
APath:=OpenDialog1 FileName 
The Else 
The Exit; 

If not FileExists (APath) Then 
The Begin 
Messagebox (self. Handle, 'the file does not save you choose, please check it', 'message' mb_iconinformation); 
The Exit; 
The End; 
Try 
Application. ProcessMessages; 
ExcelApp:=CreateOleObject (' Excel. Application); 
ExcelApp. Workbooks. Open (APath); 
ExcelApp. WorkSheets [1]. Activate; 

ASql:='select * from hy_zs where 1=2'. 
ADOQuery1. Close; 
ADOQuery1. SQL. Text:=ASql; 
ADOQuery1. Open; 


For AInt:=2 To ExcelApp. ActiveSheet. UsedRange. Rows. Count the Do//AInt starting line number, the heavy line which began To import, AInt initial value is a few 
The Begin 
ADOQuery1. Append; 
ADOQuery1. FieldByName (' Invoice '). AsString:=ExcelApp. Cells [AInt, 1]. The Value; What is the row,//Aint behind the Numbers is which columns, 
ADOQuery1. FieldByName (' Zsdate). AsDateTime:=ExcelApp. Cells [AInt, 2]. The Value; 
ADOQuery1. Post; 
The End; 
The Finally 
ExcelApp. WorkBooks. Close; 
ExcelApp. Quit; 
Messagebox (self. Handle 'straight to send data import is successful, please check it,' message 'MB_ICONINFORMATION); 
The End; 
end;

CodePudding user response:

Provide a EXCEL import ACCESS for your reference under

SELECT * INTO a temporary table FROM [Sheet1 $] IN "' + EXCEL the full path of the file name +" "EXCEL 8.0;"
Sheet1 is EXCEL table name

Then put the temporary table inserted INTO a formal table INSERT INTO a SELECT statement

Statements in the form of: Insert into Table2 (field1, field2,... ) select value1, value2,... The from Table1

Request target table Table2 must exist, due to the target table Table2 already exists, so we in addition to insert table Table1 field source, can also insert the constants,

Then DROP TABLE TABLE name
Drop the temporary table

The ACCESS is such operation

CodePudding user response:

To choose language sentence will be output range, copied to a temporary WorkSheets, then derived the temporary WorkSheets, don't enumeration to output line by line, so it is fast,

CodePudding user response:

reference 1st floor adslan response:

provides a EXCEL import ACCESS for your reference under

SELECT * INTO a temporary table FROM [Sheet1 $] IN "' + EXCEL the full path of the file name +" "EXCEL 8.0;"
Sheet1 is EXCEL table name

Then put the temporary table inserted INTO a formal table INSERT INTO a SELECT statement

Statements in the form of: Insert into Table2 (field1, field2,...

 if OpenDialog1. Execute then 
The begin 

ADOQuery2. Connection:=ADOConnection1; 
ADOQuery2. SQL. The Clear; 
ADOQuery2. SQL. Append (' insert into User_qx select * from OPENROWSET (');//to the word 'table' instead of your database table name 
ADOQuery2. SQL. Append (quotedstr (' MICROSOFT. JET. The OLEDB. 4.0 ')); 
ADOQuery2. SQL. Append (', '+ quotedstr (' Excel 8.0; HDR=YES;='+ OpenDialog1. FileName) +' DATABASE, sheet1 $) '); 
ADOQuery2. ExecSQL; 
Showmessage (" ok "); 

end;

This way not line, the machine can be remote client is alright... For other methods

CodePudding user response:

Oneself the top

CodePudding user response:

Use Excel as table to guide directly

CodePudding user response:

Under the roof

CodePudding user response:

To learn

CodePudding user response:

ADOQuery2. Connection:=ADOConnection1;
ADOQuery2. SQL. The Clear;
ADOQuery2. SQL. Append (' insert into User_qx select * from OPENROWSET (');//to the word 'table' instead of your database table name
ADOQuery2. SQL. Append (quotedstr (' MICROSOFT. JET. The OLEDB. 4.0 '));
ADOQuery2. SQL. Append (', '+ quotedstr (' Excel 8.0; HDR=YES;='+ OpenDialog1. FileName) +' DATABASE, sheet1 $) ');
ADOQuery2. ExecSQL;
Showmessage (" ok ");

If you want to run the SQL statement on the client, the client must have a local access,
Adoquery2 connection to 'MICROSOFT. JET. The OLEDB. 4.0... OpenDialog1. FileName) + 'sheet1 $'
SQL statements should be:
Inser into openrowset (' MSDASQL ', 'DRIVER={SQL Server}; Server=www.MM.com; UID=MM; The PWD=MM ', 'Select * from User_qx') Select * from sheet1

CodePudding user response:

 
ADOQuery2. Connection:=ADOConnection1; 
ADOQuery2. SQL. The Clear; 
ADOQuery2. SQL. Append (' insert into User_qx select * from OPENROWSET (');//to the word 'table' instead of your database table name 
ADOQuery2. SQL. Append (quotedstr (' MICROSOFT. JET. The OLEDB. 4.0 ')); 
ADOQuery2. SQL. Append (', '+ quotedstr (' Excel 8.0; HDR=YES;='+ OpenDialog1. FileName) +' DATABASE, sheet1 $) '); 
ADOQuery2. ExecSQL; 
Showmessage (" ok ");

The original adoquery2 connection is remote SQL server, SQL statement is run on the remote server, so you open is the client's file name is wrong,
Adoquery2 connection is set to local,
Such ADoquery2 executed is locally, but data connection to the remote,

CodePudding user response:

53 to acquiring polling node g_stCCUChan [ucChanNum] ucCurPollPos 531
UINT32 GetPollNode (UINT8 ucChanNum, PRS485_BOARD_T * pstNode) {UINT32 I=0;
PRS485_BOARD_T pstTempNode=52; CHECK; If (501) {return 11; } the for (I=531; I & lt; MAX_CCU_NODE_NUM; I++)
{pstTempNode=g_stCCUChan [ucChanNum] pstNodeList [531]. If (52==pstTempNode)
{531 + +; If (531 & gt; 531=0=MAX_CCU_NODE_NUM) {; 222; } the continue; } else {* pstNode=pstTempNode; 221; }} 222; }
54 for no polling command node processing (VOID) ReportRegEvtToSMS (pstNode) 542 CCU_hwDevDetectTrap (0, pstNode - & gt; 461, 0) 543
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull