In today's era of rapid development of information technology, users often encounter the problem of Switching Data in EXCEL files to data in ORACLE databases to achieve real-time data operations, there are not many materials available in this regard. If you need to reverse the data in the ORACLE information system and use EXCEL to analyze and sort it out, I will use ODBC to import the data into an EXCEL file in my work. For Beginners, this is a good introduction.
ODBC is the abbreviation of Open Database Connectivity, that is, Open Database interconnection. The prerequisites for using ODBC for Dynamic Data Exchange are simple. You only need to install Microsoft Office EXCEL on the local machine, and then run the compiled SQL file as needed.
The following describes the actual problems I encountered during my work:
1. Configure the ODBC data source first. On the Control Panel, select the ODBC data source and add and install odbc for oracle. You can customize the data source name and description. The user name and server must be set based on the database name set in the ORACLE database. For example:
Data Source Name: EXAMPLE
Description: EXAMPLE
User name: SCOTT
Server: TIGER
2. Open EXCLE. Under the data menu, select obtain external data source and create a database query.
3. The system prompts you to select the data source, select the data source EXAMPLE we have set earlier, and enter the Database Password. If this is the first time you run the SQL program, cancel the execution and then execute the SQL statement in MICROSOFT QUERY.
4. Select the data to be dumped based on your work needs and write SQL statements. For example:
SELECT A.ITEM_NO,A.ITEM_DESC,A.ITEM_UM, A.ITEM_CREATED_BY,A.CREATED_DATE FROM IC_ITEM_MST A
|
5. Execute the query for this section. You can see in the EXCEL table that all the records meeting the condition have been reversed. The description of the first behavior record column. Save the file.
6. If you re-execute the file, you only need to open and execute the QUERY in the micrsoft query window to obtain real-time data.
7. Then, you can use the powerful editing function of EXCEL to analyze and modify the data, which is quite convenient.
Using this method to reverse data is satisfactory. Take my machine as an example: the machine is configured as a Pentium 933,256 M memory, and it takes only a few seconds to pour 4000 records.