Use SQL Server to import data in Excel to Oracle

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,