How to use Excel to operate Oracle

Have you ever thought about a problem? Excel is the most commonly used tool to store data. However, when the number of data items is too large, it may be inconvenient to query. Can you connect Excel with a database to facilitate data use? This article describes how to import data from an Excel table to Oracle. When we import data to Oracle, can we use Excel to update and maintain the data? This article will also tell you a method.

First, we provide an indirect idea:

Using the close relation between SQL Server and Excel, the OpenDataSource method is used in SQL Server to read Excel, and the data is spelled as an oracle-compliant SQL statement.

(OpenDataSource must be manually enabled for SQL server2005. For SQL server2005, choose "configuration tool"> "peripheral application configurator"> "peripheral application configurator"> "Database Engine"> "Impromptu remote query, as shown in)

Run the following code in SQL server:

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;

Insert and update Orcale in Excel

 
 

  
  
SELECT 'Update userinfo set Useducation = '''
  
  
+ Case when [current educational level] is null then''
  
  
ELSE [current education level] END
  
  
+ Case when [technical level] is null then''
  
  
ELSE ''', Usskilllevel = ''' + [technical level] END + ''''
  
  
+ Case when [Joining Parties] is null then''
  
  
ELSE ', Usdangtuantime = to_date (''' + CONVERT (VARCHAR (12), [Joining Parties], 110) + ''', ''mm-dd-yyyy '') 'end
  
  
+ Case when [inbound working date] is null then''
  
  
ELSE ', Usinputtime = to_date (''' + CONVERT (VARCHAR (12), [inbound working date], 110) + ''', ''mm-dd-yyyy '') 'end
  
  
+ Case when [Date of Birth] is null then''
  
  
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 $]
 
 

 
 

  
  
----- The execution result is as follows :-----
  
  
View sourceprint?
  
  
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.