Import an Excel CSV file from a folder to the database

Declare @ dir sysname, @ cmd nvarchar (4000 );
Set @ dir = 'C :\';

Create Table # TMP (filename nvarchar (1024 ));
Set @ cmd = n' dir "'+ @ dir +' *. CSV"/B'
Insert # TMP exec master. DBO. xp_mongoshell @ cmd;

Delete # TMP where filename is null;

If exists (select * from # TMP where filename like '% cannot find file % ')
Begin
Raiserror ('file not found)
End
Else
Begin
Declare @ SQL nvarchar (max );
Set @ SQL = '';
Select @ SQL = @ SQL + 'exec xp_mongoshell n' 'bcp database. DBO. Table in "'+ @ dir + filename
+ '"-W-T-t,-r \ n';' + char (13) + char (10)
From # TMP;

Exec (@ SQL );
End

Drop table # TMP;

You do not need to use the Dir command to read the file list by using xp_cmdshell. xp_dirtree can return the result set.

place the Excel table in a directory, import to database favorites
-- import an Excel table from a directory to the database

-- put all the Excel files in a directory, assuming c: \ test \, then, use the following method

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 'hangzhou.xls '-- .xls file (Excel)
Declare @ FN varchar (8000)
Open TB
Fetch next from TB into @ FN
While @ fetch_status = 0
Begin
-- The following is a query statement. You need to insert the statement as needed.
-- Insert an existing table: insert into Table selct * from...
-- Used to create a table: Select * into table from...
Set @ fn = 'select * from
OpenRowSet (''microsoft. Jet. oledb.4.0 '', ''excel 5.0; HDR = yes; database = '+ @ FN + ''', all customers $ )'
Exec (@ FN)
Fetch next from TB into @ FN
End
Close TB
Deallocate TB
Drop table # T

Access Excel
1), inert into a select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "E: \ contact formula .xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]
However, the query results are ordered by column names instead of the original order in the workbook. This is a problem with the OpenRowSet and OpenDataSource functions of SQL Server 2000. It has nothing to do with the access interface engine and the Excel version. This problem does not exist in OpenRowSet and OpenDataSource of SQL Server 2005.
2), inert into a select * From OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = E: \ contact formula .xls', 'select * from [sheet1 $] ')
This method can solve the problem of opendatasurce, that is, the order of the query results is consistent with that in the workbook.
3) use the linked server:
Exec sp_addmediaserver @ Server = 'xlsserver', @ srvproduct = 'jet4. 0', @ provider = 'Microsoft. jet. oledb.4.0 ', @ datasrc = 'e: \ contact formula .xls', @ provstr = 'excel 8.0'
Inert into a select * From xlsserver... [sheet1 $]
In this case, the columns in the query results are arranged in the order of column names.