Use SQL statements to import Excel Data

Today, my team leader asked me to load some data to the database. I have done this before. I have never encountered any errors, but today I have suffered a lot from this Excel file. After my unremitting efforts, I finally solved all the problems and successfully completed the task. Next I will write down my problems and discuss them with you.
I. Question proposal
This Excel file contains about 1 million pieces of data. The data volume is not large, and the import process starts smoothly. In less than one minute, I found that all the data in one column was changed to null, and the data format of other columns was incorrect. Then I changed the Data Type of each column. As a result, the data cannot be imported. Depressed!
Ii. Deepening Problems
So I tried it with an SQL statement and executed it with the following statement.
Select *
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'Excel 8.0; HDR = yes; IMEX = 1; database = \ Surrey-test \ GS \ gs_unpaid.xls ',
'Select * from [sheet1 $] ')
Result appears, Ole db providesProgram'Microsoft. Jet. oledb.4.0 'does not contain the table 'sheet1 $ '. The table may not exist, or the current user does not have the permission to use the table.
Ole db Error Tracking [non-interface error: ole db provider does not contain the table: providername = 'Microsoft. Jet. oledb.4.0 ', tablename = 'sheet1 $'].
So I thought about it in detail. Oh, I didn't put my Excel file on the server. Then I ran it here and found the data.
3. Try to solve the problem
After the data is found, the format is still incorrect and does not meet our requirements. Therefore, the data format conversion starts. At the beginning, use convert and cast to convert the data to the float type. So switch again Convert (float, convert (varchar (50), isnull (gs_guid, 0 ))), The format is correct this time, but the data is changed due to the Float Type Precision and cannot meet the requirements. Therefore
Left (cast (convert (float, convert (varchar (20), confirmation_no) as decimal (20, 7) as varchar (20), 9) The results are still unsatisfactory, and the data is distorted. After meditation, I finally thought of this cast (confirmation_no as decimal) as varchar), OK. The problem was solved with ecstasy.
Iv. Check the problem
When I was about to submit, I found a fatal problem. When all the data formats were correct, there was a big change in a column of data, so I checked it carefully, the problem is found.
Cast (convert (bigint, convert (float, convert (varchar (50), isnull (gs_guid, 0) as varchar ), The problem is finally solved.
V. Problem Solving
Last use
Insert into temp4
Select convert (char (4), car_no) as car_no, convert (datetime, [column name]) as pu_date,
Cast (cast ([column name] As decimal) as varchar ),
-- Left (cast (convert (float (5), convert (varchar (50), [column name]) as decimal (20, 7) as varchar (20 )), 10 ),
Convert (decimal (12, 2), [column name]), convert (char (4), dr_no ),
Cast (cast ([column name] As decimal) as varchar ),
Cast (cast ([column name] As decimal) as varchar ),
-- Convert (float, convert (varchar (50), isnull ([column name], 0 ))),
Cast (convert (bigint, convert (float, convert (varchar (50), isnull ([column name], 0) as varchar ),
Cast (cast ([column name] As decimal) as varchar ),
-- Left (cast (convert (float, convert (varchar (20), [column name]) as decimal (20, 7) as varchar (20), 10 ),
-- Left (cast (convert (float, convert (varchar (20), [column name]) as decimal (20, 7) as varchar (20), 9 ),
-- Left (cast (convert (float, convert (varchar (50), isnull ([column name], 0) as decimal (20, 7 )) as varchar (20), 9 ),
-- Left (cast (convert (float, convert (char (20), [column name]) as varchar (20), 6 ),
Convert (datetime, [column name]), isnull ([column name], '')
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'Excel 8.0; HDR = yes; IMEX = 1; database = \ Surrey-test \ GS \ gs_unpaid.xls ',
'Select * from [gs_voucher_notpaid $] ')
Load data to the database.
Vi. Summary
Importing data is simple, but it still contains a lot of knowledge. For example, the data storage type, some common functions in the database, and so on. I hope that these experiences will benefit me from the project, and I hope you can give me more advice.