Shulou(Shulou.com)06/01 Report--

Sort out some of the most basic problems encountered in the last two days of work.

Because of the work requirements, the Excel table is imported into the database, and then the SQL statement is used for business logic operation, and finally the result set is exported to the table. When I heard about this task, I felt no difficulty at first, but as a result, my strength hit me in the face.

Question 1: Excel table import

Solution one:

According to the ideas I have learned, do a logical import, convert it to a file after the suffix .csv, and open cmd

Format:

Sqlldr userid=system/password control='d:\ test.ctl';-- this command will not be executed.

We edit the script test.ctl that needs to be executed

Load data

Location of infile'e:\ text.csv'--.csv

Replace into table test-- imports the table name of the database, three parameters (replace replacement) append (append) insert (insert empty table)

Fields termindted by','--Field delimiter

(A1, 2, 3)-- list of imports

At this time, we can insert successfully when we run the first instruction, and my test insertion fails, which may indicate an error.

Solution 2:

Take advantage of PL/SQL to solve:

Enter select * from test where 1: 2 for update in the tool

As shown in the following figure, to open the mini lock, we need to copy all the Excel contents first, and each column of the type in the table must be the same as the database table type, otherwise it will fail! Select the whole row of our database, paste it and finish it, don't forget to check it with a small green sign, and then submit it, don't forget! Individual under the guidance of the leadership to complete the study, although press is not a problem, there is no real practice everything is equal to zero!

Solution 3:

Use the import tool that comes with PL/SQL to do it (not verbose)

Question 2: the default scientific counting method of Excel table

So after the import, I was surprised to find that a list of values of type number is not correct and is expressed in the form of scientific counting. what is the reason? After investigation, there is not an error in the conversion process, but the original Excel table is the scientific counting method! After Baidu and learning, we know that when the default value is more than 12 digits, it is displayed by scientific counting by default. I have also tried many methods, so a simple, convenient and efficient method to share with you is summarized as follows.

Solution one:

Right-click the mouse, select the setting cell, and then customize the class 0 (text zip code, etc.), which can be transformed into numbers, but sometimes there are a few more zeros, but we can operate relatively! Both the suffixes .xls and .xlsx can then .csv is saved after typing again is still unsolved for the science and technology law!

Question 3: when I import a new table again, I find that the date format (number type) is different from my database type.

Solution:

1. To convert the date format, you need to add an empty column after this column.

2. Select the date column, data-- > pagination-- > Separator-- > Select Space-- > Select date-- > Select YMD, and click to complete the conversion, as shown below.

Before conversion:

After conversion:

Question 4: backup after the completion of the work must not be forgotten, when the amount of data is small, so efficient and convenient export method, logical backup is undoubtedly the best choice, summarized as follows

Solution:

Using database logic to back up exp (CMD command)

Format:

Exp system/tiger@Databases FILE=$path FULL=Y

Explanation:

Exporting the entire library requires permission prompts whether to obtain permissions

The whole article does not seem to have much technical content, and it is difficult for people to move forward in the work. Summing up and sharing accumulated experience together determines success or failure.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.