From the two Excel tables you can think of

From the two Excel tables you can think of

A few days ago, the customer service sister sent over a few tables, let me give her to do the next match, and then do, think, then have this blog post, not by feeling, seemingly simple two Excel table actually hidden a lot of things, narrative as follows, and June mutual encouragement.

Initial needs: Two tables, a student information sheet, a student score table, by the ticket number to link it to make it complete.

Four ways to complete your requirements:

The first method, through Navicat for MySQL to import two tables into MySQL, and then use the left JOIN association, then do the export, the method is feasible, and finally give up.

The second method, the use of Excel VLOOKUP function, because I am not familiar with Excel, not used, but the next colleague used, the method is feasible, but after all, is manual operation, can guarantee not to omit data, doubts.

The third method, using the Microsoft Query tool in Excel, uses SQL syntax to correlate data, which is feasible, inefficient, and discarded.

The four methods, coding, do a small program, matching data, this method is now used in the way, the implementation of the following:

Consider the question:

1. Why is there four ways to do a task?

A: Demand, at the beginning of the demand is only two Excel tables, but later, the data soared into more than 10 cities to do data matching, and through understanding, this data comparison is actually a recurring work, and the source of data is not by the company colleague input, the data format is more chaotic, such as the Information table field called "Name", the score table has become "examinee name", although is the same thing, but by name match, must change the field name, Excel table More, the above-mentioned three-way method efficiency is endangered, but oneself coding, judge the field when add a synonym function, can solve, To get the first lesson: Whether it is a client or a colleague, the face of every task must listen carefully, repeatedly confirmed that many people will think is to work for colleagues, others will tolerate you, but in fact, it is lazy to find an excuse, finally lazy, go detours, or oneself suffer. It is important to confirm the requirements, such as data volume, task frequency, time to complete the task, and so on.

2. How to do a small program to do the professional, why do you want to do professional? [The result of your own pondering, please the Great God more advice]

A: As a technology, professional and rigorous is the body of this, the old saying, do not sweep a room why sweep the world, a small program can not pursue a professional, how to expect to Big program, in this small program, I to three points for professional: First, is just said the field synonym, a lot of problems, as long as a step, you can change I was going to use ODBC to connect to Excel, but considering the uncertainty of the data format, finally swapping with Npoi to read the data, although the workload increased, but the usability of the program is better, for example, when reading Excel data, I will read the base table style, After matching the export style in the restoration of the past, when colleagues get results, do not have to adjust the table style, although the details, but every detail is very important; Thirdly, in the match, I was in the loop call the DataTable's Select method, but later, this method is very inefficient, The following criticism are used to refer to other professionals:

After the data is populated, it is much more efficient to set the primary key than to set the primary key before populating the data. After you set the primary key, compare the performance of the DataTable.Select method when there are no primary keys, and the results show that when you retrieve only the primary key, you use DataTable.Select after you set the primary key. Method can be retrieved much faster than without a primary key. Under the same conditions, if you only need to find a record, using DataTable.Rows.Find is much faster than datatable.select. In some cases where a DataTable query needs to be frequently manipulated, To avoid calling the DataTable.Select method in the loop body, the use of converting the DataTable to an equivalent dictionary structure can effectively solve the problem that the primary key cannot be created because of the repetition of the key value, and the Dicitonary method of using the hash table can greatly improve the query efficiency.

Later, with the demand-to-match, each Excel table data volume does not exceed 1000, the impact on the program performance can be ignored, there is no change program, but for the program performance considerations should be every programmer coding when the subconscious consideration, because this is the accomplishment, must keep in mind.

3. Thinking outside of the program.

A: After the small program is written, because the source of data is too messy, many candidates can not find relevant information, customer service sister said it is best to use the ticket number + ID card for double correlation, after thinking, the program does not move, because to this stage, the ticket number and ID card number are candidates of the only basis, It means that before the process has gone wrong, at this time, you should not be a programmer, but with the customer service sister to find out the cause of the problem, to recommend to the company, improve the process, to eliminate the occurrence of such problems, although this is beyond the scope of the job, but you must be clear, what is preventive measure, what is perfunctory, What kind of person you are, determines your development.

4. More thinking, how to be a good employee?

1. Bring benefits to the company
2. Continuous thinking, optimization of workflow, to the company culture and process structure to bring positive impact
3. Building a knowledge base has a positive impact on the company's knowledge base
4. Sharing, bringing benefits to company colleagues
5. Do every step of the above, will naturally bring their own benefits and development

Yesterday saw a passage, deep feelings, and gentlemen mutual encouragement:

Simply complete the task of the boss is always only a maximum of 60 points, the actual understanding of the boss wants, and completed the business needs of 70 points, fully digging the boss want to be able to share the team or boss to 80 points.

From the two Excel tables you can think of