Experience and lessons of importing Excel datasheet into database __ Database



You have an Excel datasheet with approximately 50,000 records (rows), a total of 9 columns, where 3 characters are up to 4000 characters long.
Want to import this table into the database for analysis, query, never want to try to success from the beginning, spent nearly one months.

1. Use MySQL to import, refer to my other two blog:

MySQL Import Chinese data table Note: http://blog.csdn.net/hpdlzu80100/article/details/51587087
Summary of methods for importing Excel data tables into MySQL using the command line brief: http://blog.csdn.net/hpdlzu80100/article/details/51789116

Although the above method is available, but you need to convert the Excel data table txt or CSV format, the conversion process often appear some inexplicable problems, resulting in txt/csv format disorder, in the load data infile will report all kinds of errors.
In short, every time you import, you are tortured. Always feel a step away from success, but it is difficult to succeed.

2. Oracle and Pl/sql were installed today, trying to import it and finally succeed.

Method: Method 2 in several methods of Excel importing Oracle (http://www.jb51.net/article/39126.htm), namely:

"Method II, using Plsql Developer
Using the Plsql Developer tool, this is the most commonly used tool for Oracle DBAs.
In cases where a single file is not large (less than 100000 lines) and the destination table structure already exists-certainly not for Excel, because the maximum behavior of an Excel file can be 65536--copy, and then use the Plsql Developer tool.
1 Enter SELECT * FROM test for update in Plsql Developer SQL window;
2 Press F8 to execute
3 Open the Lock, and then click the plus sign. The first column of the mouse point to the column header, so that all the selected state, and then paste, the last commit can be committed.
There are a lot of third-party tools, Toad,pl/sql developer,sql Navigator and so on. ”

In addition, when using Pl/sql's own ODBC import, the portion of the data that is longer than 255 bytes is truncated, data loss is lost and can only be discarded.