Loading a Flat File to a MySQL Table
The default format of a file to load into a MySQL table is as follows:
the fields must be separated by tabs, the input lines terminated by
'\n', and backslashes(\), newlines (\n), and tabs (\t) escaped by a
backslash. The MySQL documentation explains how to change these
defaults.
This example loads a flat file called infile.txt to a MySQL
table named mysql_2_table with an INT and a VARCHAR(20) column.
An example of the contents of infile.txt
(\t represents an invisible tab character):
try {
// Create the statement
Statement stmt = connection.createStatement();
// Load the data
String filename = "c:\\\\temp\\\\infile.txt";
String tablename = "mysql_2_table";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename);
// If the file is comma-separated, use this statement
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
+ tablename + " FIELDS TERMINATED BY ','");
// If the file is terminated by \r\n, use this statement
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
+ tablename + " LINES TERMINATED BY '\\r\\n'");
} catch (SQLException e) {
}
123\tString1
234\tString2
Post a comment