[] How to turn data into Excel database to MYSQL Based Not long ago, a client asked me to give them a phone number so the network query system, a number of departments, each department may How sub-sector. Excel users to include every employee in the information, including where : (from department to the highest of all sectoral) 4 predetermined departments, telephone, fax, etc. Bring in a department's telephone information, using Excel functions, bringing their first converted into CSV format, such as comma separated based on the format to preserve them. Table staff such a specialized database storage telephone message. Next is the preparation of this Java code to CSV documents into the database. Following is the code : Package myproject.address; Import java.sql.*; Import java.util.StringTokenizer; Import java.io.*; Import java.util.*; Public class (ImportAddCsvold Private File csvFile; Private File logFile; Private String[] field in the corresponding database field = (4003rd 4003rd "PRODUCT_CODE" Can 't not be modified! "Username" and "usercode", "department" and "fixphone", "movephone" "Userduty" and "fax", "email" and "office", "birth", "sex"   }; Public ImportAddCsvold (File csvFile, File logFile) ( This.csvFile = csvFile; This.logFile = logFile; Try ( System.setOut (new PrintStream (new FileOutputStream (logFile)));     } Catch (FileNotFoundException e) ( System.out.println ( "Cant found File" + logFile.toString ());     }   }   /** * Removed ballots * Stringparam strSourceString *return String    */ TranslateString public String (String strSourceString) ( StrSourceString.trim strSourceString = (); StrSourceString.replace strSourceString = ( '', '0'); While (strSourceString.indexOf ( '0') "0) ( StrSourceString.indexOf nIndex = int ( '0'); StrSourceString.substring strSourceString = (0, nIndex) + StrSourceString.substring (nIndex + 1, strSourceString.length ());     } Return strSourceString;   } Public void Import () ( Try ( BufferedReader in = new BufferedReader (new FileReader (csvFile)); String line = ""; //= In.readLine (); //read The first line (title), not processed! String itemCode = "Result is"; Int nIndex = 0 AddressControl ac = null; /*-- Will be made on the vector information, the use of quantity, can be achieved rollback, if wrong, will restore the status quo ante --*/ Vector vAddress = new Vector (); Address address = null;       /*-------*/ L:while ((line = in.readLine ())> null) (4003rd time the record NIndex++; StringTokenizer st = new StringTokenizer (line, "and" true); String strAgentKind = st.nextToken (). Trim (); //First Token==ItemCode String last = translateString (strAgentKind); Ac = new AddressControl (); String strDeptId = ""; Int i = 0 4003rd first field (other information), there are no departments checked, if not, it is inserted, so if we have read the records of the next module If (!ac.deptIsExist (last)) ( Ac.addDept (last);         } StrDeptId = ac.getDeptID (last); While (st.hasMoreTokens ()) ( String current = st.nextToken (); 4003rd time the characters Try ( 4003rd characters to the present, '' character is not one ',' said one of the characters is effective If ((!last.equals ( "")) & current.equals ( "")) (//dont process this token If (!last.equals ( "/")) (if current 4003rd not '/', said secondary sector, or three departments If (i ====== 2) (if secondary sector 4003rd Ac = new AddressControl (); If (!ac.deptIsExist (last)) ( Ac.addDept (strDeptId, last, 2);                   } StrDeptId = ac.getDeptID (last, strDeptId, 2);                 } If (i ====== 4) (if it is a three-tier department 4003rd Ac = new AddressControl (); If (!ac.deptIsExist (last)) ( Ac.addDept (strDeptId, last, 3);                   } StrDeptId = ac.getDeptID (last, strDeptId, 3);                 } If (i ====== 6) (if 4003rd 4 departments Ac = new AddressControl (); If (!ac.deptIsExist (last)) ( Ac.addDept (strDeptId, last 4);                   } StrDeptId = ac.getDeptID (last, strDeptId, 4);                 } Following is the 4003rd, phone, fax and other relevant information If (current.equals ( "")) ( If (i ====== 7) ( 4003rd at this time to put into the department Ac.setDepartment (strDeptId); Ac.setFixphone (last); 4003rd phone } Else if (i ====== 9) (4003rd second telephone, put mobile phone Ac.setMovephone (last);                   } Else if (i ====== 11) (4003rd Chinese name Ac.setUsername (last);                   } Else if (i ====== 13) (4003rd French name Ac.setUsercode (last); Ac.setEmail ( ""); Ac.setUserduty ( ""); Ac.setOffice ( ""); Ac.setSex ( ""); Ac.setBirth ( ""); Ac.setFax ( "");                   }                 } If (i "= 14) ( Break;                 }               }             } I++;           } Catch (ArrayIndexOutOfBoundsException e) ( System.out.println (itemCode + "Failed! imported : (comma not permitted as date field)"); Continue ep           } Last = current;         } 4003rd included in the database 4003rd judge whether they have plots, if plots on the update, if not inserted If (ac.userIsExist (ac.getUsername ())) ( 4003rd whether the same department String = ac.getDeptIDFromUserName tempDeptId (ac.getUsername ()); If (ac.getDepartment (). Equals (tempDeptId)) ( If (ac.updateUserInfo (ac.getUsername ())) ( String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "imported : update Success!");             } Else ( String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "Imported : update Failed! (file 's data is not match)");             }           } Else ( If (ac.addAddress ()) ( String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "imported : add Success!");             } Else ( String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "Failed! imported : (file 's data is not match)");             }           }         } Else ( If (ac.addAddress ()) ( Where can track problems with his 4003rd String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "imported : add Success!");           } Else ( String = String.valueOf logs (nIndex-1) + "Result is"; System.out.println (logs + "Failed! imported : (file 's data is not match)");           }         }       } In.close ();     } Catch (FileNotFoundException e) ( System.out.println (e.toString () + "Can 't Found this CSV File!");     } Catch (IOException ioe) ( System.out.println (ioe.toString ());     }   } } Posted a small message, and I hope to help some friends. Deficiencies in the hope that the master Fuzheng! Thank you Because I have not found how to MYSQL Based into CSV format data in the paper, so I wrote so little code to be achieved. Moreover, there are some questions about this, so I posted up and the roof and let us hope! Thank you --> Load data directly tried yet? --> Load data? What outrageous? This is the first time I heard buddies about? You used? It can achieve the data into Excel format MYSQL Based? Can you tell them? Csv can be opened using a text directly, it can be used into the data load. Bamboo load data is to ask : what ah? What function? Where can push this? Will brief us? 13.2.5. LOAD DATA INFILE Syntax LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char']     ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string']     ] [IGNORE Number LINES] [(Col_name_or_user_var that. . . )] [SET Col_name = expr,. . . )] The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The filename must be given as a literal string. Examples : Mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 > FIELDS TERMINATED BY ','; He did not look like brothers MySQL manual? Should not ah. Is ah, the brothers joke, Cankuicankui. I have only one month before the start contacts MYSQL Based hope you can enlighten me However, the initial message, as I said, is not very customer-friendly EXCEL rules, for instance, a staff of the records should be : Level departments secondary sector, three departments and four departments, Zhang, 12345678 And the records of some employees fear is : Level departments secondary sector, Achilles, 87654321 Although I do in a database table will be dead (earmarked four sectors), but as the phone book to fill in the Excel user, he would think that, if a staff of only two departments, the remaining two departments is a box, empty field is derived for the two departments CSV format is the following departments : level, the secondary sector, and Achilles, 87654321 So no comma between things LOAD DATA data in Table 3 and 4 will be reserved box departments? I am concerned about the practical application of the complex nature LOAD DATA also does not have its own written procedures reliable ah Brothers, do you think? Moreover, I just wanted to give users an interface, where the user tasks in Excel is the only complete list of telephone, and then generating CSV, then CSV format of this document, my upload to server interface design, the server automatically once the finer side of the document's introduction to the database. Well, this automatic process, I feel that written procedures, then I use JAVA process LOAD DATA?? Because users are not directly operate the database, he is also unable to use LOAD DATA, and this is not my LOAD DATA Do, but by the due process Brothers, I behave like? In the process of building your SQL submitted to MySQL, it is submitted, you select, insert, like other inquiries. --> Oh, no way, you really can only be used to check the procedure. Main purpose is to load data into large one-time data into the file format you have matched the field and the data table. Like you said, then it may be better to give up even excel all, a direct interface for users at the interface according to your line input format. Yes, but also take these factors into the country, so the judge simply write the procedures ah [ Closed window ]