Import all the Excel files in the folder to the SQL database. Each file is a separate table named by the file name.

-- Import all the Excel files in the folder to the SQL database. Each file is a separate table named by the file name.
-- By madgoat 2006-11-23
Create Table # T (fname Varchar ( 260 ), Depth Int , ISF Bit )
Insert Into # T Exec Master .. xp_dirtree ' C: \ test \ ' , 1 , 1
Declare TB Cursor For Select FN = ' C: \ test \ ' + Fname From # T
Where ISF = 1 And Fname Like ' 2.16.xls ' -- Obtain the. xls file (Excel)
Declare @ FN Varchar ( 8000 )
Declare @ Fname Varchar ( 8000 )
Declare   @ End   Int   -- The end position of the screenshot.
Declare   @ Fnamelen   Int   -- File Name Length
Open TB
Fetch Next From TB Into @ FN
While @ Fetch_status = 0
Begin
Set   @ End   =   Len ( @ FN ) - Patindex ( ' % \ % ' , Reverse ( @ FN ))
Set   @ Fnamelen   =   Len ( @ FN ) - 5 - @ End
Set   @ Fname =   Substring ( @ FN , @ End + 2 , @ Fnamelen )
 
Set @ FN = ' Select * ' + @ Fname + ' From OpenDataSource ( '' Microsoft. Jet. oledb.4.0 '' ,
'' Excel 5.0; database = ' + @ FN + ''' ) [Sheet1 $] ' -- The key is this sentence.
Exec ( @ FN )
Fetch Next From TB Into @ FN
End
Close TB
Deallocate TB
Drop Table # T