Use SQL scripts to import Excel Data

1. import data to the MySQL database

First save the Excel file as a *. CSV file, and then execute the following command.

Load data local infile "path.csv" into talble (col1, col2, col3, col4 ,...);

 

Ii. Import the SQL Server database

First, you need to open the ad hoc distributed queries option. The method for opening an account is as follows:

Exec sp_configure 'show advanced options', 1

Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

 

Then execute:

Select * into newtable
From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "F: \ one-page management .xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]

 

Finally, disable the ad hoc distributed queries option.

Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0

Reconfigure