This article provides an indirectExcelData Import inOracleMethod: UseSQL ServerClose relationships with Excel. in SQL Server, use the OpenDataSource method to read Excel and spell the data into an oracle-compliant SQL statement.
Run the following code in SQL server:
- SELECT 'Update userinfo set Useducation = '''
-
- + Case when [current educational level] is nullthen''
-
- ELSE [current education level] END
-
- + CASEWHEN [technical level] is nullthen''
-
- ELSE ''', Usskilllevel = ''' + [technical level] END + ''''
-
- + CASEWHEN [Joining Parties] is nullthen''
-
- ELSE ', Usdangtuantime = to_date (''' + CONVERT (VARCHAR (12), [Joining Parties], 110) + ''', ''mm-dd-yyyy '') 'end
-
- + CASEWHEN [inbound working date] is nullthen''
-
- ELSE ', Usinputtime = to_date (''' + CONVERT (VARCHAR (12), [inbound working date], 110) + ''', ''mm-dd-yyyy '') 'end
-
- + CASEWHEN [Date of Birth] is nullthen''
-
- ELSE ', Usbirthdate = to_date (''' + CONVERT (VARCHAR (12), [Date of Birth], 110) + ''', ''mm-dd-yyyy '') 'End
-
- + 'Where Uscardid = ''' + [ID card number] + ''';'
-
- From opendatasource ('Microsoft. Jet. OLEDB.4.0 ',
-
- 'Data Source = D: \ gongren.xls; Extended Properties = Excel 8.0 ')... [Sheet1 $]
Note:
1) provide the correct file path and Sheet name;
2) Close the Excel file to be read; otherwise, an error is reported;
3) Make sure that the field corresponding to the to_date function is of the date type in Excel;
The execution result is as follows:
- Update userinfo set Useducation = 'medium specialization', Usinputtime = to_date ('12-25-2000 ', 'Mm-dd-yyyy'), Usbirthdate = to_date ('09-06-1976 ', 'Mm-dd-yyyy') where Uscardid = '000000 ';
-
- Update userinfo set Useducation = '', Usskilllevel = 'intermediate work', Usdangtuantime = to_date ('07-11-2000', 'Mm-dd-yyyy '), usinputtime = to_date ('07-01-2003 ', 'Mm-dd-yyyy'), Usbirthdate = to_date ('08-21-1979 ', 'Mm-dd-yyyy ') where Uscardid = '20140901 ';
-
- Update userinfo set Useducation = '', Usskilllevel = 'Senior employee', Usdangtuantime = to_date ('07-03-2001 ', 'Mm-dd-yyyy '), usinputtime = to_date ('01-01-2005 ', 'Mm-dd-yyyy'), Usbirthdate = to_date ('10-26-1980 ', 'Mm-dd-yyyy ') where Uscardid = '20140901 ';
Execute the above Code in pl/SQL.
This article introduces,