EXCEL direct import filed a timeout expired, less data, data a not more, how to better-CodePudding

Dim fileName As String
FileName=EXCELFILENAME. Text
Dim sConnectionString As String="Provider=Microsoft. Ace. The OleDb. 12.0. Data Source='" & amp; FileName & amp; "'; Extended Properties=12.0 Excel;"
"' Dim sConnectionString As String=" Provider=Microsoft. Ace. The OleDb. 16.0; Data Source='" & amp; FileName & amp; "'; Extended Properties=16.0 Excel;"
"' Dim sConnectionString As String=" Provider=Microsoft. Jet. The OleDb. 4.0; Data Source='" & amp; FileName & amp; "'; Extended Properties=8.0 Excel;"
Dim oleDbConnection As oleDbConnection=New oleDbConnection (sConnectionString)
OleDbConnection. The Open ()
Get the excel table
Dim the dataTable As dataTable=oleDbConnection. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, Nothing)
Dim tableName As String=dataTable. Rows (0), (2). The ToString (). The Trim ()
TableName="[" & amp; tableName. Replace (" '"," ") & amp; "] "

"' build EXCEL connection, read the data
'Dim strConn As String="Provider=Microsoft. Ace. The OleDb. 12.0; Data Source='" & amp; FileName & amp; "'; Extended Properties=12.0 Excel;"
'
'use of the SQL statement to get the data from the Excel file
Dim query As String="SELECT purchase requisition number, business type, date, please purchase department, please buy, coding style, style, color, size, quantity, amount, date of demand, suggested date of order, supplier number, documents making, warehouse code FROM" & amp; TableName
Dim the dataset As a dataset=New dataset ()
Dim oleAdapter As OleDbDataAdapter=New OleDbDataAdapter (query, sConnectionString)
OleAdapter. The Fill (dataset, "TY_INPUVOUCH")

SQL database connection
'Dim sqlcon As SqlClient. SqlConnection=New SqlClient. SqlConnection (IMcn)
Sqlcon. The Open ()
"To clear the data inside the
Dim CLEARDATA As DataTable=New DataTable ()
Dim sqlDACLEAR As SqlClient. SqlDataAdapter=New SqlClient. SqlDataAdapter (" truncate table TY_INPUVOUCH sqlcon)
Dim sqlCBCLEAR As SqlClient. SqlCommandBuilder=New SqlClient. SqlCommandBuilder (sqlDACLEAR)
SqlDACLEAR. The Fill (CLEARDATA)

'after data from the excel file, insert records to the SQL Server data table
Dim dataTable1 As DataTable=New DataTable ()
Dim sqlDA1 As SqlClient. SqlDataAdapter=New SqlClient. SqlDataAdapter (" SELECT purchase requisition number, business type, date, please purchase department, please buy, coding style, style, color, size, quantity, amount, date of demand, suggested date of order, supplier number, documents making, warehouse code from TY_INPUVOUCH WITH (TABLOCKX) ", sqlcon)

Dim sqlCB1 As SqlClient. SqlCommandBuilder=New SqlClient. SqlCommandBuilder (sqlDA1)

SqlDA1. The Fill (dataTable1)
Dim dataRow11 As DataRow

For Each dataRow11 In the dataset. The Tables (" TY_INPUVOUCH "). The Rows

'in the SQL data dataRow1

Dim dataRow1 As DataRow=dataTable1. NewRow ()
DataRow1 (" purchase requisition ")=dataRow11 (" purchase requisition ")
DataRow1 (" business type ")=dataRow11 (" business type ")
DataRow1 (" date ")=dataRow11 (" date ")
DataRow1 (" please purchase department ")=dataRow11 (" please purchase department ")
DataRow1 (" please buy ")=dataRow11 (" please buy ")
DataRow1 (" design code ")=dataRow11 (" design code ")
DataRow1 (" name ")=dataRow11 (" name ")
DataRow1 (" color ")=dataRow11 (" color ")
DataRow1 (" size ")=dataRow11 (" size ")
DataRow1 (" number ")=dataRow11 (" number ")
"Amount" dataRow1 ()=dataRow11 (" amount ")
DataRow1 (" need date ")=dataRow11 (" need date ")
DataRow1 (" suggested order date ")=dataRow11 (" suggested order date ")
DataRow1 (" supplier number ")=dataRow11 (" supplier number ")
DataRow1 (" making people ")=dataRow11 (" making people ")
DataRow1 (" warehouse code ")=dataRow11 (" warehouse code ")
DataTable1. Rows. The Add (dataRow1)

Next

Try

' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

SqlDA1. Update (dataTable1) 'import EXCEL data

CodePudding user response:

 oleDbConnection. The Open () 
'instead of 
OleDbConnection.Com mandTimeout 10 minutes 
=600 'OleDbConnection. The Open ()

CodePudding user response:

SQL server data import and export tools to look at,

I don't know how much your data, I this all day with 78 million data no problem,