Import an Access database using Excel in ASP

There are many import methods, and there are two ways of thinking:

1. Open the local Excel file and import the ACCESS database directly through the program;

2. You can open a local Excel file, upload the file to a fixed folder through a program, and then import the data to the ACCESS database through the program.

Compared with the two methods, method 2 is more suitable than method 1.

First, use the first method. The local machine passes the test, but the error message '80040e37' appears during network access, and the system prompts that the specified file cannot be found. Later, we found that to use this method to import data, you must set the server's control permission to full control. You also need to set the drive letter of the Excel file to share by default ~ In this case, this method is not very desirable and has an impact on server security.

If you use the second method, you do not have the preceding permission restrictions. You only need to upload files in combination to avoid the error of '80040e37.

First, attach the function used in Method 1:

'Timeout '-------------------------------------------------------------------------------------
'Exce import to SQL
'Parameter: fileexce Excel table path; sheet: Excel table name; default value: sheet1
'
'Timeout '-------------------------------------------------------------------------------------
Function excetosql (filepath, sheet, pro_type)
Dim patnnow
If sheet = "" Then sheet = "sheet1"
Patnnow = filepath
'Patternnow = "uploadfile/ultrawebgrid1.xls"

'================================== ASP read Excel note ====================== ====================
'I) Think Of The xls file (book) generated by excel97 or excel2000 as a database, and each Worksheet (sheet) in it as a database table.
Ii) ADO assumes the name of the first row in Excel. Therefore, you must include the content of the first row in the defined range.
'III) the row title (field name) in Excel cannot contain numbers. The Excel driver may encounter errors in this case. For example, your line title is "f1"
'Iiii) If a column in your Excel worksheet contains both text and numbers, the ODBC driver of Excel cannot process the data type of this row normally, make sure that the data type of this column is consistent.
'================================================ ================================

Dim connxls, driver, dbpath, RS
Dim tmpname, table, tmpitem

Table = "data table" 'table name
'Tmpitem = array ("A1", "A2", "A3 ")
Tmpitem = "A1, A2, A3"
'Tmpitem = Split (tmpitem ,",")
Tmpname = array ("A1", "A2", "A3") 'excel Header

'Create a connection object
Set connxls = server. Createobject ("ADODB. Connection ")
Driver = "driver = {Microsoft Excel Driver (*. xls )};"
'Dbpath = "DBQ =" & server. mappath (patnnow)
Dbpath = "DBQ =" & patnnow
'Call the Open Method to open the database
Connxls. Open driver & dbpath

'Dsn connection method
'Connxls. Open "DSN = test"
SQL = "select * from [" & sheet & "$]" 'note that the table name must be in the following format: "[Table Name $ ]"
Set rs = connxls. Execute (SQL)
Response. Write "<br>"
If Rs. EOF and Rs. bof then
MessageBox ("system prompt: the required data is not found. Please confirm and try again !! ")
Goback () 'returns the small function on the previous page.
Else
Do while not Rs. EOF
Value1 = ""
For I = 0 to ubound (tmpname)
Value1 = value1 & "," & "'" & RS (tmpname (I ))&"'"
Next
Value1 = right (value1, Len (value1)-1) & "," & pro_type & ", 'cn '"
If left (value1, 1) <> "'" Then value1 = "'" & value1

'Write data to the database
Set rstmp = server. Createobject ("ADODB. recordset ")
Rstmp. Open "select count (*) from" & table & "Where [keyword segment] = '" & keyword & "'", dbconn
If rstmp. Fields (0) = 0 then
SQL = "insert into" & table & "(" & tmpitem & ") values (" & value1 &")"
SQL = Replace (replace (SQL, ",'', ",", null, "),", '',", ", null ,")
Dbconn. Execute SQL
Else
SQL = "Update" & table & "set"
Tmpitem_arr = Split (tmpitem ,",")
Value1_arr = Split (value1 ,",")
For I = 0 to ubound (tmpitem_arr)
If valuew.arr (I) = "" then
SQL = SQL & tmpitem_arr (I) & "= NULL"
Else
SQL = SQL & tmpitem_arr (I) & "=" & value1_arr (I)
End if
If I <> ubound (tmpitem_arr) Then SQL = SQL & "," else SQL = SQL & "Where [keyword segment] = '" & keyword &"'"
Next
Dbconn. Execute SQL
End if
Rs. movenext
Loop
End if

Rs. Close
Set rs = nothing

Connxls. Close
Set connxls = nothing

MessageBox ("system prompt: batch entry successful !! ")
Goback () 'returns the small function on the previous page.

End Function